sapnote_0000619188(oracle wait events)

25.10.2011 of 36

SAP Note 619188 - FAQ: Oracle wait events

Note Language: English Version: 221 Validity: Valid Since13.09.2011

Summary

Symptom

1. What are wait events?

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

3. For what purpose are wait events used?

4. How can I access wait event information?

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

6. What are idle events?

7. What are the most important idle events?

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

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

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

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

12. How should I interpret the WAIT_TIME from V$SESSION_WAIT?

13. How can I trace the wait events of an Oracle session?

14. Where do I find historical Wait event information?

15. How can I optimize the individual wait events?

16. What else should I check in relation to wait events?

17. Where can I find further information about wait events?

Other termsFAQ, queue event, event, queue events, events

Reason and Prerequisites

Solution

1. What are wait events?

Wait events are characteristic parts of the Oracle kernel source codethat can contain Oracle sessions during execution. Oracle providesstatistical information on the wait events, which comprises the

25.10.2011 of 36


following components:

o Name of the wait event

o Optional: Up to three parameters of different importance dependingon the wait event.

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

The database response time is largely determined by the following twocomponents:

o Waiting in the context of wait events

o CPU consumption

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

For information on the distribution between wait event time and CPUtime, refer to the initial screen of transaction ST04 ("Busy waittime" and "CPU time"). Note, however, that ST04 may also include IdleWait Events in the Busy Wait Time, which is why it is best to checkthe 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 iscurrently doing or what it is waiting for. In the case of performanceproblems, you can therefore determine whether they are caused bydatabase locks, disk accesses, latches or another cause.

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

If sporadic performance bottlenecks occur, analyzing the wait eventsthat 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 followingis important in this context:

o V$SYSTEM_EVENT: Wait events accumulated system-wide since thedatabase was started.

o V$SESSION_EVENT: Wait events accumulated per session since thedatabase was started.

o V$SESSION_WAIT: Current wait events.

o V$EVENT_NAME: Name and parameter of the wait events

25.10.2011 of 36


In addition to a direct access at Oracle level, you can also usetransaction "ST04 -> Detail analysis menu -> Display V$ values" or theRSORAVDV (or /SDF/RSORAVDV) to access these views. This note primarilydescribes the direct Oracle queries because you can sometimes nolonger 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 canuse 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 relevantfor an event using:

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

6. What are idle events?

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

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

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

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

7. What are the most important idle events?

o Idle events as defined by SAP and Oracle:

ASM background timerclass slave waitDIAG idle waitdispatcher timerEMON idle waitgcs for actiongcs remote messageges remote messageHS message to agenti/o slave wait

25.10.2011 of 36


jobq slave waitJS external jobKSV master waitLNS ASYNC archive logLNS ASYNC dest activationLNS ASYNC end of loglock manager wait for remote messageLogMiner: client waiting for transactionLogMiner: slave waiting for activate messageLogMiner: wakeup event for builderLogMiner: wakeup event for preparerLogMiner: wakeup event for readerNull eventparallel query dequeueparallel recovery coordinator waits for cleanup of slavespipe getPL/SQL lock timerpmon timerPX Deq Credit: need bufferPX Deq Credit: send blkdPX Deq: Execute ReplyPX Deq: Execution MsgPX Deq: Index Merge ClosePX Deq: Index Merge ExecutePX Deq: Index Merge ReplyPX Deq: Join ACKPX Deq: kdcphc_ackPX Deq: kdcph_maiPX Deq: Msg FragmentPX Deq: Par Recov Change VectorPX Deq: Par Recov ExecutePX Deq: Par Recov ReplyPX Deq: Parse ReplyPX Deq: Signal ACKPX Deq: Table Q NormalPX Deq: Table Q SamplePX Deq: Txn Recovery ReplyPX Deq: Txn Recovery StartPX Deque waitPX Idle Waitqueue messagesrdbms ipc messageSGA: MMAN sleep for component shrinksingle-task messageslave waitsmon timerSQL*Net message from clientSQL*Net message from dblinkStreams AQ: deallocate messages from Streams PoolStreams AQ: delete acknowledged messagesStreams AQ: qmn coordinator idle waitStreams AQ: qmn slave idle waitStreams AQ: RAC qmn coordinator idle waitStreams AQ: waiting for messages in the queueStreams AQ: waiting for time management or cleanup tasksStreams fetch slave: waiting for txnsvirtual circuit statuswait for unread message on broadcast channel

25.10.2011 of 36


wait for unread message on multiple broadcast channelswakeup time managerwatchdog main loop

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

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

o Idle events as defined by SAP only:

db file parallel writeLog archive I/Olog file parallel writelog file sequential readARCH wait on SENDREQ

In general, all wait events for background processes are defined bySAP as idle events. The non-idle waits that are allotted tobackground processes (such as "db file sequential read") aretherefore also not relevant from an SAP perspective. As of Oracle10g, these wait events for the wait class "System I/O" are groupedtogether (WAIT_CLASS# = 9).

"log file sequential read" may represent an exception. It can alsobe executed by shadow processes that read information from the redologs in the case of errors in order to generate trace files thatcontain sufficient information.

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

As of Oracle 10g, wait events are assigned to specific wait classessuch as "User I/O" or "Concurrency". You can use the following commandto determine what proportion of the entire non-idle wait time is takenup 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) PERCENTFROM (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 sincethe database was started. You can use the following SQL command todetermine the longest wait event:

SELECT EVENT, TIME_WAITED, AVERAGE_WAITFROM V$SYSTEM_EVENTORDER BY TIME_WAITED DESC;

25.10.2011 of 36


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

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

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

SELECT EVENT, TOTAL_WAITS, TIME_WAITED, AVG_MS, ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENTFROM(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 workprocess?

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

You can determine the <opid> at Oracle level from the <rpid> asfollows:

SELECT SID FROM V$SESSION WHERE PROCESS = <rpid>;

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

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

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

25.10.2011 of 36


o 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 P3FROM V$SESSION S, V$SESSION_WAIT SW, V$SQLTEXT ST, AUDIT_ACTIONS AAWHERE 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.COMMANDORDER BY S.SID;

o 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) SQLTEXTFROM V$SESSION S, V$SQLSTATS SS, AUDIT_ACTIONS AAWHERE 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 <osid>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 P3FROM V$SESSION S, V$SESSION_WAIT SW, V$SQLTEXT ST, AUDIT_ACTIONS AAWHERE S.SID = 14 AND S.SID = SW.SID AND S.SQL_HASH_VALUE = ST.HASH_VALUE (+) AND

25.10.2011 of 36


AA.ACTION = S.COMMANDORDER BY ST.PIECE;

This gives you the complete SQL statement, which is split up intoseveral 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:

o 0: The wait event is currently active.

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

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

o -2: The wait event has already been terminated, but no timeinformation 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 anevaluation 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 todetermine what a particular Oracle Session is waiting for in aparticular period. This may be useful if an SAP transaction takes anunusually long time on the database, without there being any obviousexplanation for this in the SAP System. For a thorough description ofusing ORADEBUG, see Note 613872.

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

SELECT SUBSTR(EVENT, 1, 45) EVENT, TOTAL_WAITS, TIME_WAITEDFROM V$SESSION_EVENTWHERE SID = <sid>UNIONSELECT 'CPU', NULL, VALUEFROM V$SESSTATWHERE SID = <sid> AND STATISTIC# = 12ORDER BY 3 DESC;

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

14. Where do I find historical Wait event information?

25.10.2011 of 36


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

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

The bug described in Note 607415 can result in incorrect averagevalues in RSORAVSE. Implement the correction instructions or arelevant Support Package to eliminate the problem.

o As of Oracle 10g, you can use V$ACTIVE_SESSION_HISTORY to displayhistorical data of active sessions. Each second, the system checkswhether a session is currently active (that is, it is waiting for anon-idle wait event or is consuming CPU). If it is active, an entryis created in V$ACTIVE_SESSION_HISTORY. Use the following commandto obtain the last 20 activities of a session <sid>, including therespective SQL statement:

SET LINESIZE 120SELECT * 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 = <sid> 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 makessense to determine which objects are principally associated with acertain wait event or group of wait events. As of 10g, you can use thefollowing query on V$ACTIVE_SESSION_HISTORY to determine the topobjects as regards the wait event <event_pattern> (for example, "enq%"for enqueue waits):

SELECT * FROM(SELECT SUBSTR(ASH.EVENT, 1, 30) EVENT,

25.10.2011 of 36


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 '<event_pattern>' 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 oftime and is therefore not always representative. You can obtainsnapshots of earlier periods in DBA_HIST_ACTIVE_SESS_HISTORY. As ofOracle 10.2, you can also create detailed ASH reports in Oracle. SeeNote 853576 for further details.

15. How can I optimize the individual wait events?

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

o 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 inV$SYSTEM_EVENT should not exceed 15ms.

- Optimization steps:

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

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

25.10.2011 of 36


Optimize processing-intensive SQL statements using a largenumber of disk reads (Note 766349).

With suboptimal, average "db file sequential read" times, youcan use V$FILESTAT and - as of Oracle 10g - V$FILE_HISTOGRAM tolocalize hot spots in the disk area. In cooperation with yourhardware partner, you should also check whether the hardwareworks correctly and the setup is optimum. Also refer to Note793113 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 observedwere significantly lower (2 ms, for example). You shouldtherefore always compare the current average values with pastvalues (for example, using the /SDF/RSORAVSE report).

o db file parallel read

- Meaning: Waiting for blocks to be read in parallel from thedisk

- Parameter: File number/block numbers/requests


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

o db file scattered read

- Meaning: Waiting for several blocks to be read from the disk

- Parameter: File number/block number/number of groups


The wait event "db file scattered read" always occurs whereseveral physically sequential blocks are read from disk.Typical cases are Full Table Scans or Index Fast Full Scans. Inindividual cases, "db file scattered read" waits may occur whenprefetch operations (for example, table prefetch using a_TABLE_LOOKUP_PREFETCH_SIZE that is higher than 0) areperformed, when the Oracle buffer pool is "warmed" using Oracle10g or higher (_DB_CACHE_PRE_WARM = TRUE) or during otheraccesses (for example, index range scans). Therefore, checkwhich 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 goodindication. You should therefore specifically check all SQLstatements for Full Table Scans that are at the very top in theSQL cache in relation to disk reads.

If an increased number of "db file scattered read" waits occurdirectly after a database is started and critical activities

25.10.2011 of 36


are slowed down as a result, you can deactivate the underlyingcache prewarming by the Oracle parameter _DB_CACHE_PRE_WARM =FALSE.

With the following statement, you can determine the objects atOracle level, that currently have the most blocks due to fulltable 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, whichcan be used to determine SQL statements that use full tablescans or index fast full scans. The following statement returnsthe 20 SQL statements that are responsible for the most diskreads in the context of full table scans and index fast fullscans:

SELECT * FROM(SELECT SUBSTR(SA.SQL_TEXT, 1, 68) SQL_TEXT, SA.DISK_READS DISK_READSFROM 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 informationabout the number of Full Table Scans or Index Fast Full scansper 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.

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

25.10.2011 of 36


- Meaning: Waiting for blocks that are read directly from thedisk drive to avoid the Oracle buffer pool

- Parameter: Descriptor/DBA/number of blocks

- Rule of thumb: The total wait time for these wait events shouldnot exceed 5% of the relevant database time.


Direct path operations, with which the Oracle buffer pool isbypassed, are used when PSAPTEMP is accessed (sortings, hashjoins, bitmap operations), in the case of parallel query andwhen LOB data that is not cached is accessed. As of Oracle 11g,full table scans for larger tables are read via direct path.

Depending on your Oracle release, these activities are assignedto the wait events as follows:

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

Parallel Execution: direct path read

Full table scans on large tables: direct path read (>= 11g)

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

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

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

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

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

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

"direct path read temp" waits for INSERTs in J2EE environmentsare triggered by temporary LOBs (Note 500340).

o 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 avoidthe Oracle buffer pool

- Parameter: Descriptor/DBA/number of blocks

25.10.2011 of 36


- Rule of thumb: This should not appear under the Top 10 waitevents in terms of the cumulative queue time


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

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

Parallel query: direct path write

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

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

o Log file sync

- Meaning: Waiting until the LGWR has written all data from theredo buffer to the online redo log (for example, as part of acommit, if the DBWR triggers the LGWR because a dirty blockthat is to be written to the hard disk has not yet been enteredin the redo logs, or if LGWR is triggered in another way towrite the redo buffer to the online redo log).

- Parameter: Buffer number / - / -

- Rule of thumb: The average value for the wait time displayed inV$SYSTEM_EVENT should not exceed 15ms.


In most cases the increased "log file sync" times are relatedto I/O problems for the LGWR process. Therefore you must checkthe operating system and the hardware you are using forbottlenecks during write-access to the Online Redo log. Sinceincreased access times are generally caused by a bottleneck inthe operating system or hardware / input/output area, pleaseconsult your operating system and hardware partner for a morespecific analysis. Also check whether the current Oracleconfiguration can be optimized regarding the I/O configurationaccording to Note 793113.

"log file sync" waits may also occur when the system accessesunbuffered number ranges because the system performs a COMMITfor each of the numbers involved. In this situation, checkwhether buffering is possible for the affected number ranges.If the problem occurs in the BI environment with DIMD and SIDnumber ranges, see Note 857998.

If "log file sync" waits occur in BW systems when you changelarge data volumes (for example, loading or compressing data),check whether indexes can be temporarily dropped or used with

25.10.2011 of 36


NOLOGGING (see Note 853084).

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

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

As of Oracle 10g, Oracle gives you the option of controllingthe performance of "log file sync" using the parameterCOMMIT_WRITE. Due to the fact that the consistency of theapplication can no longer be guaranteed if settings arechanged, changing this parameter in a way that deviates fromthe standard system is generally not allowed in the SAPenvironment.

The operation of a dataguard standby database in the "MAXIMUMAVAILABILITY" mode or "MAXIMUM PROTECTION" mode may alsocontribute to increased "log file sync" times because the redodata during the COMMIT is propagated synchronously with thestandby database in this case. In this case, the LGWR processwaits for events such as "LGWR-LNS wait on channel" or "LGWRwait on LNS". In this situation, optimize the dataguardconfiguration and use the "MAXIMUM PERFORMANCE" mode.

In individual cases, increased "log file sync" times can alsobe caused by other effects. This means that an ARCH process canhold a control file enqueue over a long period of time (seeNote 745639), which means that the LGWR process, which alsorequires this enqueue cannot carry out a log switch. As aresult, all DML operations must wait for "log file sync" untilthe ARCH process has released the enqueue. Situations have alsobeen observed in which the DBWR processes had to wait forlatches due to an Oracle bug and as a result, the LGWRprocesses could not continue processing ("LGWR wait for redocopy" waits). This also led to massive "log file sync" waits.

o log buffer space

- Meaning: Waiting for freespace in the redo buffer


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

o log file switch

- Meaning: Waiting for a redo log switch


25.10.2011 of 36


In the case of "log file switch (archiving needed)", check inaccordance with Note 391 whether an Archiver Stuck hasoccurred.

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

In the case of "log file switch (private strand flushincomplete)", refer to Note 793113 and optimize the DBWRperformance.

In the case of "log file switch completion", you have to waitfor a log switch to end. Provide a sufficient redo log size toensure that there are not too many log switches (more than oneper minute) for optimizing this wait event. Note also theoptimization possibilities in the "log file sync" sectionbecause during a log switch, the system also flushes the redobuffer. Increased "log file switch completion"-times may alsobe a consequence of "checkpoint not complete" problems.Therefore, check if "log file switch (checkpointincomplete)"-waits occur, and if so, proceed as described inNote 79341.

o log file parallel write

- Meaning: LGWR waiting for blocks to be written to disk

- Parameter: File/block/ I/O requests


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

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

- Meaning: Waiting for a block because it is currently beingimported or changed by another session.

- Parameter: File number/Block number/ ID (<= 9i)

- Rule of thumb: The average wait time of "read by other session"should be below 10 ms. "buffer busy waits" should be less than1 % of the database time.


If the rule of thumb is exceeded for "read by other session",this is generally due to problems in the I/O area. You shouldtherefore also carry out the same checks in this case asdescribed in the section "DB file sequential read".

The ID specified as a third parameter gives more exactinformation as to why the block cannot be accessed. It isgenerally the case that: if the first number is a 1, just theblock is read. If the first number is a 2, the block is kept in

25.10.2011 of 36


an incompatible mode. As of Oracle 10g, the third parameter forthe class of the affected block is available:

1 Data block2 Sort block3 Save undo block4 Segment header5 Save undo header6 Free List7 Extent map8 1st level bitmap block9 2nd level bitmap block10 3rd level bitmap block11 Bitmap block12 Bitmap index block13 File header block14 Unused15 System undo block16 System undo block17 Undo header18 Undo block

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

If the waits occur frequently on certain blocks, you can usethe following SELECT to determine the corresponding segment(<file#> and <block#> are parameters 1 and 2 of the waitevent). In addition, the HDR column contains information as towhether it is a header block or not:

SELECT SUBSTR(SEGMENT_NAME, 1, 30), SEGMENT_TYPE, DECODE(<block#> - BLOCK_ID + EXTENT_ID, 0, 'YES', 'NO') HDRFROM DBA_EXTENTSWHERE FILE_ID = <file#> AND <block#> BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

You can use the following SELECT to determine the data files inwhich 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 asto how many Busy Wait buffers occurred for every segment. Inthe following query, for example, you can determine the 20segments 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;

25.10.2011 of 36


The Oracle view V$WAITSTAT contains an overview of how oftenBuffer Busy Waits occurred in the individual block types sincethe database was started as well as the average length of thewaits. Depending on the type of the block, take the followingmeasures:

Data block: If INSERTs run onto the "buffer busy waits", thenumber of the FREELISTs can be increased for the affectedsegment. This change can be carried out dynamically - it is notnecessary to reorganize the object. The FREELISTs are assignedto the Oracle processors according to the algorithm MOD(PID,#FREELISTs) + 1 (where PID denotes the Oracle-PID fromV$PROCESS).

If this change does not result in a successful outcome, you canincrease the number of FREELIST GROUPs. It is useful to use adifferent prime number for the number of FREELISTS and FREELISTGROUPs because the calculation of the used FREELIST GROUP in anon-RAC environment occurs according to the same algorithm asthe FREELIST calculation.

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

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

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

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

"Buffer busy" waits that have a significantly high average waittime may be the consequence of a simultaneous "archiver stuck".In this case, the "buffer busy" waits are only a consequence ofthe 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 Oracle10g to check whether "buffer busy waits" usually occur at thesame time as "log buffer space" waits.

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

"buffer busy waits" may also occur as a result of "free bufferwaits". You must therefore check whether "free buffer waits"also occur at the same time as "buffer busy waits".

25.10.2011 of 36


o write complete waits.

- Meaning: Waiting until the DBWR has written a necessary blockto the disk

- Parameter: File number/Block number/ID

- Rule of thumb: Should not be in the top 10 of V$SYSTEM_EVENT


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

o free buffer waits

- Meaning: Waiting for the DBWR to write dirty blocks to disk sothat 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


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

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

o Oracle 9i or lower: latch freeOracle 10g or higher: latch: <latch_name> / latch free

- Meaning: Waiting for a latch to be released; a latch is a verybasic serialization mechanism that can be used to prevent datastructures in the SGA being accessed simultaneously.

- Parameter: latch address/latch number/number of sleeps


See Note 767414.

o Oracle 9i or lower: enqueueOracle 10g or higher: enq: <typ> - <description>

- Meaning: waiting for an Oracle-Lock

25.10.2011 of 36


- Parameter: Type/ID1/ID2


See Note 745639.

o library cache locklibrary cache load locklibrary cache pin

- Meaning: Waiting for exclusive access to data of the librarycache


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

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

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

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

If the problem occurs with Oracle 9.2.0.6, refer to Note846364.

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

If a deadlock of TX enqueues or library cache locks occurs withrow 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 alsobe triggered in connection with ALTER INDEX ... COALESCEbecause unlike in the standard system behavior, this operationrequests a "row cache lock" first and then a "library cachelock". This incorrect behavior is addressed in Oracle bug6051177.

"library cache lock" waits can appear for any access if aVALIDATE STRUCTURE CASCADE ONLINE, an INDEX COALESCE or anEXPLAIN and an ALTER, ANALYZE STATISTICS or DBMS_STATS commandis executed on the same segment (or on a table and relevantindex). In this case, one of the two sessions must be cancelledto allow the table to be accessed again.

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

25.10.2011 of 36


SELECT DISTINCT LOCK_A.KGLLKSNMFROM X$KGLLK LOCK_A, X$KGLLK LOCK_BWHERE 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 entryin V$SESSION to determine the session that is blocking anothersession <sid> using a library cache lock (or another lock). Seealso the corresponding SELECT in Note 20071.

"Library cache lock" waits that last a long time under Oracle9.2.0.8 or 10.2.0.2 may also be a consequence of the bugdescribed in Note 971261.

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

If these wait situations occur in relation to a FOR ALL ENTRIESaccess that is executed in parallel frequently, you mayconsider to increase the blocking factors (Note 881083).

If a session waits for more than five minutes for "library cacelock" or "library cache pin", the session is terminated withthe error ORA-04021. If a deadlock occurs, the system returnserror ORA-04020.

Wait situations on "library cache lock" can also be follow-onproblems of an archiver stuck ("log file switch (archivingneeded)").

o row cache lock

- Meaning: Waiting for exclusive row cache access

- Parameter: Cache ID/lock mode/request


High "row cache lock" wait times are usually triggered byOracle bugs. Check whether you have one of the bugs describedin Notes 738641 and 768308.

If a deadlock of TX enqueues or library cache locks occurs withrow 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 downsignificantly because the management structures are gettingbigger and bigger in the shared pool. In such a case, you canperform a flush of the shared pool as a workaround:

25.10.2011 of 36


ALTER SYSTEM FLUSH SHARED_POOL;

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

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

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

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

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

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

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

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

If "row cache lock" waits occur on partitions on Oracle10.2.0.2 in connection with SHRINK SPACE COMPACT, see Note1121838.

Deadlocks of "row cache lock" and "library cache lock" may alsobe triggered in connection with ALTER INDEX ... COALESCEbecause unlike in the standard system behavior, this operationrequests a "row cache lock" first and then a "library cachelock". This incorrect behavior is addressed in Oracle bug6051177.

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

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

o Null event

- Meaning: Unspecific wait event

- Parameter: -

25.10.2011 of 36



"Null event" is usually an idle event. There is thereforenormally no handling requirement. However, in exceptional casesit is possible that a session may hang for a longer period oftime 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 Note545932.

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

o db file parallel write

- Meaning: waiting of the DBWR, until blocks have been written todisk


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

Depending on your Oracle version and operating system, the thelogged 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 ofwritten requests (P1). If there is a greater number of writtenrequests, runtimes in the second area are acceptable.

o DB file single write

- Meaning: Writing individual blocks to the hard disk

- Parameter: File number/Block number/1


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

o rdbms ipc reply

- Meaning: shadow process waiting for a background process

25.10.2011 of 36


- Parameter: PID of the background process/timeout in seconds/-


If a session hangs for a prolonged period on this wait event,the PID of the background process can be used to determine therelevant 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 mustexecute a checkpoint. Furthermore, there have to be "rdbms ipcreply" waits in the context of a buffer pool flush. There arealso "rdbms ipc reply" waits on the DBWR in the context ofRESIZE operations.

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

With Oracle 9i or lower, the design flaws described in Note758989 result in increased "rdbms ipc reply" times in thesequence of checkpoints for BEGIN BACKUP, DROP and TRUNCATE.Oracle 10g solves this problem. You can alleviate the problemby reducing the buffer pool, but often you do not want thisbecause of a deterioration in performance elsewhere. Forinformation on optimizing BEGIN BACKUP runtimes, see also Note875477.

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

The duration of "rdbms ipc reply" waits may also be associatedwith a poor I/O performance. In this context, refer to Note793113 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 sessionsmust wait for the CI enqueue.

o refresh controlfile command

- Meaning: Refreshing the controlfile size information


This wait event is generally an uncritical event. It isinvolved with control file information in conjunction withaccesses to X$ views (and the V$ views based on this) andshould not last any longer than a few milliseconds.Significantly longer wait times have thus far only been

25.10.2011 of 36


observed on AIX if the Oracle kernel extension (pw-syscall) wasloaded in various different versions in parallel or if a rebootwas forgotten after the extension was imported. See Note328822.

o SQL*Net message to clientSQL*Net message to dblinkSQL*Net more data to clientSQL*Net more data to dblinkSQL*Net message from clientSQL*Net more data from clientSQL*Net message from dblinkSQL*Net more data from dblink

- Meaning: Communication between the shadow process and theclient

- Parameter: Driver ID/Bytes/-


Wait events such as "SQL*Net message from client" or "SQL*Netmessage from dblink" are categorized as idle events, but (justlike the other SQL*Net wait events) they may be indicative ofcommunication 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 mainidle event for Oracle shadow processes.

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

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

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

nttcon: got tcp.nodelay = 1

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

ntvllt: No network parameter file found

is logged, there are problems when you access protocol.ora orsqlnet.ora. As well as using .protocol.ora as described in Note198752, instead of protocol.ora, the problem can also be causedby incorrectly installed client libraries. Refer to Note 180430

25.10.2011 of 36


and the sub-notes referred to there for a description of theinstallation of the Oracle client software.

Small SDU sizes are another reason for "SQL*Net more data toclient" and "SQL*Net more data from client" ("Session DataUnit") occurring frequently, whereby a larger dataset istransferred through the network in several small packages. TheSDU values in tnsnames.ora and listener.ora can be increased inthis case (Note 562403).

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

o index block split

- Meaning: Waiting for the split of an index block as part of anINSERT.

- Parameter: Root DBA/level/child DBA

- Rule of thumb: This wait event should never appear under thetop 10 wait events. A process must never wait longer than afraction of a second for "index block split".


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

o SQL*Net break/reset to clientSQL*Net break/reset to client

- Meaning: The execution of a statement is prematurely terminated


If these wait events occur increasingly, you must firstdetermine the cause of the terminations. In the SAPenvironment, the most frequent cause is INSERTs that encounteran ORA-00001 because a data record with the same unique keyalready exists in the table. However, SELECTs from a closedcursor or a non-existing table may also lead to this type ofwait event. The same applies for SQL statements that containsyntax errors and for cursor fetches for which the last datarecord was already previously delivered.

If this problem occurs in connection with duplicate keys forINSERTs, in the application you must check whether you canreduce or prevent these INSERTs.

o io done

25.10.2011 of 36


- Meaning: Waiting for a synchronous write I/O to finish


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

o imm op

- Meaning: Waiting for an IMMEDIATE I/O request to a slaveprocess to end.


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_SLAVESare used. Generally, the "imm op" event does not pose aproblem.

If DBWR_IO_SLAVES are set to 1 or higher, we recommend that youcheck 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 RMANbackup if dedicated I/O slave processes are to copy backupwrite processes to tape and not the Oracle shadow processes. Inthis case, "imm op" waits only affect the backup runtime, butnot the live system.

o index (re)build online startindex (re)build online cleanupindex (re)build online merge

- Meaning: Wait situations during an ALTER INDEX REBUILD ONLINE

- Parameter: Object ID / - / -


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

o ksu process alloc latch yield


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

o checkpoint completed

- Meaning: Waits for a checkpoint to be completed

25.10.2011 of 36



The "checkpoint completed" Wait event occurs if a sessionexplicitly has to wait for a checkpoint to finish in certainsituations (for example, when stopping or sometimes evenstarting up the database). To optimize this, proceed asdescribed in the process for the wait events "checkpoint notcomplete".

o 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


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

o Wait for shrink lock2 (Oracle 10g or higher)

- Meaning: A shrink is waiting for an enqueue to be released.


"Wait for shrink lock2" occurs in a shrink (Note 910389) if aTM enqueue (Note 766349) on the segment is already being heldand requested by other sessions at the same time. For thisreason, avoid carrying out other activities on the table inquestion during the shrink.

o sort segment request

- Meaning: Waiting for the allocation of a sort segment


This wait event occurs when the SMON process is very busy, and

25.10.2011 of 36


is no longer able to quickly process the incoming requests.This might be the case, for example, if large rollbackactivities are carried out.

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

Older Oracle releases sometimes contain errors which can leadto unnecessary "sort segment request" waits. Therefore, checkin the first step whether the problem also occurs with thelatest Oracle patch set. If this is the case, create an SAPmessage.

o wait for stopper event to be increased

- Meaning: SMON rollback operations in the background


The event usually occurs in connection with extensive rollbackoperations after a transaction recovery, or SHUTDOWN ABORT andSTARTUP. This only affects the SMON process. If no otherprocesses are affected, there is no need for furtherprocessing. This can, however, mean that subsequent problemsoccur such as "sort segment shrink" or there are SS enqueuewaits, which then have to be optimized.

Also note the special case described in Note 963894.

o wait for a undo record

- Meaning: A PX session waits for an undo record while atransaction recovery is running.


If several parallel execution slaves want to access the sameundo record as part of a parallelized transaction recovery, thewait event "wait for a undo record" may occur. If this causesdelays in the transaction recovery or has an effect on therunning system, you can deactivate it as follows:

FAST_START_PARALLEL_ROLLBACK = FALSE

o wait list latch free

- Meaning: Waiting for a latch


The "wait list latch free" optimization runs at the same timeas the "latch free" optimization. Therefore, refer to theinformation for the "latch free" wait event.

o async disk IOksfd: async disk IO (Oracle 10 g or higher)

25.10.2011 of 36


- Meaning: Waiting for asynchronous disk accesses


"async disk IO" or "ksfd: async disk IO" may occur in differentsituations: For example, when creating tablespaces, creating orexpanding data files, executing RMAN backups, archiving redologs with ARCH processes or during AUTOEXTEND extensions (suchas during INSERT operations). You can use V$SESSION_EVENT todetermine which sessions are mainly responsible for these waitsituations. For optimization options, see Note 793113.

o PX Deq: Execute ReplyPX Deq: Table Q NormalPX Deq Credit: send blkdPX Deq: Execution MsgPX qref latchPX Deq: Signal ACKPX Deq: Join ACKPX Deq Credit: need bufferPX Deq: Parse Replyreliable message

- Meaning: Wait events in the parallel execution area


See Note 651060.

o local write wait

- Meaning: Waiting for a write operation to the hard disk


"local write wait" mainly occur in connection with TRUNCATEoperations. You can tune this wait event to optimize the I/Obehavior. For more information, see Note 793113.

o control file single write

- Meaning: Write access to control file


"control file single write" waits appear with operations thataccess the control file (for example, BACKUP CONTROLFILE, ENDBACKUP). Generally, these waits do not cause any performanceproblems.

o Wait for Table Lock

- Meaning: Wait for exclusive TM enqueue in materialized viewoperations.


25.10.2011 of 36


In the SAP environment, materialized views (Note 741478) areonly implicitly used as standard when you carry out an onlinereorganization. Therefore, the "Wait for Table Lock" wait eventgenerally only occurs in connection with an onlinereorganization. If you reorganize a table online and executecommands such as DBMS_REDEFINITION.START_REDEF_TABLE orDBMS_REDEFINITION.FINISH_REDEF_TABLE, and if another sessionsimultaneously holds an exclusive TM enqueue (Note 745639) onthe same table, the reorg session waits for "Wait for TableLock".

Therefore, this wait event only affects the runtime ofreorganizations, and does not affect the production operation.You can reduce the number of times "Wait for Table Lock" occursby not carrying out any other activities that set an exclusiveTM enqueue at the same time as the online reorganization (alsosee Note 745639).

o writes stopped by instance recovery or database suspension

- Meaning: This is the wait situation of processes when you stopthe database using ALTER SYSTEM SUSPEND (for example, during asplit mirror backup).


Check if it is possible to reduce the frequency with whichALTER SYSTEM SUSPEND is executed, or if you can reduce the timebetween SUSPEND and RESUME. Also check whether you can executethe SUSPEND at a timem of lower system load.

This wait event does not only prevent blocks from being writtento the disk - it also stops the system from executing DMLoperations such as INSERT or UPDATE.

o cursor mutex Xcursor mutex Scursor pin S wait on Xcursor pin Xcursor pin Slibrary cache: mutex Xlibrary cache: mutex S

- Meaning: Wait situations involving mutexes (Oracle 10g andhigher)


See Note 964344.

o Data file init write (Oracle 10g or higher)

- Meaning: Wait for the initialization of datafile blocks


This wait situation may occur during productive operation when

25.10.2011 of 36


AUTOEXTEND operations are carried out. To decrease the numberof AUTOEXTEND operations and the appearance of these waitevents, you can create larger datafiles from the outset.

o Datapump dump file I/O (Oracle 10g or higher)kupp process wait (Oracle 10g or higher)

- Meaning: These Wait events are connected to Data Pump.


See Note 1013049.

o Streams AQ: qmn coordinator waiting for slave to start

- Meaning: Waiting for the QMNC process to start an advancedqueuing slave process.


In the SAP environment, streams can only be used in connectionwith Data Pump. See Note 1013049.

o Statement suspended, wait error to be cleared

- Meaning: Wait for the clean-up of a space error in an activatedRESUMABLE.


This wait event can only occur if the RESUMABLE option wasindividually activated for sessions, and this is never the casein the SAP standard system. The wait event occurs when a spaceerror occurs in the RESUMABLE session, and it can be removed bycleaning up the storage problem or by terminating the waitingsession.

o resmgr:become active (Oracle 10g and higher)

- Meaning: Preventing database connections due to an activeQUIESCE session


Generally, this wait situation occurs when you execute certainEMCA operations such as the operation for creating the EMrepository. As a result of these operations, the systemsimplicity switches to QUIESCE mode. Therefore, all databaseconnections (except users SYS and SYSTEM) must wait for"resmgr:become active". In this case, refer to Note 1044758 andexecute the following command if necessary:

ALTER SYSTEM UNQUIESCE;

o resmgr:CPU quantumresmgr:resource group CPU method

- Meaning: Waiting for limitations of the Oracle Resource Manager

25.10.2011 of 36



Make sure that the Oracle Resource Manager is configuredcorrectly (Note 1589924) or that it is deactivated completely.To deactivate it, the parameter RESOURCE_MANAGER_PLAN must beblank and you must refer to Note 1579946 for maintenancewindows.

o SGA: allocation forcing component growth

- Meaning: The session waits until the system is finished using"ALTER SYSTEM SET" to adjust the size of the dynamic SGAcomponents.


Only the session that is currently being adjusted has to waitfor this wait event. Other sessions can continue to work asnormal. Therefore, there is no tuning requirement.

o kdic_do_merge

- Meaning: INDEX REBUILD is waiting for a library cache lock tobe released.


To avoid critical deadlocks between INDEX REBUILDs and otherDDL operations, we introduced the wait event "kdic_do_merge"that takes about two seconds in the Oracle bug 3424721. Thesystem uses this wait event if an offline INDEX REBUILD mustwait for a library cache lock that is being held by another DDLoperation. This can be solved by avoiding DDL activities thatexist in parallel to INDEX REBUILD.

o control file sequential read

- Meaning: A block from the control file is read


These waits occur, among other things, if control file-relatedV$ views are accessed. Generally, the number of accesses shouldbe manageable and non-critical. However, unfavorable accessplans for V$ views may result in an increased number ofaccesses to control files. For example, the problem describedin Note 1011731 when accessing V$RMAN_STATUS may result in theobject X$KCCRSR (which is references as part of this view)being used an internal table for extensive nested loop joins,which requires a lot of individual control file accesses.

If these problems occur, check whether a bug may beresponsible. Oracle 10g and higher should also ensure that DDICand fixed object statistics exist and are up-to-date inaccordance with Note 838725.

o opishd

25.10.2011 of 36


- Meaning: Waiting for successful shutdown


If a session performs a shutdown and has to wait for activeprocesses, it results in "opishd" waits. After an hour, theshutdown is terminates ("SHUTDOWN: Active sessions preventdatabase close operation").

In such cases, check which processes were responsible for thelong shutdown (also see Note 521264) and remove the problem.

o flashback buf free by RVWR

- Meaning: Wait on writing in Fast Recovery Area


Read Note 966117, make sure that the Fast Recovery Area is onthe fastest disks possible, and if possible, minimize the I/Oin the Fast Recovery Area (for example, by using Restore Pointsinstead of the general Flashback Database).

16. What else should I check in relation to wait events?

If you see a number of individual wait events that is inexplicablyhigh, this may be due to a CPU bottleneck on the database server. Itis therefore conceivable that an Oracle process that has a lock may bedisplaced by other processes from the CPU. As a result, the period ofthe lock stopping greatly increases and other processes serialize onthis lock. You must therefore use transaction ST06/OS07 to checkwhether there are sufficient CPU resources. SAP recommends an idleproportion of at least 30% per hour.

Significantly increased average values may occur frequently forindividual wait events, due to measurement errors. To prevent thevalues displayed resulting from individual incorrect statisticalvalues, you can carry out a reset in many R/3 screens. The valuescollected afterward are generally correct since it is unlikely that anincorrect statistical value will appear after a reset.

17. Where can I find further information on wait events?

The Oracle 9i online documentation contains information on wait eventsin the following books:

Performance Tuning Guide and Reference-> 22 Instance Tuning-> Wait Events

Reference-> A Oracle Wait Events

Header Data

25.10.2011 of 36


Release Status: Released for CustomerReleased on: 13.09.2011 18:51:26Master Language: GermanPriority: Recommendations/additional infoCategory: FAQPrimary Component: BC-DB-ORA Oracle

Secondary Components:SV-BO Backoffice Service Delivery

The Note is release-independent

Related Notes

Number Short Text

1611285 FRP sequence 1: Performance improvement for DIF access

1176846 DBA Cockpit: Adjusting the Oracle idle events

1121838 SELECT on partition hangs if a shrink compact is running

1044758 EM repository operations with EMCA and Oracle quiesce

1017970 DBMS_ADVISOR may result in deadlock

1013049 FAQ: Oracle Data Pump

971261 Oracle 9.2 / 10.2: Hanging queries with STAR_TRANSFORMATION

964344 FAQ: Oracle mutexes

963894 SMON blocks processes in rollback of large transactions

904188 Locks when you execute Index Rebuilds Online in parallel

894078 9.2.0.7: Long waits for KSU PROCESS ALLOC LATCH YIELD

875477 Avoiding long runtimes with BEGIN BACKUP

869006 Composite SAP note: ORA-04031

857998 Number range buffering for DIM IDs and SIDs

857973 Deleting clients efficiently using Oracle

853576 Oracle 10g: Performance analysis w/ ASH and Oracle Advisors

853084 Transferring temporary /BI0/06 tables to another tablespace

846364 Parse of star query may spin

842240 FAQ: Backup strategy of large and highly-available databases

840553 BW Oracle: TRUNCATE table slower than DELETE

830576 Parameter recommendations for Oracle 10g

825653 Oracle: Common misconceptions

806554 FAQ: I/O-intensive database operations

805934 FAQ: Database time

793113 FAQ: Oracle I/O configuration

789011 FAQ: Oracle memory areas

768308 Deadlock with row cache locks and library cache locks

25.10.2011 of 36


Number Short Text

767414 FAQ: Oracle latches

766827 Composite SAP note: Performance SCM 4.0

766349 FAQ: Oracle SQL optimization

758989 Poor performance with TRUNCATEs

745639 FAQ: Oracle enqueues

738641 Change run/rollup stops with simultaneous COALESCE

712624 High CPU consumption by Oracle

659946 FAQ: Temporary tablespaces

651060 FAQ: Oracle Parallel Execution

649876 Library Cache Pin Contention after Upg. to 9.2.0.2 - 9.2.0.4

626172 Performance problems with Outer Join queries in Oracle 9.2

620803 Oracle 9i: Automatic Segment Space Management

618868 FAQ: Oracle performance

613872 Oracle traces with ORADEBUG

610357 Oracle hang with event DB_FILE_SCATTERED_READ

607415 RSORAVSE displays poor averages

596748 Simultaneous generation of selection screens in background

596420 System standstill during deadlock (ORA-60)

582427 Error due to inconsistent Oracle DDIC

564446 Scheduling of background job BTCH_RSORAVSH

563359 Performance optimization for tables with LOB columns

562403 FAQ: Oracle Net

556764 Upgrade hangs in phase ACT_<REL>

549298 RSORAVSH: LOAD_PROGRAM_NOT FOUND during the run of RSCOLL00

541538 FAQ: Reorganization

521230 FAQ: Client software 9i or lower on UNIX

500235 Network Diagnosis with NIPING

488583 Database hangs: Waits for cache buffer chain latch

449136 "cache buffers chains" latch contention on 8.1

400698 ORA-4031 Memory leak on Oracle 8.1.7

376905 Long parsing times and Library Cache Locks

359835 Design of the temporary tablespace in the BW System

328822 AIX kernel extension for Oracle

198752 TCP delay problem under Oracle 8.1.x

180430 Installing the ORACLE client software for UNIX

164925 Storage parameter of tablespace PSAPTEMP

79341 Checkpoint not complete

72638 Performance problems with SQL*Net

20071 Permanent lock after connection termination

391 Archiver stuck

sapnote_0000619188(oracle wait events)

Documents

individual wait events

wait eventsthat

wait event time

busy wait time

wait evento optional

queue events

idlewait events

important idle events