Direct path write
                                                 Last update (2008-07-26 20:35:15)
                                                                                                                   Date added (2008-07-26 20:30:25)

Summary
Direct path writes allow a session to queue an IO write request and continue processing whilst the OS handles the IO. If the session needs to know if an outstanding write is complete then it waits on this waitevent. This can happen because the session is out of free slots and just needs an empty buffer (it waits on the oldest IO) or because it needs to ensure all writes are flushed.

If asynchronous IO is not being used then the IO write request blocks until completed but this dies not show as a wait at the time the IO is issued. The session returns later to pick up the completed IO data but can then show a wait on "direct path write" even though this wait will return immediately

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 write" 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 writes are:
    * 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) 
    * Writes to uncached "LOB" segments (later releases wait on "direct path write (lob)" )

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 write request being waited for.

        start block#: Block# of the first block in the IO write 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 write 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 write" are quite normal!
Reviews
Categories
Filters
Search