Direct path read waits are associated with direct read operations. This wait event falls under the User I/O wait class. An Oracle direct read operation reads data directly into the session's PGA , bypassing the SGA. The data in PGA is not shared with other sessions.
Direct reads may be performed in synchronous or asynchronous mode depending on the platform , and the value of the DISK_ASYNC_IO parameter
A significant number of direct path read waits is most likely an application issue
Common causes, Diagnosis and Actions:
Till Oracle 9i this wait events occured for SQL statements with functions that require sorts such as ORDER BY, GROUP BY, UNION,DISTINCT and ROLLUP, HASH PARTITIONS that do not fit into SQL Work Area.
From Oracle 10g this wait event has been divided in two wait events 1. direct path read and 2. direct path read temp
Direct path read waits in 10g onward occurs for read operations by parallel slaves used in parallel query. While direct path read temp waits occur for sort operations, hashes,index creation etc
This wait event has three parameters: FILE#, FIRST BLOCK#, and BLOCK COUNT.
If level 8 or level 12 10046 trace is enabled for session performing direct path read then number of blocks counts is shown by parameter p3 .
If session performing direct path read is traced with strace , truss, tusc or trace then size of block chunk of direct read can be seen
Note: There is a separate directr read wait event for LOB segments: direct path read(lob). This wait event applies for LOBs that are stored as NOCACHE. When LOBs are stored as CACHE reads and writes go through the SGA and show up as db file sequential reads.
Avoid sorting or hashing large tables else Increase PGA_AGGREGATE_TARGET or MEMORY_MAX_TARGET after querying V$PGASTAT and V$PGA_TARGET_ADVICE or V$MEMORY_TARGET_ADVICE
Direct reads may be performed in synchronous or asynchronous mode depending on the platform , and the value of the DISK_ASYNC_IO parameter
A significant number of direct path read waits is most likely an application issue
Common causes, Diagnosis and Actions:
Till Oracle 9i this wait events occured for SQL statements with functions that require sorts such as ORDER BY, GROUP BY, UNION,DISTINCT and ROLLUP, HASH PARTITIONS that do not fit into SQL Work Area.
From Oracle 10g this wait event has been divided in two wait events 1. direct path read and 2. direct path read temp
Direct path read waits in 10g onward occurs for read operations by parallel slaves used in parallel query. While direct path read temp waits occur for sort operations, hashes,index creation etc
This wait event has three parameters: FILE#, FIRST BLOCK#, and BLOCK COUNT.
If level 8 or level 12 10046 trace is enabled for session performing direct path read then number of blocks counts is shown by parameter p3 .
If session performing direct path read is traced with strace , truss, tusc or trace then size of block chunk of direct read can be seen
Note: There is a separate directr read wait event for LOB segments: direct path read(lob). This wait event applies for LOBs that are stored as NOCACHE. When LOBs are stored as CACHE reads and writes go through the SGA and show up as db file sequential reads.
Avoid sorting or hashing large tables else Increase PGA_AGGREGATE_TARGET or MEMORY_MAX_TARGET after querying V$PGASTAT and V$PGA_TARGET_ADVICE or V$MEMORY_TARGET_ADVICE
Posts
