Oracle Wait Events Concept : ***************

Showing newest posts with label Interview Questions : Wait Events. Show older posts
Oracle Wait Events

Before looking into wait events, let us understand various state of user process.
Oracle user process is typically in one of the three states:

a. Idle wait. e.g. 'SQL*Net message from client'

b. Running code - Either on CPU or on a run queue. Oracle itself does not know if it is on-CPU or just on a run queue.

c. Waiting

i. for some resource to become available. e.g. enqueue (lock) or a latch
ii. for an activity to complete that it has requested. Like an IO read request.

Oracle has a set of 'Wait Events' for activities in 'a' and 'c', and record CPU utilization for 'b'.

This is best illustrated with a simplified example of few seconds in the life of an Oracle shadow process:

State Notes...
~~~~~ ~~~~~~~~
IDLE : Waiting for 'SQL*Net message from client'. Receives a SQL*Net packet requesting 'parse/execute' of a statement

ON CPU : decodes the SQL*Net packet.

WAITING : Waits for 'latch free' to obtain the a 'library cache' latch Gets the latch.

ON CPU : Scans for the SQL statement in the shared pool, finds a match, frees latch , sets up links to the shared cursor etc.. & begins to execute.

WAITING : Waits for 'db file sequential read' as we need a block which is not in the buffer cache. Ie: Waiting for an IO to complete.

ON CPU : Block read has completed so execution can continue. Constructs a SQL*Net packet to send back to the user containing the first row of data.

WAITING : Waits on 'SQL*Net message to client' for an acknowledgement that the SQL*Net packet was reliably delivered.

IDLE : Waits on 'SQL*Net message from client' for the next thing to do.

Most common wait events are..

Buffer Busy waits/Cache Buffers Chains Latch waits

•This 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

Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.

Typical solutions are:-

Look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimise the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.

Increase the PCTFREE for the table storage parameter. This will result in less rows per block.

Consider implementing reverse key indexes. (if range scans aren't commonly used against the segment)

In v$session_wait, the P1, P2, and P3 columns identify the file number, block number, and buffer busy reason codes, respectively.

"Read By Other Session" wait event.

When user sessions request for data, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same data, the first session will read the data into the buffer cache while other sessions wait. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10g and higher, this wait time is now broken out into the "read by other session" wait event.

Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:

SELECT p1 "file#", p2 "block#"
FROM v$session_wait WHERE event = 'read by other session';

If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object.

The following query will give the name and type of the object:

SELECT owner, segment_name, segment_type
FROM dba_extents WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1

Log File Sync waits

Log file sync waits occur when sessions wait for redo data to be written to disk.
Typically this is caused by slow writes or committing too frequently in the application.

db file sequential read

Wait for an I/O read request to complete. A sequential read is usually a single-block read. This differs from "db file scattered read" in that a sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).

db file scattered read

This wait happens when a session is waiting for a multiblock IO to complete. This typically occurs during full table scans or index fast full scans. Oracle reads up to DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache.

direct path read

Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache).

This style of read request is typically used for:

Sort I/Os when memory Sort areas are exhausted and temporary tablespaces are used to perform the sort
Parallel Query slaves.

direct path write

This wait is seen for:

Direct load operations (eg: Create Table as Select (CTAS) may use this)
Parallel DML operations
Sort IO (when a sort does not fit in memory)

db file parallel write

DBW waits on "db file parallel write" when waiting for a parallel write to files and blocks to complete.
The db file parallel write occurs when the process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

--------------------------------------------------
V$Session_wait_history

From Oracle Database 10g a new view V$Session_wait_history will allow us to see the last few wait events a session waited on.

The last 10 wait events that a session experienced can be displayed using the v$session_wait_history view. The session has to be currently active. Once the session ends this information is not available.

We can use the seq# column to sort the wait events into the order in which the wait events occurred for the session.