Why Oracle 11g Uses Direct Path Reads for Full Table Scans and How to Tune Them

In Oracle 11g, full table scans can use the direct path read method, bypassing the buffer cache and performing physical reads, whereas Oracle 10g always reads through the GC buffer, so the direct path read issue does not exist.

Common Reasons for High Direct Path Read Usage

Massive disk sort operations (ORDER BY, GROUP BY, UNION, DISTINCT, ROLLUP) that cannot be completed in PGA and therefore use temporary tablespace; reading the sorted results from the temporary tablespace triggers direct path reads.

Large hash join operations that store the hash area in temporary tablespace.

Parallel execution of SQL statements.

Full table scans on very large tables: the scanning algorithm now considers table size and cache size to decide whether to bypass the SGA and read directly from disk. Oracle 11g treats large‑table scans as direct path reads, which can be faster than the scattered reads used in 10g and consumes fewer latches.

Most direct path read wait events are likely caused by application‑level issues. Direct path read events are driven by SQL statements that perform direct reads from temporary or regular tablespaces. When the input data exceeds the PGA work area, SQL statements that require sorting write the sorted results to temporary tablespace; the session then waits on the direct path read event while reading those results.

Influencing Parameters

The initialization parameter DB_FILE_DIRECT_IO_COUNT may affect direct path read performance.

A hidden parameter controls the feature: _serial_direct_read = false disables direct path reads. _serial_direct_read = true enables direct path reads.

To significantly reduce direct path reads, you can execute:

alter system set "_serial_direct_read"=never scope=both sid='*';

Adjusting this setting can lower the number of direct path read wait events and improve overall query performance.