Oracle writes buffers from the session’s PGA to the datafiles.
A session can issue multiple write requests and continue processing.
The OS handles the I/O operation.
If the session needs to know if the I/O operation was completed, it will wait on direct path write event.
The direct path write operation is normally used when writing to temporary segments,
in direct data loads (inserts with APPEND hint, or CTAS), or in parallel DML operations.
As with the direct path write event, the number of waits and time waited for this event can be misleading when asynchronous I/O is in use.
This event should occur, and there is no reason for alarm, during batch processing.
Use the script posted in "Identify waiting sessions".
From that, you can:
1. Using the SQL hash value from the CURRENT_EVENT cursor, you can capture the SQL textfrom the GV$SQLTEXT view as shown next and store it in the appropriate history table.
If you are also interested in the SQL statistics, you can get the information from the V$SQLAREA using the same SQL hash value from the cursor:
SELECT INST_ID, HASH_VALUE, ADDRESS, PIECE, SQL_TEXT FROM GV$SQLTEXT WHERE HASH_VALUE = 4190307765 --sql_hash_value AND INST_ID = 1 --RAC Instance ORDER BY INST_ID, PIECE;
2. Find the segment being read:
SELECT DISTINCT A.OBJECT_NAME, A.SUBOBJECT_NAME FROM DBA_OBJECTS A, SYS.X$BH B WHERE (A.OBJECT_ID = B.OBJ OR A.DATA_OBJECT_ID = B.OBJ) AND B.FILE# = 604 --P1 AND B.DBABLK = 2003; --P2