Direct Path Reads

Possible Causes:

•          These waits are associated with direct read operations which read data directly into the sessions PGA bypassing the SGA.
•          The "direct path read" and "direct path write" wait events are related to operations that are performed in PGA like sorting, group by operation, hash join.
•          In DSS type systems, or during heavy batch periods, waits on "direct path read" are quite normal.
•          However, for an OLTP system these waits are significant.
•          These wait events can occur during sorting operations which is not surprising as direct path reads and writes usually occur in connection with temporary tsegments.
•          SQL statements with functions that require sorts, such as ORDER BY, GROUP BY, UNION, DISTINCT, and ROLLUP, write sort runs to the temporary tablespace when the input size is larger than the work area in the PGA.

Actions:

•          Ensure the OS asynchronous IO is configured correctly.
•          Check for IO heavy sessions / SQL and see if the amount of IO can be reduced.
•          Ensure no disks are IO bound.
•          Set your PGA_AGGREGATE_TARGET to appropriate value (if the parameter WORKAREA_SIZE_POLICY = AUTO) Or set *_area_size manually (like sort_area_size and then you have to set WORKAREA_SIZE_POLICY = MANUAL.
•          Whenever possible use UNION ALL instead of UNION, and where applicable use HASH JOIN instead of SORT MERGE and NESTED LOOPS instead of HASH JOIN.
•          Make sure the optimizer selects the right driving table. Check to see if the composite index’s columns can be rearranged to match the ORDER BY clause to avoid sort entirely.
•          Also, consider automating the SQL work areas using PGA_AGGREGATE_TARGET in Oracle9i Database.
•          Query V$SESSTAT> to identify sessions with high "physical reads direct".

Remarks:

•          Default size of HASH_AREA_SIZE  is twice that of SORT_AREA_SIZE.
•          Larger HASH_AREA_SIZE will influence optimizer to go for hash joins instead of nested loops.
•          Hidden parameter DB_FILE_DIRECT_IO_COUNT can impact the direct path read performance.It sets the maximum I/O buffer size of direct read and write operations. Default is 1M in 9i.
•          How to identify resource intensive SQL statements?