wait events

1. What are wait events?

Wait events are characteristic parts of the Oracle kernel source code that can contain Oracle sessions during execution. Oracle provides statistical information on the wait events, which comprises the following components:

• Name of the wait event
• Optional: Up to three parameters of different importance depending on the wait event.

2. What is the connection between wait events and database performance?

The database response time is largely determined by the following two components:

• Waiting in the context of wait events
• CPU consumption

In well-tuned systems, the wait events make up about 60% of the response time. Otherwise, the proportion may be much higher, which has a negative effect on the response times. Wait event tuning can therefore frequently bring about a significant improvement in database performance.

For information on the distribution between wait event time and CPU time, refer to the initial screen of transaction ST04 ("Busy wait time" and "CPU time"). Note, however, that ST04 may also include Idle Wait Events in the Busy Wait Time, which is why it is best to check the accuracy of the Busy Wait Time with the data from V$SYSTEM_EVENT (see below).

3. For what purpose are wait events used?

By determining the wait event, you can determine what a session is currently doing or what it is waiting for. In the case of performance problems, you can therefore determine whether they are caused by database locks, disk accesses, latches or another cause.

Looking at all the wait events that have accumulated since the start database was started enables you to draw conclusions as to the extent of the potential for optimization and where you need to focus to increase global database performance.

If sporadic performance bottlenecks occur, analyzing the wait events that occurred during the problematic period can also be very helpful.

4. How can I access wait event information?

You can use V$-Views to access wait event information. The following is important in this context:

V$SYSTEM_EVENT: Wait events accumulated system-wide since the database was started.

V$SESSION_EVENT: Wait events accumulated per session since the database was started.

V$SESSION_WAIT: Current wait events.

V$EVENT_NAME: Name and parameter of the wait events

In addition to a direct access at Oracle level, you can also use transaction "ST04 -> Detail analysis menu -> Display V$ values" or the RSORAVDV (or /SDF/RSORAVDV) to access these views. This note primarily describes the direct Oracle queries because you can sometimes no longer use R/3 if extreme performance problems occur.

5. How I can determine which wait events are defined?

This information is stored in V$EVENT_NAME. At Oracle level, you can use the following call to receive a list of all wait events:

SELECT NAME FROM V$EVENT_NAME;

You can obtain a short description of the three parameters relevant for an event using:

SELECT PARAMETER1, PARAMETER2, PARAMETER3 FROM V$EVENT_NAME
WHERE NAME = '';

6. What are idle events?

It is useful to distinguish between idle events as defined by Oracle and idle events as defined by SAP.

Idle events, as defined by Oracle, are wait events that are reported when the Oracle process has nothing to do. The idle event "SQL*Net message from client" means that a shadow process is currently waiting for the next request from the client (that is, from R/3).

Idle events, as defined by SAP, are the events that do not have any influence over database response time. These include, for example, "db file parallel write", because the related writing of the DBWR processes occurs asynchronously and a client inquiry does not have to wait for this. Those events whose times are completely covered in the context of other non-idle events (for example, "db file parallel write", which is contained in the "log file sync") are also considered idle events from an SAP point of view.

All events that are not defined by SAP as idle events are entered in the database response time and are therefore included in the core of a database performance analysis.

7. What are the most important idle events?

Idle events as defined by SAP and Oracle:

ASM background timer
class slave wait
DIAG idle wait
dispatcher timer
EMON idle wait
gcs for action
gcs remote message
ges remote message
HS message to agent
i/o slave wait
jobq slave wait
JS external job
KSV master wait
LNS ASYNC archive log
LNS ASYNC dest activation
LNS ASYNC end of log
lock manager wait for remote message
LogMiner: client waiting for transaction
LogMiner: slave waiting for activate message
LogMiner: wakeup event for builder
LogMiner: wakeup event for preparer
LogMiner: wakeup event for reader
Null event
parallel query dequeue
parallel recovery coordinator waits for cleanup of slaves
pipe get
PL/SQL lock timer
pmon timer
PX Deq Credit: need buffer
PX Deq Credit: send blkd
PX Deq: Execute Reply
PX Deq: Execution Msg
PX Deq: Index Merge Close
PX Deq: Index Merge Execute
PX Deq: Index Merge Reply
PX Deq: Join ACK
PX Deq: kdcphc_ack
PX Deq: kdcph_mai
PX Deq: Msg Fragment
PX Deq: Par Recov Change Vector
PX Deq: Par Recov Execute
PX Deq: Par Recov Reply
PX Deq: Parse Reply
PX Deq: Signal ACK
PX Deq: Table Q Normal
PX Deq: Table Q Sample
PX Deq: Txn Recovery Reply
PX Deq: Txn Recovery Start
PX Deque wait
PX Idle Wait
queue messages
rdbms ipc message
SGA: MMAN sleep for component shrink
single-task message
slave wait
smon timer
SQL*Net message from client
SQL*Net message from dblink
Streams AQ: deallocate messages from Streams Pool
Streams AQ: delete acknowledged messages
Streams AQ: qmn coordinator idle wait
Streams AQ: qmn slave idle wait
Streams AQ: RAC qmn coordinator idle wait
Streams AQ: waiting for messages in the queue
Streams AQ: waiting for time management or cleanup tasks
Streams fetch slave: waiting for txns
virtual circuit status
wait for unread message on broadcast channel
wait for unread message on multiple broadcast channels
wakeup time manager
watchdog main loop

As of Oracle 10g, idle events as defined by Oracle can be determined with the following command (WAIT_CLASS = 'Idle):

SELECT NAME FROM V$EVENT_NAME WHERE WAIT_CLASS# = 6;

Idle events as defined by SAP only:

db file parallel write
Log archive I/O
log file parallel write
log file sequential read
ARCH wait on SENDREQ

In general, all wait events for background processes are defined by SAP as idle events. The non-idle waits that are allotted to background processes (such as "db file sequential read") are therefore also not relevant from an SAP perspective. As of Oracle 10g, these wait events for the wait class "System I/O" are grouped together (WAIT_CLASS# = 9).

8. How can I determine which wait classes add to the response time and the extent to which they add to it?

As of Oracle 10g, wait events are assigned to specific wait classes such as "User I/O" or "Concurrency". You can use the following command to determine what proportion of the entire non-idle wait time is taken up by these wait classes:

SELECT
SUBSTR(WAIT_CLASS, 1, 30) WAIT_CLASS,
ROUND(TIME_WAITED/100) "TIME_WAITED (S)",
ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENT
FROM
(SELECT WAIT_CLASS, SUM(TIME_WAITED) TIME_WAITED
FROM V$SYSTEM_EVENT
WHERE WAIT_CLASS NOT IN ('Idle', 'System I/O')
GROUP BY WAIT_CLASS)
ORDER BY 2 DESC;

9. How can I determine which wait events in particular cause a high load?

V$SYSTEM_EVENT lists the accumulated values for all wait events since the database was started. You can use the following SQL command to determine the longest wait event:

SELECT EVENT, TIME_WAITED, AVERAGE_WAIT
FROM V$SYSTEM_EVENT
ORDER BY TIME_WAITED DESC;

Go through this list from top to bottom, ignoring idle events. This means that you get non idle events for which the system had to wait the longest (TIME_WAITED). You can now use this information to analyze the determined wait event more precisely.

Important: The smaller the queue time of a wait event compared to the queue time of the uppermost non idle event, the less you need to tune this wait event (at least for the global database performance).

As of Oracle 10g, you can execute the following queries to determine the most important wait events and the CPU-load since the last database start:

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 NOT IN ('Idle', 'System I/O')
UNION
SELECT 'CPU' EVENT, NULL, VALUE, NULL
FROM V$SYSSTAT
WHERE STATISTIC# = 12
ORDER BY 3 DESC)
WHERE ROWNUM <=10;

10. How can I find out which Oracle session belongs to an R/3 work process?

Determine the PID of the R/3 work process (for example, by viewing the PID column in Transaction SM50). Now search in "Transaction ST04 -> Detail analysis menu -> Oracle session" for the row with "Clnt proc." = . In this row, you can now find the relevant Oracle PID in the "PID" column.

You can determine the at Oracle level from the as follows:

SELECT SID FROM V$SESSION WHERE PROCESS = ;

11. How can I determine the wait events that the Oracle sessions are currently waiting for?

You can determine the current wait events using "ST04 -> Detail analysis menu -> Oracle session".

At database level, the following command gives you an initial overview as to which session is waiting, or using the CPU, with which (abbreviated) SQL command for which wait event:

Oracle 9i or lower:

SELECT
SUBSTR(S.SID, 1, 3) SID,
DECODE(ST.SQL_TEXT, NULL, AA.NAME,
SUBSTR(ST.SQL_TEXT, 1, 32)) SQLTEXT
SUBSTR(DECODE(SW.WAIT_TIME, 0, SUBSTR(SW.EVENT, 1, 30), 'CPU'),
1, 20) ACTION,
SW.P1 P1,
SW.P2 P2,
SW.P3 P3
FROM
V$SESSION S, V$SESSION_WAIT SW, V$SQLTEXT ST, AUDIT_ACTIONS AA
WHERE
S.STATUS = 'ACTIVE' AND S.SID = SW.SID AND
S.SQL_HASH_VALUE = ST.HASH_VALUE (+) AND
S.SQL_ADDRESS = ST.ADDRESS (+) AND ST.PIECE (+) = 0 AND
AA.ACTION = S.COMMAND
ORDER BY
S.SID;

Oracle 10g or higher:

SELECT
SID,
DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
S.P1 P1,
S.P2 P2,
S.P3 P3,
SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT),
1, 45) SQLTEXT
FROM
V$SESSION S, V$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE
S.STATUS = 'ACTIVE' AND
S.SQL_ID = SS.SQL_ID (+) AND
AA.ACTION = S.COMMAND AND
S.TYPE = 'USER'
ORDER BY
S.SID;

If you want more exact details for an Oracle session with the Oracle SID, you can use the following statement:

SELECT
S.SID SID,
DECODE(ST.SQL_TEXT, NULL, AA.NAME, ST.SQL_TEXT) SQLTEXT,
SUBSTR(DECODE(SW.WAIT_TIME, 0, SW.EVENT, 'CPU'),
1, 20) ACTION,
SW.P1 P1,
SW.P2 P2,
SW.P3 P3
FROM
V$SESSION S, V$SESSION_WAIT SW, V$SQLTEXT ST, AUDIT_ACTIONS AA
WHERE
S.SID = 14 AND S.SID = SW.SID AND
S.SQL_HASH_VALUE = ST.HASH_VALUE (+) AND
AA.ACTION = S.COMMAND
ORDER BY
ST.PIECE;

This gives you the complete SQL statement, which is split up into several rows if it exceeds a certain length.

12. How should I interpret the wait time?

The WAIT_TIME column from V$SESSION_WAIT has the following meaning:

0: The wait event is currently active.

> 0: The wait event has already been terminated and was active for the specified number of 1/100 seconds.

-1: The wait event has already been terminated and was active for less than 1/100 seconds.

-2: The wait event has already been terminated, but no time information is available because timed_statistics is set to FALSE;

Caution: The "waittime (sec)" column in "ST04 -> Detail analysis menu -> Oracle session" does not refer to the WAIT_TIME of V$SESSION_WAIT, but to SECONDS_IN_WAIT! This column is therefore only suitable for an evaluation under certain conditions.

13. How can I trace the Wait Events of a session?

You can create an ORADEBUG trace with trace level 10 or 12 to determine what a particular Oracle Session is waiting for in a particular period. This may be useful if an SAP transaction takes an unusually long time on the database, without there being any obvious explanation for this in the SAP System. For a thorough description of using ORADEBUG, see Note 613872.

You can also obtain a rough overview of the activities of a session using snapshots on V$SESSTAT and V$SESSION_EVENT. This allows you to carry out the following statement twice with a given interval (for example, a minute) to determine the proportion of individual wait events and CPU in the intervening period using the data received:

SELECT SUBSTR(EVENT, 1, 45) EVENT, TOTAL_WAITS, TIME_WAITED
FROM V$SESSION_EVENT
WHERE SID =
UNION
SELECT 'CPU', NULL, VALUE
FROM V$SESSTAT
WHERE SID = AND STATISTIC# = 12
ORDER BY 3 DESC;

Now calculate the TIME_WAITED difference of the snapshots for the individual rows and divide this result by 100. This gives you the number of seconds that the session required for the individual wait events or CPU. This result can also be divided by the difference of the TOTAL_WAITS to determine the average duration of every wait event.

14. Where do I find historical Wait event information?

Using the report /SDF/RSORAVSE, you can determine the wait events accumulated hour by hour for past periods. It makes sense in this case to display the "Idle Events" using the button "Without Idle Events". Double-click on the day to get the statistics in hours.

The report /SDF/RSORAVSH must be scheduled hourly for the data to be collected on an hourly basis. With earlier releases/patches, the reports were still called RSORAVSE and RSORAVSH. Refer also to Notes 549298, 560475 and 564446 for information about these reports.

The bug described in Note 607415 can result in incorrect average values in RSORAVSE. Implement the correction instructions or a relevant Support Package to eliminate the problem.

As of Oracle 10g, you can use V$ACTIVE_SESSION_HISTORY to display historical data of active sessions. Each second, the system checks whether a session is currently active (that is, it is waiting for a non-idle wait event or is consuming CPU). If it is active, an entry is created in V$ACTIVE_SESSION_HISTORY. Use the following command to obtain the last 20 activities of a session , including the respective SQL statement:

SET LINESIZE 120
SELECT * FROM
(SELECT
TO_CHAR(ASH.SAMPLE_TIME, 'dd.mm.yyyy hh24:mi:ss')
"TIMESTAMP",
DECODE(ASH.WAIT_TIME, 0, SUBSTR(ASH.EVENT, 1, 30), 'CPU')
ACTION,
SUBSTR(O.OBJECT_NAME, 1, 30) OBJECT_NAME,
SUBSTR(S.SQL_TEXT, 1, 30) SQLTEXT
FROM
V$ACTIVE_SESSION_HISTORY ASH,
V$SQL S,
DBA_OBJECTS O
WHERE
ASH.SQL_ID = S.SQL_ID (+) AND
ASH.CURRENT_OBJ# = O.OBJECT_ID (+) AND
ASH.SESSION_ID =
GROUP BY
ASH.SAMPLE_TIME, ASH.WAIT_TIME, ASH.EVENT,
O.OBJECT_NAME, S.SQL_TEXT
ORDER BY ASH.SAMPLE_TIME DESC
)
WHERE ROWNUM <=20;

Sometimes - for example, in connection with enqueue waits - it makes sense to determine which objects are principally associated with a certain wait event or group of wait events. As of 10g, you can use the following query on V$ACTIVE_SESSION_HISTORY to determine the top objects as regards the wait event (for example, "enq%" for enqueue waits):

SELECT * FROM
(SELECT
SUBSTR(ASH.EVENT, 1, 30) EVENT,
COUNT(*) "COUNT",
SUBSTR(O.OBJECT_NAME, 1, 30) OBJECT_NAME
FROM
V$ACTIVE_SESSION_HISTORY ASH,
DBA_OBJECTS O
WHERE
ASH.CURRENT_OBJ# = O.OBJECT_ID (+) AND
ASH.WAIT_TIME = 0 AND
ASH.EVENT LIKE ''
GROUP BY ASH.EVENT, O.OBJECT_NAME
ORDER BY 2 DESC
)
WHERE ROWNUM <=20;

Note that V$ACTIVE_SESSION_HISTORY only covers a limited period of time and is therefore not always representative. You can obtain snapshots of earlier periods in DBA_HIST_ACTIVE_SESS_HISTORY. As of Oracle 10.2, you can also create detailed ASH reports in Oracle. See Note 853576 for further details.

15. How can I optimize the individual wait events?

The following provides explanations, rules of thumb, analysis steps and optimization options for the most important wait events. In addition, the three relevant wait event parameters are specified under "Parameter". Note that implementing these recommendations requires detailed knowledge of Oracle. In addition, note that many of the wait events below can also be optimized with SQL statement tuning (Note 766349).

db file sequential read

• Meaning: Waiting for a block requested by the operating system
• Parameter: File number/Block number/1
• Rule of thumb: The average value for the wait time displayed in V$SYSTEM_EVENT should not exceed 15ms.
• Optimization steps:

The waiting time for "db file sequential read" consists mainly of disk access times. Often, blocks are stored at levels between (for example, file system cache, I/O subsystem cache). Therefore, the average "db file sequential read" time consists of the comparatively long disk access times (usually, approximately 10 ms) and the short cache access times (usually shorter than 1 ms). As of Oracle 10g, you can use V$EVENT_HISTOGRAM to recognize these two peak areas.

Check whether the system uses the existing physical memory in a suitable way; if required, enlarge the Oracle buffer pool (but ensure that no paging occurs). In individual cases, due to the recommended deactivation of the file system cache (FILESYSTEMIO_OPTIONS = SETALL), the positive effect of a large file system cache may be lost and the "db file sequential read" times may be longer. In this case, you must enlarge the Oracle buffer pool to even out the removal of the file system cache.

Optimize processing-intensive SQL statements using a large number of disk reads (Note 766349)

With suboptimal, average "db file sequential read" times, you can use V$FILESTAT and - as of Oracle 10g - V$FILE_HISTOGRAM to localize hot spots in the disk area. In cooperation with your hardware partner, you should also check whether the hardware works correctly and the setup is optimum. Also refer to Note 793113 for an optimal I/O configuration.

Note that even apparently good access times (8 ms, for example) may result in poor performance if the times previously observed were significantly lower (2 ms, for example). You should therefore always compare the current average values with past values (for example, using the /SDF/RSORAVSE report).

db file parallel read

• Meaning: Waiting for blocks to be read in parallel from the disk
• Parameter: File number/block numbers/requests
• Optimization steps:

The wait event "db file parallel read" occurs in association with a recovery. As of Oracle 9i, blocks can also be read in parallel by the disk at another location, for example, as part of index prefetches. For information about tuning this event, see the relevant section of "db file sequential read".

db file scattered read

• Meaning: Waiting for several blocks to be read from the disk
• Parameter: File number/block number/number of groups
• Optimization steps:

The wait event "db file scattered read" always occurs where several physically sequential blocks are read from disk. Typical cases are Full Table Scans or Index Fast Full Scans. In individual cases, as part of prefetch operations, db file scattered read" waits may occur with other accesses (for example, Index range scans) where usually only eight or less blocks are read with a request. If possible, you should avoid full table scans in the R/3 environment. Therefore, check which statements execute full table scans and optimize them. Though, a high number of disk reads in the SQL Cache ("ST04 -> Detail analysis menu -> SQL requests") is normally a good indication. You should therefore specifically check all SQL statements for Full Table Scans that are at the very top in the SQL cache in relation to disk reads.

With the following statement, you can determine the objects at Oracle level, that currently have the most blocks due to full table scans or index fast full scans in the Oracle buffer pool:

SELECT * FROM
(SELECT SUBSTR(O.OWNER, 1, 15) OWNER,
SUBSTR(O.OBJECT_NAME, 1, 35) OBJECT,
COUNT(*) BLOCKS,
DECODE(O.OBJECT_TYPE, 'TABLE', 'FULL TABLE SCAN',
'INDEX', 'FAST FULL SCAN',
'OTHER') "SCAN TYPE"
FROM DBA_OBJECTS O, X$BH B
WHERE B.OBJ = O.DATA_OBJECT_ID AND
STANDARD.BITAND(B.FLAG, 524288) > 0 AND
O.OWNER != 'SYS'
GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
ORDER BY COUNT(*) DESC)
WHERE ROWNUM <=20;

As of Oracle 9i, the view V$SQL_PLAN is also available, which can be used to determine SQL statements that use full table scans or index fast full scans. The following statement returns the 20 SQL statements that are responsible for the most disk reads in the context of full table scans and index fast full scans:

SELECT * FROM
(SELECT SUBSTR(SA.SQL_TEXT, 1, 68) SQL_TEXT,
SA.DISK_READS DISK_READS
FROM V$SQLAREA SA WHERE
(SA.ADDRESS, SA.HASH_VALUE) IN
(SELECT ADDRESS, HASH_VALUE FROM V$SQL_PLAN
WHERE OPERATION = 'TABLE ACCESS' AND
OPTIONS = 'FULL' OR
OPERATION = 'INDEX' AND
OPTIONS LIKE 'FAST FULL%')
ORDER BY 2 DESC)
WHERE ROWNUM <=20;

As of Oracle 10g, V$SEGMENT_STATISTICS contains information about the number of Full Table Scans or Index Fast Full scans per segment:

SELECT * FROM
( SELECT OWNER, OBJECT_NAME, VALUE
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'segment scans'
ORDER BY VALUE DESC )
WHERE ROWNUM <=20;

If you are using AIX, see also Note 610357.

Oracle 9i or lower: direct path read / direct path read (lob)
Oracle 10g or higher: direct path read / direct path read temp

• Meaning: Waiting for blocks that are read directly from the disk drive to avoid the Oracle buffer pool
• Parameter: Descriptor/DBA/number of blocks
• Rule of thumb: The total wait time for these wait events should not exceed 5% of the relevant database time.
• Optimization steps:

Direct path operations, with which the Oracle buffer pool is read, are used when PSAPTEMP is accessed (sortings, hash joins, bitmap operations), in the case of parallel query and when LOB data that is not cached is accessed. Depending on your Oracle release, these activities are assigned to the wait events as follows:

PSAPTEMP accesses: direct path read (9i or earlier) / direct path read temp (10g or higher)

Parallel query: direct path read

LOB accesses: direct path read (lob) (9i or earlier) / direct path read (10g or higher)

The same checks as those described under "db file sequential read" can be carried out to optimize this wait event - with particular emphasis on PSAPTEMP.

The number of PSAPTEMP accesses can be reduced by increasing the PGA (see Note 789011).

Also check whether many unnecessary parallel query operations are carried out (see Note 651060).

You can reduce or avoid LOB-related direct path accesses by caching LOBs as described in Note 563359.

As described in Note 659946, also use V$SQL_WORKAREA_ACTIVE to check whether large hash joins, sortings or bitmap operations have been executed, which can be optimized.

Oracle 9i or lower: direct path write / direct path write (lob)
Oracle 10g or higher: direct path write / direct path write temp

• Meaning: Writing of a block directly on the hard drive to avoid the Oracle buffer pool
• Parameter: Descriptor/DBA/number of blocks
• Rule of thumb: This should not appear under the Top 10 wait events in terms of the cumulative queue time
• Optimization steps:

The "direct path write" waits correspond to the above "direct path read" waits. Depending on your Oracle release, these activities are assigned to the wait events as follows:

PSAPTEMP accesses: direct path write (9i or earlier) / direct path write temp (10g or higher)

Parallel query: direct path write

LOB accesses: direct path write (lob) (9i or earlier) / direct path write (10g or higher)

To optimize the accesses, proceed as described under "direct path read".

Log file sync

• Meaning: Waiting until the LGWR has written all data from the redo buffer to the online redo log (for example, as part of a commit, if the DBWR triggers the LGWR because a dirty block that is to be written to the hard disk has not yet been entered in the redo logs, or if LGWR is triggered in another way to write the redo buffer to the online redo log).
• Parameter: Buffer number / - / -
• Rule of thumb: The average value for the wait time displayed in V$SYSTEM_EVENT should not exceed 15ms.
• Optimization steps:

In most cases the increased "log file sync" times are related to I/O problems for the LGWR process. Therefore you must check the operating system and the hardware you are using for bottlenecks during write-access to the Online Redo log. Since increased access times are generally caused by a bottleneck in the operating system or hardware / input/output area, please consult your operating system and hardware partner for a more specific analysis. Also check whether the current Oracle configuration can be optimized regarding the I/O configuration according to Note 793113.

If "log file sync" waits occur when you change large data volumes (for example, loading or compressing data), check whether indexes can be temporarily dropped or used with NOLOGGING (see Note 853084 for example).

It may also be advisable to use NOLOGGING for other I/O intensive operations according to Note 806554 in order to minimize "log file sync" wait situations.

If increased "log file sync" times occur during an online backup with BACKINT, check whether backup_dev_type = util_file_online can be set instead of util_file. This means that tablespaces are set to backup mode, which reduces the load on the online redo log.

As of Oracle 10g, Oracle gives you the option of controlling the performance of "log file sync" using the parameter COMMIT_WRITE. Due to the fact that the consistency of the application can no longer be guaranteed if settings are changed, changing this parameter in a way that deviates from the standard system is generally not allowed in the SAP environment.

In individual cases, increased "log file sync" times can also be caused by other effects. This means that an ARCH process can hold a control file enqueue over a long period of time (see Note 745639), which means that the LGWR process, which also requires this enqueue cannot carry out a log switch. As a result, all DML operations must wait for "log file sync" until the ARCH process has released the enqueue.

log buffer space

• Meaning: Waiting for freespace in the redo buffer
• Optimization steps:

If the size of the redo buffer (parameter log_buffer) is less than 1 MB, you can increase this memory area to 1 MB. However, frequent "log buffer space" wait events are generally triggered by I/O problems with the LGWR. You should therefore refer to the tuning notes for the "log file sync" wait event.

log file switch

• Meaning: Waiting for a redo log switch
• Optimization steps:

In the case of "log file switch (archiving needed)", check in accordance with Note 391 whether an Archiver Stuck has occurred.

In the case of "log file switch (checkpoint incomplete)", see Note 79341 to solve the "checkpoint not complete" situation.

In the case of "log file switch (private strand flush incomplete)", refer to Note 793113 and optimize the DBWR performance.

In the case of "log file switch completion", you have to wait for a log switch to end. Provide a sufficient redo log size to ensure that there are not too many log switches (more than one per minute) for optimizing this wait event. Note also the optimization possibilities in the "log file sync" section because during a log switch, the system also flushes the redo buffer. Increased "log file switch completion"-times may also be a consequence of "checkpoint not complete" problems. Therefore, check if "log file switch (checkpoint incomplete)"-waits occur, and if so, proceed as described in Note 79341.

log file parallel write

• Meaning: LGWR waiting for blocks to be written to disk
• Parameter: File/block/ I/O requests
• Optimization steps:

Tune the LGWR I/O as described in the context of the "log file sync" wait event.

Oracle 9i or lower: buffer busy waits
Oracle 10g or higher: read by other session / buffer busy waits

• Meaning: Waiting for a block because it is currently being imported or changed by another session.
• Parameter: File number/Block number/ID
• Rule of thumb: The average value for the wait time in V$SYSTEM_EVENT should not exceed 40 ms (or 15 ms for read-write-cache-memory).
• Optimization steps:

If the rule of thumb is exceeded, this is generally due to problems in the I/O area. You should therefore also carry out the same checks in this case as described in the section "DB file sequential read".

The ID specified as a third parameter gives more exact information as to why the block cannot be accessed. It is generally the case that: if the first number is a 1, just the block is read. If the first number is a 2, the block is kept in an incompatible mode.

As of Oracle 10g, the name of the wait event tells you whether it is a read wait event ("read by other session") or a compatibility wait event ("buffer busy waits").

If the waits occur frequently on certain blocks, you can use the following SELECT to determine the corresponding segment ( and are parameters 1 and 2 of the wait event). In addition, the HDR column contains information as to whether it is a header block or not:

SELECT SUBSTR(SEGMENT_NAME, 1, 30), SEGMENT_TYPE,
DECODE( - BLOCK_ID + EXTENT_ID, 0, 'YES', 'NO') HDR
FROM DBA_EXTENTS
WHERE FILE_ID = AND
BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

You can use the following SELECT to determine the data files in which most of the buffer busy waits occurred:

SELECT * FROM
(SELECT COUNT, FILE#, SUBSTR(NAME, 1, 50) FILE_NAME
FROM X$KCBFWAIT, V$DATAFILE
WHERE INDX + 1 = FILE#
ORDER BY COUNT DESC)
WHERE ROWNUM <=20;

As of Oracle 9i, V$SEGMENT_STATISTICS contains information as to how many Busy Wait buffers occurred for every segment. In the following query, for example, you can determine the 20 segments with most Buffer Busy Waits:

SELECT * FROM
(SELECT OBJECT_NAME, VALUE FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'buffer busy waits'
ORDER BY VALUE DESC )
WHERE ROWNUM <=20;

The Oracle view V$WAITSTAT contains an overview of how often Buffer Busy Waits occurred in the individual block types since the database was started as well as the average length of the waits. Depending on the type of the block, take the following measures:

Data block: If INSERTs run onto the "buffer busy waits", the number of the FREELISTs can be increased for the affected segment. This change can be carried out dynamically - it is not necessary to reorganize the object. The FREELISTs are assigned to the Oracle processors according to the algorithm MOD(PID, #FREELISTs) + 1 (where PID denotes the Oracle-PID from V$PROCESS).

If this change does not result in a successful outcome, you can increase the number of FREELIST GROUPs. It is useful to use a different prime number for the number of FREELISTS and FREELIST GROUPs because the calculation of the used FREELIST GROUP in a non-RAC environment occurs according to the same algorithm as the FREELIST calculation.

Another solution is to change to ASSM (see Note 620803). Otherwise you must optimize the SQL statement, the application and/or the I/O subsystem. You may need to increase the db_block_buffers Oracle parameter.

Segment header: Use several FREELIST GROUPs for the segment in question. You can only change the number of FREELIST GROUPs when reconstructing the object. Another solution is to change to ASSM (see Note 620803).

Undo block: The I/O times for the rollback segments must be optimized.

Undo header: There are too few rollback segments. Increase the number of the rollback segments to at least a quarter of the number of work processes.

"Buffer busy" waits that have a significantly high average wait time may be the consequence of a simultaneous "archiver stuck". In this case, the "buffer busy" waits are only a consequence of the problem, and do not require further analysis.

Buffer busy waits may be caused by "log buffer space" waits. Therefore, use (for example) V$ACTIVE_SESSION_HISTORY on Oracle 10g to check whether "buffer busy waits" usually occur at the same time as "log buffer space" waits.

Buffer busy waits may also occur due to "async disk IO" waits or "Data file init write" waits caused by AUTOEXTEND operations. In this case, these are "File Header Block" waits in V$WAITSTAT.

write complete waits.

• Meaning: Waiting until the DBWR has written a necessary block to the disk
• Parameter: File number/Block number/ID
• Rule of thumb: Should not be in the top 10 of V$SYSTEM_EVENT
• Optimization steps:

You must tune the DBWR performance. Check whether the I/O subsystem can be tuned, whether the database buffer pool (db_block_buffers) is set too small and whether it would be useful to define several DBWR processes.

free buffer waits

• Meaning: Waiting for the DBWR to write dirty blocks to disk so that they can be replaced by new blocks
• Parameter: File number/Block number/ID
• Rule of thumb: Should not be in the top 10 of V$SYSTEM_EVENT
• Optimization steps:

"Free buffer" waits are an indication that the DBWR processes are not writing changed blocks to the disk quickly enough. Therefore, check the Oracle I/O configuration as described in Note 793113. Also ensure that the Oracle buffer pool is large enough (refer to Note 789011).

If you determine that not all DBWR processes are continually active, dispite very large "free buffer" waits, you can reduce the Oracle parameter FAST_START_MTTR_TARGET as a test, to force the DBWR processes to become more active. However, bear in mind that reducing this parameter causes a greater write-load on the DBWR processes, which can be counterproductive.

Oracle 9i or lower: latch free
Oracle 10g or higher: latch: / latch free

• Meaning: Waiting for a latch to be released; a latch is a very basic serialization mechanism that can be used to prevent data structures in the SGA being accessed simultaneously.
• Parameter: latch address/latch number/number of sleeps
• Optimization steps:

For more information, see Note 767414.

Oracle 9i or lower: enqueue
Oracle 10g or higher: enq: -

• Meaning: waiting for an Oracle-Lock
• Parameter: Type/ID1/ID2
• Optimization steps:

For more information, see Note 745639.

library cache lock
library cache load lock
library cache pin

• Meaning: Waiting for exclusive access to data of the library cache
• Optimization steps:

If accesses are hanging on certain tables on these wait events, the problem can be triggered by a hanging Oracle session. For more information, see Note 20071.

If you are using Oracle <= 8.1.7.1 with 64-bit on UNIX, see also Note 376905.

If you notice "library cache pin" waits with Oracle 9.2.0.2 or 9.2.0.3, refer also to Note 649876.

Also check for Oracle <= 9.2.0.5 whether you have the bug described in Note 768308, and import a fix.

If the problem occurs with Oracle 9.2.0.6, refer to Note 846364.

If these wait events occur when running catalog scripts (Note 582427), make sure that no parallel activities are running on the database (for example by monitoring tools such as PATROL).

If a deadlock of TX enqueues or library cache locks occurs with row cache locks in connection with parallel index rebuilds, note the bug described in Note 904188.

Deadlocks of "row cache lock" and "library cache lock" may also be triggered in connection with ALTER INDEX ... COALESCE because unlike in the standard system behavior, this operation requests a "row cache lock" first and then a "library cache lock". This incorrect behavior is addressed in Oracle bug 6051177.

"library cache lock" waits can appear for any access if a VALIDATE STRUCTURE CASCADE ONLINE, an INDEX COALESCE or an EXPLAIN and an ALTER, ANALYZE STATISTICS or DBMS_STATS command is executed on the same segment (or on a table and relevant index). In this case, one of the two sessions must be cancelled to allow the table to be accessed again.

You can use the following statement to determine the sessions that are currently retaining a library cache lock and that are blocking other sessions:

SELECT DISTINCT LOCK_A.KGLLKSNM
FROM X$KGLLK LOCK_A, X$KGLLK LOCK_B
WHERE
LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL AND
LOCK_A.KGLLKREQ = 0 AND
LOCK_B.KGLLKSES IS NOT NULL AND
LOCK_B.KGLLKREQ > 0;

As of Oracle 10g, you can also use the BLOCKING_SESSION entry in V$SESSION to determine the session that is blocking another session using a library cache lock (or another lock). See also the corresponding SELECT in Note 20071.

"Library cache lock" waits that last a long time under Oracle 9.2.0.8 or 10.2.0.2 may also be a consequence of the bug described in Note 971261.

Another result of an ORA-04031 error may be waits on "library cache pin" or "library cache load lock". Therefore, you must check in the alert log whether one of these errors occurred during the period in question and refer to Note 869006.

If a session waits for more than five minutes for "library cace lock" or "library cache pin", the session is terminated with the error ORA-04021. If a deadlock occurs, the system returns error ORA-04020.

row cache lock

• Meaning: Waiting for exclusive row cache access
• Parameter: Cache ID/lock mode/request
• Optimization steps:

High "row cache lock" wait times are usually triggered by Oracle bugs. Check whether you have one of the bugs described in Notes 738641 and 768308.

If a deadlock of TX enqueues or library cache locks occurs with row cache locks in connection with parallel index rebuilds, note the bug described in Note 904188.

When you use a large shared pool and a high rate of CREATE, DROP and TRUNCATE statements (such as in the BW environment, for instance), certain accesses to the row cache can slow down significantly because the management structures are getting bigger and bigger in the shared pool. In such a case, you can perform a flush of the shared pool as a workaround:

ALTER SYSTEM FLUSH SHARED_POOL;

The first argument of the "row cache lock" event contains the ID of the specified sub-cache. You can use this value to determine the row cache area in question, as follows:

SELECT * FROM V$ROWCACHE WHERE CACHE# = '';

In many cases, you can use the following statement to determine the sessions that are currently holding a row cache lock and the respective sub-cache:

SELECT S.SID, R.LOCK_MODE, SUBSTR(R.CACHE_NAME, 1, 30) CACHE_NAME
FROM V$ROWCACHE_PARENT R, V$SESSION S
WHERE R.SADDR = S.SADDR;

As of Oracle 10g, you can determine the lock holder on the basis of the column BLOCKING_SESSION in V$SESSION. See the corresponding SQL statement in Note 20071.

A permanent lock on the row cache can also be triggered by the network problem described in Note 20071.

Large-scale "row cache lock" wait situations can also occur in connection with an archiver stuck ("log file switch (archiving needed", Note 391) and are only a subsequent problem in this case.

If "row cache lock" deadlocks occur with Oracle 10g, refer to Note 1017970.

If "row cache lock" waits occur on partitions on Oracle 10.2.0.2 in connection with SHRINK SPACE COMPACT, see Note 1121838.

Deadlocks of "row cache lock" and "library cache lock" may also be triggered in connection with ALTER INDEX ... COALESCE because unlike in the standard system behavior, this operation requests a "row cache lock" first and then a "library cache lock". This incorrect behavior is addressed in Oracle bug 6051177.

An ORA-04031 error may also cause "row cache lock". Therefore, you must check in the alert log whether one of these errors occurred during the period in question and refer to Note 869006.

If you experience long wait times with "row cache lock", the system may display the following log entries: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!".

Null event

• Meaning: Unspecific wait event
• Parameter: -
• Optimization steps:

"Null event" is usually an idle event. There is therefore normally no handling requirement. However, in exceptional cases it is possible that a session may hang for a longer period of time on "Null event" (with WAIT_TIME = 0).

A possible scenario for a hanging "Null event" is an Outer Join (for instance, using "(+)" in the SQL statement) on Oracle 9i. In this case, see Note 626172.

Hints such as USE_CONCAT can cause a "Null event" on Oracle 9i. Problems like this can be solved in the CO area, for instance, by implementing the correction instructions 552217 from Note 545932.

Otherwise, contact SAP Support if statements appear on "Null event" for a prolonged period.

db file parallel write

• Meaning: waiting of the DBWR, until blocks have been written to disk
• Optimization steps:

Since the writing takes place asynchronously, this event is usually not critical. If the DBWR really does have problems with the write performance, this appears in wait events such as "free buffer waits" or "log file switch (checkpoint incomplete)", to which you can then react as described.

Depending on your Oracle version and operating system, the the logged times may be too low (for example, HP-UX) or too high (for example, AIX).

The times must always be set in relation to the number of written requests (P1). If there is a greater number of written requests, runtimes in the second area are acceptable.

DB file single write

• Meaning: Writing individual blocks to the hard disk
• Parameter: File number/Block number/1
• Optimization steps:

Optimization steps: "db file single write" waits occur if the header of a data file is changed by the background process during ALTER TABLESPACE BEGIN BACKUP or ALTER TABLESPACE END BACKUP. Exceptions concerning this wait event are triggered by I/O problems. See the information under "log file sync".

rdbms ipc reply

• Meaning: shadow process waiting for a background process
• Parameter: PID of the background process/timeout in seconds/-
• Optimization steps:

If a session hangs for a prolonged period on this wait event, the background process for which the system is waiting can be determined using the PID of the background process :

SELECT NAME FROM V$BGPROCESS WHERE PADDR =
(SELECT ADDR FROM V$PROCESS WHERE PID = );

You can then continue to analyze its activity (for example, using an ORADEBUG trace as described in Note 613872).

"rdbms ipc reply" waits usually occur with BEGIN BACKUP, TRUNCATE and DROP operations for which the CKPT process must execute a checkpoint. Furthermore, there have to be "rdbms ipc reply" waits in the context of a buffer pool flush. There are also "rdbms ipc reply" waits on the DBWR in the context of RESIZE operations.

If "rdbms ipc reply" occurs with TRUNCATE on Oracle 9.2.0.4 or lower, refer to Note 695841.

With Oracle 9i or lower, the design flaws described in Note 758989 result in increased "rdbms ipc reply" times in the sequence of checkpoints for BEGIN BACKUP, DROP and TRUNCATE. Oracle 10g solves this problem. You can alleviate the problem by reducing the buffer pool, but often you do not want this because of a deterioration in performance elsewhere. For information on optimizing BEGIN BACKUP runtimes, see also Note 875477.

In BW, you can convert TRUNCATEs on small tables into DELETEs by maintaining the ORA_TABTRUNCATE_MINIMUM parameter in RSADMIN (Note 840553).

The duration of "rdbms ipc reply" waits may also be associated with a poor I/O performance. In this context, refer to Note 793113 and, if necessary, configure several DBWR processes.

"rdbms ipc reply" waits are closely associated with CI enqueues (Note 745639). A session that waits for "rdbms ipc reply" always allocates the CI enqueue. If necessary, further sessions must wait for the CI enqueue.

refresh controlfile command

• Meaning: Refreshing the controlfile size information
• Optimization steps:

This wait event is generally an uncritical event. It is involved with control file information in conjunction with accesses to X$ views (and the V$ views based on this) and should not last any longer than a few milliseconds. Significantly longer wait times have thus far only been observed on AIX if the Oracle kernel extension (pw-syscall) was loaded in various different versions in parallel or if a reboot was forgotten after the extension was imported. See Note 328822.

SQL*Net message to client
SQL*Net message to dblink
SQL*Net more data to client
SQL*Net more data to dblink
SQL*Net message from client
SQL*Net more data from client
SQL*Net message from dblink
SQL*Net more data from dblink
SQL*Net break/reset to client
SQL*Net break/reset to client

• Meaning: Communication between the shadow process and the client
• Parameter: Driver ID/Bytes/-
• Optimization steps:

Wait events such as "SQL*Net message from client" or "SQL*Net message from dblink" are categorized as idle events, but (just like the other SQL*Net wait events) they may be indicative of communication problems between Oracle and the Oracle client (that is, SAP).

Note that it is normal that "SQL*Net message from client" appears high up in the V$SYSTEM_EVENT, because this is the main idle event for Oracle shadow processes.

If the system often waits for these events DURING THE RUNTIME of transactions, you should check your network configuration. As well as operating system tools such as PING, the SAP tool NIPING (Note 500235) can also be used to analyze the network.

You must also check that the Oracle net configuration is correct (Note 562403). When you use Oracle 9i or lower, make sure that TCP.NODELAY in protocol.ora, .protocol.ora and/or sqlnet.ora is set correctly (Notes 72638 and 198752).

If you are in any doubt as to whether TCP.NODELAY is drawn correctly, you can draw an Oracle net client trace with SUPPORT level (Note 562403) to obtain more accurate information. If TCP.NODELAY is drawn correctly, an entry such as the following must appear in the trace file that is created:

nttcon: got tcp.nodelay = 1

memory. If this entry is missing, and instead a message such as

ntvllt: No network parameter file found

is logged, there are problems when you access protocol.ora or sqlnet.ora. As well as using .protocol.ora as described in Note 198752, instead of protocol.ora, the problem can also be caused by incorrectly installed client libraries. Refer to Note 180430 and the sub-notes referred to there for a description of the installation of the Oracle client software.

Small SDU sizes are another reason for "SQL*Net more data to client" and "SQL*Net more data from client" ("Session Data Unit") occurring frequently, whereby a larger dataset is transferred through the network in several small packages. The SDU values in tnsnames.ora and listener.ora can be increased in this case (Note 562403).

"SQL*Net more data from dblink" also contains the time that the remote session requires to procure the data. Therefore, if the "SQL*Net more data from dblink" times increase, check if the processing of the database link queries on the remote database can be optimized.

index block split

• Meaning: Waiting for the split of an index block as part of an INSERT.
• Parameter: Root DBA/level/child DBA
• Rule of thumb: This wait event should never appear under the top 10 wait events. A process must never wait longer than a fraction of a second for "index block split".
• Optimization steps:

If this wait event occurs, it is usually an individual problem with an index through which an Oracle session goes into a loop and waits permanently for "index block split". Check whether a REBUILD ONLINE for the affected index solves the problem. If not, open an SAP customer message for a more thorough analysis.

io done

• Meaning: Waiting for a synchronous write I/O to finish
• Optimization steps:

The event "io done" only occurs if a synchronous I/O is used. Therefore, check (as described in Note 793113) whether an asynchronous I/O can be used and whether the parameters DISK_ASYNCH_IO and FILESYSTEMIO_OPTIONS are set correctly.

imm op

• Meaning: Waiting for an IMMEDIATE I/O request to a slave process to end.
• Optimization steps:

The event "imm op" may occur if I/O slaves are used, that is, if parameters such as DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES are used. Generally, the "imm op" event does not pose a problem.

If DBWR_IO_SLAVES are set to 1 or higher, we recommend that you check whether you can use multiple DBWR processes instead (DB_WRITER_PROCESSES parameter).

BACKUP_TAPE_IO_SLAVES is set to TRUE in the case of a RMAN backup if dedicated I/O slave processes are to copy backup write processes to tape and not the Oracle shadow processes. In this case, "imm op" waits only affect the backup runtime, but not the live system.

index (re)build online start
index (re)build online cleanup
index (re)build online merge

• Meaning: Wait situations during an ALTER INDEX REBUILD ONLINE
• Parameter: Object ID / - / -
• Optimization steps:

The wait events may occur if the system is waiting for an as yet uncommitted change during a REBUILD ONLINE (see Note 869521). Therefore, avoid running REBUILD ONLINE in parallel to a large number or long-time uncommitted changes to the corresponding table.

ksu process alloc latch yield

• Optimization steps:

Optimization steps: If the problem occurs with Oracle 9.2.0.7, implement the correction from Note 894078.

checkpoint completed

• Meaning: Waits for a checkpoint to be completed
• Optimization steps:

The "checkpoint completed" Wait event occurs if a session explicitly has to wait for a checkpoint to finish in certain situations (for example, when stopping or sometimes even starting up the database). To optimize this, proceed as described in the process for the wait events "checkpoint not complete".

Oracle 9i or lower: sbtinit / sbtopen / sbtread / sbtwrite / sbtclose / sbtinfo / sbtremove / sbtbackup / sbtclose2 / sbtcommand / sbtend / sbterror / sbtinfo2 / sbtinit2 / sbtread2 / sbtremove2 / sbtrestore / sbtwrite2 / sbtpcbackup / sbtpccancel / sbtpccommit / sbtpcend / sbtpcquerybackup / sbtpcqueryrestore / sbtpcrestore / sbtpcstart / sbtpcstatus / sbtpcvalidate

Oracle 10g or higher: Backup: sbtinit / backup: sbtopen / backup: sbtread / backup: sbtwrite / backup: sbtclose / backup: sbtinfo / backup: sbtremove / backup: sbtbackup / backup: sbtclose2 / backup: sbtcommand / backup: sbtend / backup: sbterror / backup: sbtinfo2 / backup: sbtinit2 / backup: sbtread2 / backup: sbtremove2 / backup: sbtrestore / backup: sbtwrite2 / backup: sbtpcbackup / backup: sbtpccancel / backup: sbtpccommit / backup: sbtpcend / backup: sbtpcquerybackup / backup: sbtpcqueryrestore / backup: sbtpcrestore / backup: sbtpcstart / backup: sbtpcstatus / backup: sbtpcvalidate / RMAN backup & recovery I/O

• Meaning: Wait situations during RMAN backup
• Optimization steps:

The wait events that begin with "sbt" (Oracle 9i or lower) or "Backup: sbt" (Oracle 10g or higher) are associated with wait situations during a RMAN backup. This means they only affect the backup runtime, but not the SAP database accesses. Because of this, you can ignore these wait events when you analyze the performance of an SAP system.

Wait for shrink lock2 (Oracle 10g or higher)

• Meaning: A shrink is waiting for an enqueue to be released.
• Optimization steps:

"Wait for shrink lock2" occurs in a shrink (Note 910389) if a TM enqueue (Note 766349) on the segment is already being held and requested by other sessions at the same time. For this reason, avoid carrying out other activities on the table in question during the shrink.

sort segment request

• Meaning: Waiting for the allocation of a sort segment
• Optimization steps:

This wait event occurs when the SMON process is very busy, and is no longer able to quickly process the incoming requests. This might be the case, for example, if large rollback activities are carried out.

Even if the SMON process executes space transactions, it can lead to follow-on problems such as wait situations at "sort segment request". In this case, refer to the "TYPE = ST" section from Note 745639.

Older Oracle releases sometimes contain errors which can lead to unnecessary "sort segment request" waits. Therefore, check in the first step whether the problem also occurs with the latest Oracle patch set. If this is the case, create an SAP message.

wait for stopper event to be increased

• Meaning: SMON rollback operations in the background
• Optimization steps:

The event usually occurs in connection with extensive rollback operations after a SHUTDOWN ABORT and STARTUP. This only affects the SMON process. If no other processes are affected, there is no need for further processing. This can, however, mean that subsequent problems occur such as "sort segment shrink" or there are SS enqueue waits, which then have to be optimized.

Also note the special case described in Note 963894.

wait for a undo record

• Meaning: A session waits for an undo record while a transaction recovery is running.
• Optimization steps:

This wait event should not comprise a significant part of the database time. If this is not the case, create an SAP customer message.

wait list latch free

• Meaning: Waiting for a latch
• Optimization steps:

The "wait list latch free" optimization runs at the same time as the "latch free" optimization. Therefore, refer to the information for the "latch free" wait event.

async disk IO
ksfd: async disk IO (Oracle 10 g or higher)

• Meaning: Waiting for asynchronous disk accesses
• Optimization steps:

"async disk IO" or "ksfd: async disk IO" may occur in different situations: For example, when creating tablespaces, creating or expanding data files, executing RMAN backups, archiving redo logs with ARCH processes or during AUTOEXTEND extensions (such as during INSERT operations). You can use V$SESSION_EVENT to determine which sessions are mainly responsible for these wait situations. For optimization options, see Note 793113.

PX Deq: Execute Reply
PX Deq: Table Q Normal
PX Deq Credit: send blkd
PX Deq: Execution Msg
PX qref latch
PX Deq: Signal ACK
PX Deq: Join ACK
PX Deq Credit: need buffer
PX Deq: Parse Reply
reliable message

• Meaning: Wait events in the parallel execution area
• Optimization steps:

For more information, see Note 651060.

local write wait