direct path read - Diagnose Oracle Direct Path I/O Waits

direct path read

Overview

Wait Event Class: User I/O

Parameters: file number, first block, block count

direct path read fires when Oracle reads database blocks directly into the session’s PGA, bypassing the SGA buffer cache entirely. This is the fundamental distinction from db file sequential read and db file scattered read, which both bring blocks into the shared buffer cache.

Why Direct Path Reads Exist

The buffer cache is most valuable for data accessed repeatedly by many sessions. For large table scans or sorts that will only be read once, caching the data in the shared buffer pool is wasteful — it displaces frequently-used hot blocks. Oracle uses direct path I/O for these single-pass operations to protect buffer cache efficiency.

When Oracle Uses Direct Path Reads

Parallel Query Operations: When a query uses parallel execution, each parallel slave reads its assigned data file range directly into PGA. The parallel coordinator then assembles results. This is the most common source of direct path read in data warehouse workloads.

Serial Direct Path Reads (11g+): Oracle 11g introduced an adaptive mechanism where even serial full table scans can use direct path I/O if the segment is large enough relative to the buffer cache. The threshold is controlled by the hidden parameter _small_table_threshold. Specifically:

• If a table is larger than _small_table_threshold blocks, Oracle may read it directly into PGA even in a serial execution plan
• This avoids polluting the buffer cache with large scans

Temp Tablespace Reads: Sorts, hash joins, and other memory-spilling operations write to the temp tablespace and then read back from it using direct path I/O. These register as direct path read (lob) or direct path read temp.

LOB Reads: SecureFile and BasicFile LOBs stored out-of-line are often read via direct path I/O.

---

When This Wait Is a Problem

Context-Dependent Assessment

Unlike most wait events, direct path read is often the correct and expected behavior. The key questions are:

1. Is this a parallel query workload where large scans are expected?
2. Or is this unexpectedly high on an OLTP system where queries should be index-driven?
3. Is the latency per read reasonable given the storage system?

Scenario	Assessment
Parallel analytics on large tables	Expected — optimize throughput, not latency
Serial scan of large table (OLTP)	Investigate — may indicate missing index
Temp tablespace reads consuming significant time	Investigate — memory spill from sorts/hash joins
Direct path read average latency > 10 ms	Storage issue — even direct reads should be fast
Dominating DB time on OLTP system	Problem — missing indexes or disabled serial direct path

---

Diagnostic Queries

1. System-Level Direct Path Read Statistics

-- Instance-level direct path read overview
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2)           AS total_secs,
    ROUND(average_wait * 10, 2)           AS avg_wait_ms,
    ROUND(time_waited * 100.0 /
          NULLIF((SELECT SUM(time_waited)
                  FROM v$system_event
                  WHERE wait_class NOT IN ('Idle')), 0), 2) AS pct_db_time
FROM v$system_event
WHERE event IN ('direct path read', 'direct path read temp',
                'direct path write', 'direct path write temp')
ORDER BY time_waited DESC;

-- Check physical read stats: direct vs buffered
SELECT
    name,
    value,
    ROUND(value * 100.0 /
          NULLIF(SUM(CASE WHEN name IN ('physical reads',
                                        'physical reads direct') THEN value END)
                 OVER (), 0), 2) AS pct_of_physical
FROM v$sysstat
WHERE name IN ('physical reads', 'physical reads direct',
               'physical reads direct (lob)', 'physical reads cache')
ORDER BY value DESC;

2. Sessions Currently Doing Direct Path Reads

-- Active sessions in direct path read
SELECT
    sw.sid,
    sw.serial#,
    s.username,
    s.program,
    s.sql_id,
    sw.p1                                       AS file_number,
    sw.p2                                       AS first_block,
    sw.p3                                       AS block_count,
    sw.seconds_in_wait,
    f.name                                      AS file_name
FROM v$session_wait  sw
JOIN v$session       s  ON sw.sid  = s.sid
LEFT JOIN v$datafile f  ON sw.p1   = f.file#
WHERE sw.event IN ('direct path read', 'direct path read temp')
ORDER BY sw.seconds_in_wait DESC;

-- Check if parallel query is involved
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    s.parallel,
    s.pdml_enabled,
    s.pq_status,
    s.last_call_et
FROM v$session s
WHERE s.sid IN (
    SELECT sid FROM v$session_wait
    WHERE event = 'direct path read'
);

3. ASH Analysis — Objects and SQL Behind Direct Path Reads

-- Top objects driving direct path reads (last hour)
-- Requires Diagnostics Pack license
SELECT
    ash.sql_id,
    ash.current_obj#,
    o.object_name,
    o.object_type,
    o.owner,
    COUNT(*)                              AS ash_samples,
    COUNT(*) * 10                         AS est_wait_secs,
    ROUND(COUNT(*) * 100.0 /
          SUM(COUNT(*)) OVER (), 2)       AS pct
FROM v$active_session_history  ash
LEFT JOIN dba_objects           o ON ash.current_obj# = o.object_id
WHERE ash.event       = 'direct path read'
  AND ash.sample_time > SYSDATE - 1/24
GROUP BY ash.sql_id, ash.current_obj#, o.object_name, o.object_type, o.owner
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

-- Separate temp tablespace reads from object reads
SELECT
    CASE WHEN ash.current_obj# IS NULL THEN 'Temp/Sort'
         ELSE o.object_name
    END                                   AS object_or_operation,
    ash.event,
    COUNT(*)                              AS samples
FROM v$active_session_history  ash
LEFT JOIN dba_objects           o ON ash.current_obj# = o.object_id
WHERE ash.event        IN ('direct path read', 'direct path read temp')
  AND ash.sample_time  > SYSDATE - 1/24
GROUP BY ash.current_obj#, o.object_name, ash.event
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;

4. Temp Tablespace Usage and Spill Analysis

-- Check temp tablespace usage — high usage indicates sort/hash spills
SELECT
    tablespace_name,
    total_blocks,
    used_blocks,
    free_blocks,
    ROUND(used_blocks * 100.0 / NULLIF(total_blocks, 0), 2) AS pct_used
FROM v$sort_segment
ORDER BY used_blocks DESC;

-- Find sessions currently using temp space
SELECT
    su.sid,
    s.serial#,
    s.username,
    s.sql_id,
    ROUND(su.blocks * 8 / 1024, 2)  AS temp_mb_used,
    su.tablespace
FROM v$sort_usage  su
JOIN v$session     s ON su.session_addr = s.saddr
ORDER BY su.blocks DESC;

5. Check Serial Direct Path Read Threshold

-- _small_table_threshold: tables larger than this use direct path reads
-- Default is typically 2% of buffer cache blocks
SELECT
    name,
    value,
    description
FROM v$parameter
WHERE name IN ('_small_table_threshold', 'db_cache_size', 'sga_target',
               '_serial_direct_read');

-- Check segment sizes relative to threshold
SELECT
    segment_name,
    segment_type,
    ROUND(bytes / 1024 / 1024, 2)  AS size_mb,
    blocks
FROM dba_segments
WHERE owner = 'YOUR_SCHEMA'
  AND segment_name = 'YOUR_TABLE'
ORDER BY bytes DESC;

---

Root Causes

1. Parallel Query Scanning Large Tables

Parallel query is the primary source of direct path read in most databases. When a query uses a parallel execution plan, each PQ slave reads a granule (range of blocks) of the target table directly into PGA. This is correct and expected behavior for large analytic scans.

When it’s a problem: Parallel query consuming too much I/O bandwidth, starving concurrent OLTP sessions. Tune PARALLEL_DEGREE_POLICY, limit degree of parallelism, or use Resource Manager to control parallel query I/O.

2. Serial Direct Path Reads on Oversized Tables (11g+)

In Oracle 11g+, when a serial full table scan reads a “large” table, Oracle may automatically switch to direct path reads. The decision is based on _small_table_threshold. This is often beneficial (protects buffer cache), but can surprise DBAs who expect buffered reads.

A table may be unexpectedly doing direct path reads if:

• It has grown larger than the threshold since it was last analyzed
• The buffer cache was recently downsized
• The _serial_direct_read parameter was changed from its default

3. Sort and Hash Join Spills to Temp Tablespace

SQL operations that exceed PGA allocation (controlled by PGA_AGGREGATE_TARGET and _PGA_MAX_SIZE) spill their working set to the temp tablespace, then read it back. Each read-back registers as direct path read temp. Common causes:

• Insufficient PGA_AGGREGATE_TARGET for the workload
• Queries with large sorts (ORDER BY on millions of rows without an index)
• Large hash joins where the build side doesn’t fit in PGA
• Queries with multiple joins, each requiring sort or hash operations

4. LOB Reads

Large Object (LOB) columns stored out-of-row are often accessed via direct path reads. If an application reads many large LOB values, direct path read (lob) contributes to this wait. Consider whether LOB storage can be optimized (SecureFile vs BasicFile, inline storage for small LOBs, caching policy).

5. Inadequate Parallel Query Tuning

Parallel queries doing direct path reads may be using an inappropriate degree of parallelism — either too high (consuming excessive resources) or too low (not achieving desired throughput). Misconfigured PARALLEL_DEGREE_POLICY or conflicting hints can cause unexpected parallel execution.

---

Resolution Steps

Control Serial Direct Path Reads

-- Check if serial direct path is enabled
SELECT name, value FROM v$parameter WHERE name = '_serial_direct_read';
-- Values: TRUE (always direct path), FALSE (never), AUTO (default — threshold-based)

-- Disable serial direct path reads (forces buffered I/O for serial scans)
-- Use sparingly — disabling it can pollute the buffer cache
ALTER SESSION SET "_serial_direct_read" = FALSE;  -- Session level for testing

-- Or system-wide (requires restart for SPFILE or immediate for MEMORY):
ALTER SYSTEM SET "_serial_direct_read" = FALSE SCOPE=BOTH;
-- Note: Underscore parameters require Oracle Support guidance for permanent changes

-- Preferred alternative: make the "table" small (partition it) so it falls below threshold
-- Or increase buffer cache so threshold is higher

Fix Temp Spills — Increase PGA

-- Check current PGA configuration
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER workarea_size_policy;

-- Check PGA advisor recommendation
SELECT
    pga_target_for_estimate / 1024 / 1024    AS target_mb,
    estd_pga_cache_hit_percentage,
    estd_overalloc_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

-- Increase PGA target if advisor recommends it
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;

-- For 12c+ with PGA_AGGREGATE_LIMIT:
ALTER SYSTEM SET pga_aggregate_limit = 8G SCOPE=BOTH;

-- Find the specific SQL causing large temp spills
SELECT
    s.sql_id,
    SUBSTR(s.sql_text, 1, 80)              AS sql_text,
    s.last_active_time,
    st.value / 1024 / 1024                 AS temp_mb
FROM v$sql        s
JOIN v$sql_workarea st ON s.sql_id = st.sql_id
WHERE st.operation_type IN ('SORT', 'HASH JOIN', 'BITMAP MERGE', 'BITMAP CREATE')
  AND st.active_time > 0
ORDER BY st.value DESC
FETCH FIRST 20 ROWS ONLY;

Control Parallel Query DOP and I/O Impact

-- Limit parallel query degree to prevent I/O saturation
ALTER SYSTEM SET parallel_degree_limit = 8 SCOPE=BOTH;
ALTER SYSTEM SET parallel_max_servers = 64 SCOPE=BOTH;

-- Use Resource Manager to cap parallel query resources
-- Create a consumer group for reporting that limits parallelism
BEGIN
    DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan    => 'MIXED_WORKLOAD_PLAN',
        comment => 'Limit parallel query I/O'
    );
    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'REPORTING_GROUP',
        comment        => 'Reporting users - limited parallelism'
    );
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan                      => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan          => 'REPORTING_GROUP',
        comment                   => 'Limit parallel degree',
        parallel_degree_limit_p1  => 4,
        max_utilization_limit     => 40
    );
    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

Reduce Direct Path I/O for Temp by Optimizing SQL

-- Find sort-heavy SQL from AWR
SELECT
    sql_id,
    sorts,
    executions,
    ROUND(sorts / NULLIF(executions, 0), 2) AS sorts_per_exec,
    SUBSTR(sql_text, 1, 100)                AS sql_snippet
FROM v$sqlarea
WHERE sorts > 1000
ORDER BY sorts DESC
FETCH FIRST 20 ROWS ONLY;

-- Add an index to eliminate sort on large tables
-- If query does: SELECT ... FROM orders ORDER BY order_date
CREATE INDEX idx_orders_date ON orders(order_date) TABLESPACE indexes;
-- Optimizer can now use index scan in order, eliminating the sort

-- Or use analytic pre-aggregation to reduce sort volume
-- Partition the sort across smaller result sets

---

Prevention & Tuning

1. Right-Size PGA to Avoid Temp Spills

Temp tablespace reads (direct path read temp) are always suboptimal compared to in-memory operations. Monitor PGA advisor recommendations and size PGA_AGGREGATE_TARGET to eliminate or minimize spills. The PGA advisor in AWR shows historical recommendations.

2. Partition Large Tables

Large tables that generate direct path reads (both parallel and serial) are prime candidates for partitioning. Partition pruning limits the number of blocks read, reducing both the volume of direct path I/O and its impact on buffer cache.

3. Monitor Direct Reads vs Buffered Reads Ratio

-- Track ratio of direct vs buffered physical reads over time from AWR
SELECT
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24') AS hour,
    SUM(CASE WHEN stat_name = 'physical reads direct'
             THEN (e.value - b.value) ELSE 0 END)     AS direct_reads,
    SUM(CASE WHEN stat_name = 'physical reads cache'
             THEN (e.value - b.value) ELSE 0 END)     AS buffered_reads
FROM dba_hist_sysstat  b
JOIN dba_hist_sysstat  e ON  e.snap_id        = b.snap_id + 1
                          AND e.stat_name      = b.stat_name
                          AND e.dbid           = b.dbid
JOIN dba_hist_snapshot s ON  s.snap_id         = e.snap_id
                          AND s.dbid            = e.dbid
WHERE b.stat_name IN ('physical reads direct', 'physical reads cache')
  AND s.begin_interval_time > SYSDATE - 7
GROUP BY TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24')
ORDER BY 1 DESC;

4. Tune Hash Join and Sort Operations

Hash joins and large sorts are the primary causes of temp spills. Review execution plans for large queries and consider:

• Adding indexes to avoid sorts (ORDER BY, GROUP BY, DISTINCT)
• Rewriting queries to reduce join set sizes (add predicates to filter early)
• Using partitioned hash joins to reduce per-operation memory requirements
• Increasing HASH_AREA_SIZE or SORT_AREA_SIZE for specific sessions if ASMM doesn’t allocate enough

---

Related Wait Events

• db file sequential read — Buffered single-block reads; contrast with direct path for storage latency comparison
• db file scattered read — Buffered multi-block reads; alternative to direct path for large scans
• log file sync — Commit waits; often co-occurs with heavy DML workloads that also generate direct writes
• gc buffer busy — In RAC, direct path reads bypass the global cache entirely, which can affect RAC resource management