Wait Events

db file scattered read – oracle wait event

DB File scattered read wait event occurs when an oracle process waits for a physical read of multiple oracle blocksfrom the disk. This wait identifies that a full scan (Full Table Scan or Index fast full scan) is occurring BUT during this read the blocks are read into memory locations that are not physically adjacent to each other, hence the term scattered read.

In simple words, when a SQL Statement causes a full scan, oracle’s shadow process reads up to DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache. Since a large no. of blocks have to be read into the buffer cache, server process has to search for a large no. of free/usable blocks in buffer cache which leads to wait included in db file scattered read wait.

db file sequential reads – Oracle wait event

DB file sequential reads wait event comes under the User I/O wait class. In any busy system this is one of the most common wait event and normally appears in the top 5 wait events. In this post we’ll understand whats happening during this wait event.

In simple words, Db file sequential reads wait event occurs when a process has issued an I/O request to read one block (single block I/O) from a datafile (Or datafile headers) into the buffer cache and is waiting for the operation to complete. These single block I/Os are usually a result of using indexes Or table data blocks accessed through an index.

So, when you issue any SQL Statement that performs a single block read operation against indexes, undo segments, tables (only when accessed by rowid), control files and data file headers, oracle server process waits for the OS to provide that block from the data file, and the wait event on which server process waits till the block is made available is termed as db file sequential read.

This is a common phenomenon BUT if you see this wait event is a significant portion of the total wait time then there are following things that we can do:-

• Identify Top SQLs responsible for Physical Reads (SQL Ordered by Reads section in AWR) and tune them to use efficient explain plan:-
  • If Index Range scans are involved, more blocks than necessary could be being visited if the index is unselective. By forcing or enabling the use of a more selective index, we can access the same table data by visiting fewer index blocks (and doing fewer physical I/Os).
  • If indexes are fragmented, again we have to visit more blocks because there is less index data per block.
    In this case, rebuilding the index will compact its contents into fewer blocks. Indexes can be (online) rebuilt, shrunk, or coalesced.
  • If the index being used has a large Clustering Factor, then more table data blocks have to be visited in order to get the rows in each index block: by rebuilding the table with its rows sorted by the particular index columns we can reduce the Clustering Factor and hence the number of table data blocks that we have to visit for each index block.
  • Use Partitioning to reduce the number of index and table data blocks to be visited for each SQL statement by usage of Partition Pruning.
• Identify HOT Tablespace/Files which are are servicing most of the I/O  requests.
  • I/Os on particular datafiles may be being serviced slower due to excessive activity on their disks. In this case, find such hot disks and spread out the I/O by manually moving datafiles to other storage or by making use of Striping, RAID and other technologies to automatically perform I/O load balancing for us.
  • Analyze V$SEGMENT_STATISTICS and see if indexes should be rebuilt or Partitioning could be used to reduce I/O on them.
• Use Buffer Cache Advisory and Increase the Buffer Cache if there is a potential benefit.

Direct Path Read/Direct Path Read Temp – Oracle Wait Event

When writing posts on performance tuning I felt that there needs to be a my-version of description of Oracle Wait Events along with Access method and Process & Memory Architecture. Thus this series is to start the Oracle Wait events which are common in any Database Environment BUT has a lot of story.

Direct Path Read/Direct Path Read Temp – wait event

When a session reads buffers from disk directly into the PGA (opposed to SGA’s buffer cache), the wait is on this wait event.

If the I/O subsystem doesn’t support asynchronous I/Os, then each wait corresponds to a physical read request.

If the I/O subsystem supports asynchronous I/O, then the process overlaps read requests with processing the blocks that are already in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not always the same as the number of read requests.

Causes for the direct path read/direct path read temp – wait event

• High disk sorts – If the sorts are too large to fit in memory and get sent to disk, this wait can occur.
• Parallel slaves – Parallel slaves are used for scanning data or parallel DML may be used to create and populate objects. These may lead to direct path read wait and direct path write wait respectively.
• Direct path loads – The direct path API is used to pass data to the load engine in the server and can cause the related direct path write wait.
• Server process ahead of I/O – The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.
• Data Warehouse – Sorts in a data warehouse environment may always go to disk leading to high waits on direct path read temp and/or direct path write temp.

Library Cache lock – Oracle Wait Event

The library cache lock controls the concurrency between clients of the library cache by acquiring a lock on the object handle so that either:

• One client can prevent other clients from accessing the same object
• The client can maintain a dependency for a long time (no other client can change the object).

This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).

There can be several causes for library cache lock, some of the common ones are discussed below along with the quick solutions:-

• Cause #1: Unshared SQL Due to Literals
  • Solution: Rewrite the SQL to use bind values
  • Solution: Use the CURSOR_SHARING initialization parameter
• Cause #2: Shared SQL being aged out
  • Solution: Increase the size of the shared pool
  • Solution: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size
  • Solution: Keep (“pin”) frequently used large PL/SQL and cursor objects in the shared pool
• Cause #3: Library cache object Invalidations
  • Solution: Do not perform DDL operations during busy periods
  • Solution: Do not collect optimizer statistics during busy periods
  • Solution: Do not perform TRUNCATE operations during busy periods
• Cause #4: Objects being compiled across sessions
  • Solution: Avoid compiling objects in different sessions at the same time or during busy times
• Cause #5: Auditing is turned on
  • Solution: Evaluate the need to audit
• Cause #6: Unshared SQL in a RAC environment
  • Solution: Rewrite the SQL to use bind values
  • Solution: Use the CURSOR_SHARING initialization parameter
• Cause #7: Extensive use of row level triggers
  • Solution: Evaluate the need for the row trigger
• Cause #8: Excessive Amount of Child Cursors
• Solution: Inappropriate use of parameter CURSOR_SHARING

Library Cache Pin – Oracle Wait Event

Library cache pins are used to manage library cache concurrency. Pinning an object causes the heaps to be loaded into memory (if not already loaded). PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode.

The following SQL can be used to show the sessions which are holding and/or requesting pins on the object that given in P1 (Column P1 of v$session_wait) in the wait:

1 2 3 4 5

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"   FROM x$kglpn p, v$session s  WHERE p.kglpnuse=s.saddr    AND kglpnhdl='&P1RAW' ;

Note that an X request (3) will be blocked by any pins held S mode (2) on the object and an S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

log file switch – Oracle Wait Event

log file switch (archiving needed)

Waiting for a log switch because the log that the LGWR will be switching into has not been archived yet. Check the alert file to make sure that archiving has not stopped due to a failed archive write. To speed archiving, consider adding more archive processes or putting the archive files on striped disks.

log file switch (checkpoint incomplete)

Waiting for a log switch because the session cannot wrap into the next log. Wrapping cannot be performed because the checkpoint for that log has not completed.

log file switch (clearing log file)

Waiting for a log switch because the log is being cleared due to a CLEAR LOGFILE command or implicit clear logfile executed by recovery.

log file switch (private strand flush incomplete)

User sessions trying to generate redo, wait on this event when LGWR waits for DBWR to complete flushing redo from IMU buffers into the log buffer; when DBWR is complete LGWR can then finish writing the current log, and then switch log files.

log file sync – Oracle Wait Event

When a user session commits, the session’s redo information needs to be flushed from memory to the redo logfile to make it permanent.

At the time of commit, the user session will post the LGWR to write the log buffer (containing the current unwritten redo, including this session’s redo information) to the redo log file. When the LGWR has finished writing, it will post the user session to notify it that this has completed. The user session waits on ‘log file sync’ while waiting for LGWR to post it back to confirm all redo changes have made it safely on to disk.

The time between the user session posting the LGWR and the LGWR  posting the user after the write has completed is the wait time for ‘log file sync’ that the user session will show.

Most common causes of log file sync wait events are:-

• Slow I/O throughput of the disks hosting redo log files.
• Excessive Application Commits.

row cache lock – Oracle Wait Event

The Row Cache or Data Dictionary Cache is a memory area in the shared pool that holds data dictionary information to reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occurring, or possibly recursive operations such as storage management and incrementing sequence numbers.

Use below query to find which cache (cache id) is being waited for:-

1 2 3 4 5 6 7 8 9 10 11

SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';   P1TEXT P1 P2TEXT P2 P3TEXT P3   cache id 8 mode 0 request 3   SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8;   PARAMETER COUNT GETS GETMISSES MODIFICATIONS   DC_SEQUENCES    869    76843 508432   4500<span              data-mce-type="bookmark"                id="mce_SELREST_start"              data-mce-style="overflow:hidden;line-height:0"              style="overflow:hidden;line-height:0"           ></span><span

Then depending upon the cache initialization parameter, proceed accordingly:-

DC_SEQUENCES
For DC_SEQUENCES, consider caching sequences using the cache option.

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities.

DC_SEGMENTS
Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.

DC_USERS
This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.

DC_TABLESPACES
The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention.

Buffer busy waits – Oracle Wait Event

Buffer busy wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is “busy”. The two main cases where this can occur are:

1. Another session is reading the block into the buffer
2. Another session holds the buffer in an incompatible mode to our request

Finding the blocking process can be quite difficult as the information required is not externalized. If P3 (Reason Code)(Column P3 of v$session_wait) shows that the “buffer busy wait” is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: “db file sequential read” or “db file scattered read”) for the same file# and block#. And If the wait is due to the buffer being held in an incompatible mode then it should be freed very soon.

As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that “buffer busy waits” for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) – the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk – the other sessions (scanning the same index) quickly ‘catch up’ and want the block which is currently being read from disk – they wait for the buffer as someone is already reading the block in.

The following hints may be useful for particular types of contention – these are things that MAY reduce contention for particular situations:

Block Type	Possible Actions
data blocks	Eliminate HOT blocks from the application. Check for repeatedly scanned / unselective indexes. Change PCTFREE and/or PCTUSED. Check for ‘right- hand-indexes’ (indexes that get inserted into at the same point by many processes). Increase INITRANS. Reduce the number of rows per block.
segment header	Increase of number of FREELISTs. Use FREELIST GROUPs (even in single instance this can make a difference).
freelist blocks	Add more FREELISTS. In case of Parallel Server make sure that each instance has its own FREELIST GROUP(s).
undo header	Add more rollback segments.