Direct path read
                                                 Last update (2008-06-03 13:18:33)
                                                                                                                   Date added (2008-06-03 12:50:39)

Summary
The Direct path read wait event means that Oracle is reading asynchronously from datafiles into the PGA. If asynchronous IO is used then Oracle can submit IO requests and continue processing. It can then pick up the results of the IO request later and will wait on "direct path read" until the required IO completes.

This wait event is sometimes misleading because:
1. the total number of waits does not reflect the number of IO requests
2. the total time spent in "direct path read" does not always reflect the true wait time.

So direct path read is not the diabolic root cause of performance problems.

Actions that can result to direct path reads are:
    * Sort IO (when a sort does not fit in memory)
    * Parallel Query slaves
    * Readahead (where a process may issue an IO request for a block it expects to need in the near future) 

Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting.
 Parameters:	
    P1 = file#
    P2 = start block#
    P3 = num blocks

	file#: Absolute file number of the IO read request being waited for.

        start block#: Block# of the first block in the IO read request being waited for.

                To find the object that Oracle doing the I/O use one of       
                the two following ways
                 
                SELECT owner, segment_type, segment_name, partition_name,      
                tablespace_name 
	        FROM dba_extents 
	        WHERE :P2 BETWEEN block_id AND (block_id + blocks - 1) 
	        AND file_id = :P1;

                Or even better
                SELECT a.SID, c.obj, c.FILE#, c.dbablk 
	        FROM v$session_wait a, x$bh c 
	        WHERE a.p1 = c.FILE#(+)
	        AND a.p2 = c.dbablk(+) 
	        AND a.event = 'direct path read'
	        AND a.SID = :sid_waiting; 

        num blocks: Number of contiguous blocks in the read request. 

 Wait Time:	
The wait blocks until the outstanding IO request completes. 
Note that for asynchronous IO the wait time is not the time 
that the IO itself takes as the wait does not start when the IO is issued.

There is no Oracle timeout on this wait.

Some advise
If you see this wait event then general you don't have any problem. During heavy batch periods, waits on "direct path read" are quite normal!
Reviews
Categories
Filters
Search