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
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
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.
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,
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.
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.
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!