Oracle problems

direct path read: The Scan, the Spill, and the Fix

3 November 2026

direct path read is a read that bypasses the buffer cache: the blocks go from the datafiles straight into the memory of the session, and no other session finds them in the cache afterwards. Oracle uses it for parallel query, for the full scan of a table it judges large, for LOBs stored without caching, and for the temp segments of a sort or a hash join that did not fit in memory, which has an event of its own, direct path read temp. Oracle's reference describes the mechanics: the reads are issued asynchronously and the wait is the moment the session "needs to make sure that all outstanding asynchronous I/O have been completed to disk". The event at the top of the list is therefore one of two things, a big scan or a spill, and the first query below says which.

What the wait means

From the reference: "During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests". The wait time is "10 seconds. The session will be posted by the completing asynchronous I/O. It will never wait the entire 10 seconds", and the parameters are not a file and a block but a descriptor address, the "first dba", the oldest I/O outstanding, and a block count. Two consequences follow. The blocks read this way never enter the buffer cache, so a table scanned with direct path reads is read from disk again by the next session, however often it is scanned. And since 11g Oracle chooses this path for a serial full scan of any table it judges large against the buffer cache, so a scan that waited on db file scattered read on an older release, and was partly served from the cache, now waits here and is served by the storage every time.

Scan or spill

Oracle keeps the direct reads apart in V$SYSSTAT; two readings an hour apart give the rate, and the shares say what kind they are:

SELECT name, value
FROM   v$sysstat
WHERE  name IN ('physical reads', 'physical reads direct',
                'physical reads direct temporary tablespace',
                'physical reads direct (lob)',
                'sorts (disk)',
                'workarea executions - optimal',
                'workarea executions - onepass',
                'workarea executions - multipass');

Direct reads that are mostly temporary tablespace, with onepass and multipass work areas climbing, are spills: sorts and hash joins larger than their share of the PGA. Direct reads that are mostly not temp, with the LOB figure small, are scans. The sessions on the event right now, by statement:

SELECT s.sql_id, s.event, COUNT(*) AS sessions,
       MIN(s.sql_exec_start) AS running_since
FROM   v$session s
WHERE  s.event IN ('direct path read', 'direct path read temp')
AND    s.status = 'ACTIVE'
GROUP  BY s.sql_id, s.event
ORDER  BY sessions DESC;

Twenty sessions on one sql_id are usually one parallel statement and its servers. The segments that take the direct reads:

SELECT owner, object_name, object_type, value AS direct_reads
FROM   v$segment_statistics
WHERE  statistic_name = 'physical reads direct'
ORDER  BY value DESC FETCH FIRST 15 ROWS ONLY;

The spill, while it runs

SELECT sid, sql_id, operation_type, policy,
       ROUND(work_area_size / 1048576) AS mb_in_memory,
       ROUND(tempseg_size / 1048576)   AS mb_in_temp,
       number_passes
FROM   v$sql_workarea_active
WHERE  tempseg_size IS NOT NULL
ORDER  BY tempseg_size DESC;

operation_type says whether it is a SORT, a HASH JOIN or a GROUP BY that spilled, mb_in_temp how much of it is on disk, and number_passes how many times it will be read back: one pass is a sort that overflowed once, several passes is a sort that reads its own temp segment again and again, and a statement that does so every minute is what fills the temp tablespace. The plan of the statement shows the operation and, for a parallel scan, the PX lines above it:

SELECT id, operation, options, object_name, cardinality,
       ROUND(temp_space / 1048576) AS temp_mb
FROM   v$sql_plan
WHERE  sql_id = '&sql_id' AND child_number = 0
ORDER  BY id;

The fix, in order

1. A scan that should be a seek. The same rule as for every scan: a statement that needs a few rows and reads a whole table wants an index or a predicate the index can use, and the scan page has the checks. Direct path makes the wrong scan worse, because nothing it reads is kept for the next run.
2. A scan that is right: fewer blocks, then more streams. Partition pruning, archived rows and compression cut the blocks; a degree of parallelism the storage can serve cuts the time. Direct reads are bound by throughput, not latency: V$SYSMETRIC's "Physical Read Total Bytes Per Sec" against what the storage delivers says whether a higher degree will help or just queue.
3. A spill: give the sort its memory. V$PGA_TARGET_ADVICE says what a larger PGA_AGGREGATE_TARGET would save in passes; the PGA limit page has the sizing. Then the statement: an ORDER BY the application does not use, a DISTINCT that hides a bad join, a hash join whose build side is the big table, each one is a spill that no memory setting fixes as cheaply as the rewrite.
4. LOBs read from disk every time. "physical reads direct (lob)" climbing is a LOB column stored NOCACHE that the application reads on every request; the CACHE option of the LOB storage clause puts it through the buffer cache like any other column.
5. A table Oracle decided is large, scanned all day. When a table crosses the size at which Oracle switches a serial scan to direct path, every run reads it from disk, and the same scan was served from the cache the week before. The index that makes the scan unnecessary is the fix that lasts; a table small enough to cache but read often can be kept in the KEEP pool so the optimizer's choice no longer matters.
6. Schedule the big one. A parallel scan at full degree during the day takes the storage from everyone; the same statement at four in the morning takes nothing from anybody.

See it coming

Radar Engine, the detection layer of Scops, samples every session every 5 seconds and the work areas and temp usage every 10 minutes, so a spill is a finding with the statement's name while it is still running, and a scan that takes the storage is a finding with the statement that holds most of the active sessions:

The finding names the statement, the sessions and servers behind it, and for a spill the temp it holds and the passes it makes. The Activity chart draws the User I/O class by statement, so the four-hour scan and the sort behind it are one click apart, on any edition and without the Diagnostics Pack.

Questions

What is direct path read?
A read from the datafiles straight into the session's memory, bypassing the buffer cache. Oracle uses it for parallel query, for serial full scans of tables it judges large, for LOBs stored without caching, and, as direct path read temp, for sorts and hash joins that spill to the temp tablespace.

Why does a full table scan use direct path read instead of db file scattered read?
Since 11g Oracle reads a large table straight into the session's memory rather than through the buffer cache, because caching a table bigger than a good part of the cache would evict everything else. The scan then waits on direct path read, and the next session reads the table from disk again.

What is direct path read temp?
The same mechanism reading a temp segment back: a sort, a hash join or a group by that did not fit in its share of the PGA wrote its work to the temp tablespace and now reads it. V$SQL_WORKAREA_ACTIVE shows the statement, the size on disk and the number of passes.

Is direct path read bad?
For a report or a batch that must read a whole table, it is the fastest way to do it. It is a problem when the statement should have used an index, when a sort spills that memory or a rewrite would keep in RAM, and when a table read all day stopped being cached because it grew past Oracle's threshold.

Related problems

• db file scattered read
• Temp tablespace full: ORA-01652
• ORA-04036: PGA memory exceeds the limit
• ORA-12827: parallel server pool full
• Oracle wait events, by class
• All Oracle problems

Sources

• Oracle: Descriptions of wait events, V$SQL_WORKAREA_ACTIVE, V$PGA_TARGET_ADVICE, V$SEGMENT_STATISTICS
• Oracle: Instance tuning using performance views, Monitoring parallel execution performance
• Scops docs: Detector reference