Search This Blog

direct path read waits

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

Follow by Email