SAP ENQUEUE ANALYSIS
A Brief Explanation on Oracle Wait Events :
At any given moment, when an Oracle process that is not busy, it would be waiting for one of the events to occur. These events are known as Oracle wait events. Suppose an application has submitted a COMMIT statement and the server process is waiting for the LGWR process to signal that the redo log buffer has been flushed to disk. This wait event is called "log file sync." Another dedicated server process might be waiting for a row-level lock on the INVOICES table to be freed so that a SELECT FOR UPDATE statement can continue. That wait event is called "Enqueue."
It is very beneficial that Oracle is diligent about tracking wait event information and making it available to DBAs. We call this the "wait event interface."
Oracle10g has brand-new wait events and the database kernel now captures statistics on more than 800 specific wait events. These new wait events are the result of Oracle breaking-out their latch waits into their individual components and breaking-out enqueue waits (locks) into a finer level of granularity.
Some of the oracle views available are given below :
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_SQLTEXT - Displays the text of SQL statements.
DBA_HIST_SYSTEM_EVENT - Displays historical information on total waits for an event.
ENQUEUE ANALYSIS with ORACLE WAIT EVENTS :
Starting from Oracle 10g, with the introduction of AWR (Automatic workload repository) functionality, we can analyze the past lock wait situations and in many times can find out the root cause of the issue. However, the default retention period for AWR data is seven days.
The sql statements mentioned below can be executed in any of the following methods.
a) At Oracle level.
b) In SAP, using RSORADJV report.
c) In SAP, Tx. ST04n -> Additional Functions -> Arbitrary Monitoring ( Join-Select)
d) In SAP, new kernel version, Tx ST04 -> Additional Functions -> SQL Command Editor.
1. Sql Statement to Check the Distribution of different Oracle Wait Times of the System
SELECT
EVENT,
TOTAL_WAITS,
TIME_WAITED,
AVG_MS,
ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENT
FROM
( SELECT
SUBSTR(EVENT, 1, 30) EVENT,
TOTAL_WAITS,
TIME_WAITED,
ROUND(TIME_WAITED_MICRO / TOTAL_WAITS / 1000, 2) AVG_MS
FROM V$SYSTEM_EVENT
WHERE
WAIT_CLASS != 'Idle' AND
EVENT NOT IN
('db file parallel write', 'log file parallel write',
'log file sequential read', 'control file parallel write',
'control file sequential read', 'Log archive I/O')
UNION
SELECT 'CPU' EVENT, NULL, VALUE, NULL
FROM V$SYSSTAT
WHERE STATISTIC# = 12
ORDER BY 3 DESC)
WHERE ROWNUM <= 10;
Explanation:
This sql statement gives an overview how the database resources are being utilized. And the other wait events which are not relevant from the view of SAP performance are ignored.
Example:
In the example, it shows that since start of the system, 11% of the oracle server time was used for enqueues, which is much higher than normal and could have significant impact on the system performance. This needs to be investigated further for the root cause.
2. Sql Statement to check the Enqueue Peak Time.
SELECT
TO_CHAR(END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')
END_INTERVAL_TIME,
TOTAL_WAITS,
TIME_WAITED_MICRO,
ROUND(DECODE(TOTAL_WAITS, 0, 0, TIME_WAITED_MICRO /
TOTAL_WAITS / 1000), 2) AVG_WAIT_MS
FROM
( SELECT
HSS.END_INTERVAL_TIME,
HSE.EVENT_NAME,
HSE.TOTAL_WAITS - LAG(HSE.TOTAL_WAITS, 1) OVER
(ORDER BY HSS.SNAP_ID) TOTAL_WAITS,
HSE.TIME_WAITED_MICRO - LAG(HSE.TIME_WAITED_MICRO, 1) OVER
(ORDER BY HSS.SNAP_ID) TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT HSE, DBA_HIST_SNAPSHOT HSS
WHERE
HSE.SNAP_ID = HSS.SNAP_ID AND
HSE.EVENT_NAME = 'enq: TX - row lock contention'
ORDER BY
HSS.SNAP_ID DESC
)
WHERE
TOTAL_WAITS >= 0;
Explanation:
This sql statement gives the information of the enqueue situation in the system during different time periods. It queries the DBA_HIST_SYSTEM_EVENT and DBA_HIST_SNAPSHOT views for the enqueue event ‘enq:TX – row lock contention’.
Example :
Here, we can observe that there have been high number of lock waits during the time period ending 03:00:00 and 4:00:00 on 2008-07-10. So we need to analyze further on this time period and try to find out what happened in the system during this time.
3. Sql Statement to check which enqueue waits occurred during a given time period.
SELECT
TO_CHAR(ASH.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS')
SAMPLE_TIME,
ASH.SESSION_ID,
ASH.BLOCKING_SESSION,
O.OBJECT_NAME,
S.SQL_TEXT
FROM
DBA_HIST_ACTIVE_SESS_HISTORY ASH,
DBA_HIST_SQLTEXT S,
DBA_OBJECTS O
WHERE
ASH.SQL_ID = S.SQL_ID (+) AND
ASH.CURRENT_OBJ# = O.OBJECT_ID (+) AND
ASH.EVENT like 'enq: TX - row lock contention' AND
ASH.SAMPLE_TIME BETWEEN
TO_TIMESTAMP('10.07.2008 02:30:00', 'dd.mm.yyyy hh24:mi:ss') AND
TO_TIMESTAMP('10.07.2008 04:00:00', 'dd.mm.yyyy hh24:mi:ss') AND
ASH.SESSION_STATE = 'WAITING'
ORDER BY
SAMPLE_TIME DESC;
Explanation:
This sql statement gets the information about the enqueue waits during the mentioned time period. It queries the views DBA_HIST_ACTIVE_SESS_HISTORY , DBA_HIST_SQLTEXT & DBA_OBJECTS.
Example :
In this example, we observe that may oracle sessions were waiting for the object USR02 till 03:22:45 system time. The column ‘Blocking_session’ gives the session id which was blocking the object, in this example session ‘248’ held the object ‘USR02’.
We can try and correlate this to the other activities that happened in the system during the same time period. In this example scenario, we found a batch job running during this time which was caused the lockwait situation.
4. Sql Statement to check the Oracle holders during a given time period.
SELECT
TO_CHAR(ASW.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS')
SAMPLE_TIME,
ASW.BLOCKING_SESSION SESSION_ID,
COUNT(*) "#WAITERS",
ASH.BLOCKING_SESSION,
ASH.TIME_WAITED,
DECODE(ASH.SESSION_STATE, NULL, 'INACTIVE',
'WAITING', ASH.EVENT, 'CPU') ACTION,
TO_CHAR(SUBSTR(HST.SQL_TEXT, 1, 4000)) SQL_TEXT
FROM
DBA_HIST_ACTIVE_SESS_HISTORY ASH,
DBA_HIST_ACTIVE_SESS_HISTORY ASW,
DBA_HIST_SQLTEXT HST
WHERE
ASH.SQL_ID = HST.SQL_ID (+) AND
ASH.SAMPLE_TIME (+) = ASW.SAMPLE_TIME AND
ASH.SESSION_ID (+) = ASW.BLOCKING_SESSION AND
ASW.EVENT = 'enq: TX - row lock contention' AND
ASW.SESSION_STATE = 'WAITING'
GROUP BY
TO_CHAR(ASW.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS'),
ASW.BLOCKING_SESSION,
ASH.BLOCKING_SESSION,
ASH.TIME_WAITED,
DECODE(ASH.SESSION_STATE, NULL, 'INACTIVE',
'WAITING', ASH.EVENT, 'CPU'),
TO_CHAR(SUBSTR(HST.SQL_TEXT, 1, 4000))
ORDER BY
TO_CHAR(ASW.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS')
Explanation:
This Sql statement shows the activities of the oracle lock holders during the given time. It queries the view DBA_HIST_ACTIVE_SESS_HISTORY & DBA_HIST_SQLTEXT.
Example :
It shows which sql statements were being executed by the oracle session The status INACTIVE shows that it might have been active on SAP side during that time.
SAP ENQUEUE ANALYSIS
