I/O-related Wait Events

posted Jul 27, 2011, 10:33 AM by Sachchida Ojha   [ updated Jul 27, 2011, 11:24 AM by Sachchida Ojha ]
I/O-related Wait Events that occur most often in Oracle databases together with reference notes describing each wait. 

Datafile I/O-Related Wait Events:

'db file sequential read' Note:34559.1
'db file scattered read' Note:34558.1
'db file parallel read'
'direct path read' Note:50415.1
'direct path write' Note:50416.1
'direct path read (lob)'
'direct path write (lob)'
Controlfile I/O-Related Wait Events:
'control file parallel write'
'control file sequential read'
'control file single write'

Redo Logging I/O-Related Wait Events:

'log file parallel write' Note:34583.1
'log file sync' Note:34592.1
'log file sequential read'
'log file single write'
'switch logfile command'
'log file switch completion'
'log file switch (clearing log file)'
'log file switch (checkpoint incomplete)'
'log switch/archive'
'log file switch (archiving needed)'

Buffer Cache I/O-Related Wait Events:

'db file parallel write' Note:34416.1
'db file single write'
'write complete waits'
'free buffer waits'

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads:

select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read' and b.event = 'db file scattered read';
use v$session_wait View to find information about Waiting:
P1 = file#
P2 = block#
P3 = blocks

file# : This is the file# of the file that Oracle is trying to read
block# : This is the starting block number in the file from where Oracle starts reading the blocks.
blocks : This parameter specifies the number of blocks that Oracle is trying to read from the file# starting at block#.
This is usually "1" but if P3 > 1 then this is a multiblock read. Multiblock "db file sequential read"s may be seen in earlier Oracle versions when reading from a
SORT (TEMPORARY) segments.

We can see this wait event on v$session_event View:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits> 0 ORDER BY 3,2;

---------- ----------- -----------
310 1 0
1871 1 0
1238 2 0
5263 2 0
4650 5 0
3403 1 1
1555 2 1
1566 3 1
3412 3 1
2491 3 1
4025 4 1

SQL>SELECT SID, EVENT, P1, P2 , P3 FROM v$session_wait where event='db file sequential read' ;

---------- -------------------------------------------------- ---------- ---------- ----------
1874 db file sequential read 418 2502194 1
2474 db file sequential read 66 20134 1
2478 db file sequential read 435 268260 1
4343 db file sequential read 420 485693 1

SQL>SELECT tablespace_name, file_name FROM dba_data_files WHERE file_id = 418;

------------------------------ ----------------------------------------------------------------------
TS_ARBINET_DATA +TDB1_DATA/tdb1/datafile/ts_arbinet_data.378.751650823

block# ->
If the file is NOT a TEMPFILE then the following query should show the name and type of the segment:

SYS@TDB1 SQL>SELECT owner , segment_name , segment_type FROM dba_extents WHERE file_id = 46 AND 2502194 BETWEEN block_id AND block_id + blocks-1;

no rows selected

About Partition use:

SELECT owner , segment_name , segment_type, partition_name FROM dba_extents WHERE file_id = 46 AND 6072 BETWEEN block_id AND block_id + blocks -1

------------------------------ ---------------- ------------------ -------------------

When see waiting time "db file sequential read" event, we should investigate V$SQLAREA to see SQL statements performing many disk reads. Cross-check I/O
system and V$FILESTAT for poor read time.

We can reduce a waiting:
- Check SQL statements [use index scans] , Tune SQL Statements.
- Check buffer cache, A larger buffer cache can help; (increase SGA)
- Partition TABLE and INDEX , that can help to reduce the amount of data you need to look at.