Tuesday, February 24, 2009

direct path read

The direct path read event occurs when Oracle is reading data blocks directly into the session’s PGA instead of the buffer cache in the SGA.
Direct reads may be performed in synchronous I/O or asynchronous I/O mode, depending on the hardware platform and the value of the initialization parameter, DISK_ASYNCH_IO.
Direct read I/O is normally used while accessing the temporary segments that reside on the disks.
These operations include sorts, parallel queries, and hash joins.

The number of waits and time waited for this event are somewhat misleading.
If the asynchronous I/O is not available, the session waits till the I/O completes.
But these are not counted as waits at the time the I/O request is issued. The session posts a direct path read wait event when accessing the data after the completion of the I/O request.
In this case, the wait time will be negligibly small.

If the asynchronous I/O is available and in use, then the session may issue multiple direct path read requests and continue to process the blocks that are already cached in the PGA.
The session will register direct path read wait event only when it cannot continue processing because the required block has not been read into the buffer.
Therefore, the number of read requests may not be the same as the number of waits. Due to these anomalies, it is unlikely that you will see this wait event reported in V$SYSTEM_EVENT and V$SESSION_EVENT views.
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

No comments:

Post a Comment