Search This Blog

direct path write wait events in Oracle

This wait event belongs to "User I/O" wait class and signifies that Oracle Server Process is writing buffers ino datafiles bypassing the SGA. This occurs when direct path append insert operation is performed. This also occirs for CTAS done in parallel and parallel DML. The number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes.

 Cases: It occurs in the following situations:
  • Parallel DML are issued to create objects (CREATE TABLE hr.admin_emp_dept
    AS SELECT * FROM hr.employees
    WHERE department_id = 10;)
  • Parallel DMLS is issued to populate objejcts
  • Direct path loads  ( Insert /*+ append / into table2 select * from table1)
To see what object is undergoing above wait event query V$SESSION or V$SESSION_WAIT parameter columns:

Wait event parameters are explained as below: 
P1: File_id for the write call
P2: Start block_id for the write call
P3: Number of blocks in the write call

select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name like 'direct path write';
NAME                 PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS
-------------------- -------------------- -------------------- -------------------- -------------
direct path write    file number          first dba            block cnt            User I/O
value of P1 and p2 can be passed to DBA_EXTENTS to find the object undergoing direct path write event.

Action Plan: Make sure I/O distribution is balanced and I/O subsystem is adequate to meet parallel I/O demand on data files, Ideally parallel degree should not exceed the actual parallelization data files on differnt devices.


Follow by Email