Oracle Wait Events – direct path write

A direct path write I/O operation is just the opposite of the direct path read operation, and it writes buffers from a session's PGA to the datafiles. The direct path write operation allows a session to issue multiple write requests and continue processing. At some point, the session will wait on the direct path write event, either to confirm that the OS has completed all outstanding I/Os, or wait for slots to become available so that more writes can be issued. The direct path write operation is normally applied when writing to the temporary tablespace (sort overflows) or when using the direct load method to create or populate objects (CTAS, insert with the APPEND hint). Oracle 8.1.7 has a separate direct write I/O wait event for LOB segments – direct path write (lob).

Like the direct path read event, the number of waits and time waited for the direct path write event can be misleading if asynchronous I/O is supported and used.

V$SESSION_WAIT Parameters:

• P1=The absolute file# 
   
• P2=The starting block# 
   
• P3=Number of blocks read

If P1 is not in DBA_DATA_FILES or V$DATAFILE and it is greater than the DB_FILES INIT.ORA parameter, then it is a TEMPFILE.

Common Causes and Actions

The direct path write wait event is also popular with direct load operations such as CTAS and INSERT /*+ APPEND */ … SELECT. First, make sure the query is properly tuned to minimize the number of sorts, then tune the I/O subsystem to remove hot spots, if any.

Applications that write to LOB segments that are stored as NOCACHE tend to bottleneck on this event. Make sure there is no I/O bound on the disks where the LOB segments live.

Diagnosis

In Oracle 8.1.6 and up, the DBA can identify live sessions that wrote the most number of blocks in direct mode using the query below.

select a.name, b.sid, b.value
from   v$statname a, v$sesstat b
where  a.statistic# = b.statistic#
and    b.value      > 0
and    a.name       = 'physical writes direct'
order by b.value;

select a.name, b.sid, b.value, d.ktssoblks
from   v$statname a, v$sesstat b, v$session c, x$ktsso d
where  a.statistic# = b.statistic#
and    b.value      > 0
and    a.name       = 'physical writes direct'
and    b.sid        = c.sid
and    c.saddr      = d.ktssoses
and    c.serial#    = d.ktssosno
order by b.sid;

The cure for the direct path write waits is to locate and tune the SQL statement to reduce the number of sorts and eliminate hot disks. When the statement is executing, examine the P1 values. If they mostly point to the TEMP tablespace, this means the statement is performing disk sort operations, and if there are a lot of them, then the statement must be tuned to reduce the number of sorts. The DBA can query the X$KTSSO table to see what kind of sorts are taking place to further guide the DBA in tuning the statement. If there is enough memory, consider increasing the SORT_AREA_SIZE parameter, which can help reduce the number of disk sorts. If P1s mostly point to a tablespace other than TEMP, then the statement is probably inserting into a table with the APPEND hint or creating a table with the CTAS operation. In this case, use the appropriate operating system tools or utilities such as sar –d or iostat –dxn to ensure that there are no hot disks. During Direct Path operations, the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if, during a direct write, no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

Wait Time:

10 seconds. The session will be posted by the completing asynchronous I/O. It will never wait the entire 10 seconds. The session waits in a tight loop until all outstanding I/Os have completed.