read by other session - Diagnose Oracle Buffer Read Waits

read by other session

Overview

The read by other session wait event occurs when a session needs a data block that is not currently in the buffer cache, but another session is already in the process of reading that same block from disk. Rather than issuing a duplicate physical I/O, Oracle makes the waiting session sleep until the first session completes its read and places the block in the buffer cache.

This event was introduced in Oracle 10g as a split from the older buffer busy waits event. Understanding this distinction is important: buffer busy waits indicates contention on a block that is in the buffer cache, whereas read by other session indicates contention on a block being fetched from disk. Both represent hot block contention, but the underlying cause and resolution differ.

When this wait appears prominently in your top wait events, it almost always means that multiple sessions are simultaneously requesting the same blocks from disk — a classic symptom of hot block access patterns, full table scan storms, or index leaf block contention under heavy concurrent load.

When This Wait Is a Problem

Acceptable: Occasional read by other session waits at low average wait times (under 5ms) are normal in any OLTP system, especially during startup or cache warm-up.

Investigate when:

• Average wait time exceeds 10–15ms consistently
• The event appears in the top 5 wait events in AWR/Statspack reports
• Wait time totals more than 5% of total DB time in an AWR snapshot
• A small number of object IDs (P1/P2 values) account for the bulk of waits — indicating specific hot blocks

Critical: If average wait time exceeds 50ms alongside high I/O latency, the root cause may be storage performance rather than logical hot block contention.

The P1 parameter of this wait event is the absolute file number, and P2 is the block number. Repeatedly seeing the same P1/P2 values in V$SESSION_WAIT points to a specific hot block that warrants further investigation.

Diagnostic Queries

1. Identify Current Sessions Waiting and the Blocks Being Contested

-- Sessions currently experiencing read by other session
SELECT
    sw.sid,
    sw.serial#,
    sw.username,
    sw.program,
    sw.event,
    sw.wait_time,
    sw.seconds_in_wait,
    sw.p1 AS file#,
    sw.p2 AS block#,
    sw.p3 AS class#,
    o.object_name,
    o.object_type,
    o.owner
FROM
    v$session sw
    LEFT JOIN dba_extents e
        ON sw.p1 = e.file_id
        AND sw.p2 BETWEEN e.block_id AND (e.block_id + e.blocks - 1)
    LEFT JOIN dba_objects o
        ON e.owner = o.owner
        AND e.segment_name = o.object_name
WHERE
    sw.event = 'read by other session'
    AND sw.wait_class = 'User I/O'
ORDER BY
    sw.seconds_in_wait DESC;

2. Aggregate by Object — Find the Hottest Blocks

-- Top objects generating read by other session waits (current)
SELECT
    o.owner,
    o.object_name,
    o.object_type,
    COUNT(*) AS waiter_count,
    SUM(sw.seconds_in_wait) AS total_wait_seconds,
    sw.p1 AS file#,
    sw.p2 AS block#
FROM
    v$session sw
    JOIN dba_extents e
        ON sw.p1 = e.file_id
        AND sw.p2 BETWEEN e.block_id AND (e.block_id + e.blocks - 1)
    JOIN dba_objects o
        ON e.owner = o.owner
        AND e.segment_name = o.object_name
WHERE
    sw.event = 'read by other session'
GROUP BY
    o.owner, o.object_name, o.object_type, sw.p1, sw.p2
ORDER BY
    waiter_count DESC;

3. System-Level Wait Statistics from V$SYSTEM_EVENT

-- Current session wait statistics
SELECT
    event,
    total_waits,
    total_timeouts,
    time_waited,
    average_wait,
    max_wait,
    time_waited_micro
FROM
    v$system_event
WHERE
    event IN (
        'read by other session',
        'buffer busy waits',
        'db file sequential read',
        'db file scattered read'
    )
ORDER BY
    time_waited DESC;

4. Historical Analysis via Active Session History

-- Historical read by other session waits from ASH
-- Identify which SQL statements and objects are responsible
SELECT
    ash.sql_id,
    ash.current_obj#,
    o.object_name,
    o.object_type,
    o.owner,
    COUNT(*) AS ash_samples,
    ROUND(COUNT(*) * 10 / 60, 1) AS approx_wait_minutes,
    MIN(ash.sample_time) AS first_seen,
    MAX(ash.sample_time) AS last_seen
FROM
    v$active_session_history ash
    LEFT JOIN dba_objects o
        ON ash.current_obj# = o.object_id
WHERE
    ash.event = 'read by other session'
    AND ash.sample_time > SYSDATE - 1/24  -- Last hour
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;

5. Compare Buffer Cache Hit Ratio and Cache Size

-- Buffer cache hit ratio and size
SELECT
    bp.name,
    bp.block_size,
    bp.buffers,
    ROUND(bp.buffers * bp.block_size / 1024 / 1024, 2) AS size_mb,
    bp.physical_reads,
    bp.db_block_gets + bp.consistent_gets AS logical_reads,
    ROUND(
        100 * (1 - bp.physical_reads / NULLIF(bp.db_block_gets + bp.consistent_gets, 0)),
        2
    ) AS hit_ratio_pct
FROM
    v$buffer_pool_statistics bp
WHERE
    bp.buffers > 0;

Root Causes

1. Full Table Scan Storms (Most Common)

When multiple sessions execute queries that perform full table scans on the same large table simultaneously, each session reads blocks sequentially. As session A reads block 1000, sessions B, C, and D also arrive at block 1000 needing the same physical I/O. Oracle serializes these reads — only one session performs the read while others wait. This is especially pronounced when the table does not fit in the buffer cache, forcing repeated physical I/O across all sessions.

Identify full-scan contention by looking at the SQL_ID values in ASH and checking their execution plans. A TABLE ACCESS FULL against a large table during concurrent load is the textbook scenario.

2. Index Leaf Block Contention

Indexes with poor selectivity or monotonically increasing key values (e.g., sequence-based primary keys) concentrate inserts and reads onto a small set of index leaf blocks (right-hand side of the B-tree). When many sessions simultaneously read and insert into the same leaf blocks, read-by-other-session waits accumulate alongside buffer busy waits. Reverse key indexes or hash partitioning of the index can distribute this load.

3. Insufficient Buffer Cache Size

When the buffer cache cannot hold the working set of data for active sessions, blocks are constantly evicted and re-read from disk. Even moderately concurrent access to the same table produces read-by-other-session waits because evicted blocks must be re-fetched. The buffer cache hit ratio dropping below 95% for OLTP workloads is a warning sign.

4. Cold Start / Cache Warm-Up

After a database restart or following a large cache flush (e.g., ALTER SYSTEM FLUSH BUFFER_CACHE), the buffer cache is empty. Any concurrent access during the warm-up period will produce elevated read-by-other-session waits as sessions race to populate the cache from disk.

5. Poorly Partitioned Tables Under Concurrent Load

Non-partitioned tables accessed by many concurrent sessions produce contention on shared blocks. Table partitioning eliminates cross-session block contention because each session’s range or hash partition is accessed independently, reducing the probability of two sessions competing for the same block.

Resolution Steps

Step 1: Identify the Contended Objects

Run the diagnostic queries above (particularly the ASH query) to identify which objects and SQL statements are generating the most waits. Note the object names and SQL IDs.

Step 2: Evaluate and Increase the Buffer Cache (If Undersized)

-- Check current DB_CACHE_SIZE and memory targets
SHOW PARAMETER db_cache_size;
SHOW PARAMETER memory_target;
SHOW PARAMETER sga_target;

-- Increase buffer cache (requires sufficient SGA headroom)
ALTER SYSTEM SET db_cache_size = 4G SCOPE=BOTH;

-- If using AMM/ASMM, increase the target
ALTER SYSTEM SET sga_target = 16G SCOPE=BOTH;

Step 3: Cache Small Hot Tables in the KEEP Pool

-- Place frequently-scanned lookup tables in the KEEP buffer pool
-- This prevents them from being aged out by full-scan streams
ALTER TABLE lookup_codes STORAGE (BUFFER_POOL KEEP);

-- Size the KEEP pool appropriately
ALTER SYSTEM SET db_keep_cache_size = 512M SCOPE=BOTH;

-- Verify table is assigned to KEEP pool
SELECT owner, table_name, buffer_pool
FROM dba_tables
WHERE buffer_pool = 'KEEP';

Step 4: Partition Tables to Reduce Block Contention

-- Example: Convert a non-partitioned table to range partitioning
-- This eliminates cross-partition block contention for concurrent sessions
CREATE TABLE sales_new
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_2025 VALUES LESS THAN (MAXVALUE)
)
AS SELECT * FROM sales;

Step 5: Address Index Leaf Block Hot Spots

-- For sequence-based indexes with right-side contention,
-- consider a reverse key index to spread inserts across leaf blocks
CREATE INDEX idx_orders_id_rev
ON orders(order_id) REVERSE
TABLESPACE idx_tbs;

-- Or use index partitioning to distribute leaf blocks
CREATE INDEX idx_orders_id_hash
ON orders(order_id)
GLOBAL PARTITION BY HASH (order_id)
PARTITIONS 8;

Step 6: Reduce Concurrency on Problematic Scans

-- Use parallel query for large scans to reduce duration
-- (Fewer sessions compete for fewer seconds)
SELECT /*+ PARALLEL(t, 4) */ *
FROM large_table t
WHERE conditions;

-- For reporting queries, consider result cache to eliminate re-reads
SELECT /*+ RESULT_CACHE */
    category, SUM(amount)
FROM sales
GROUP BY category;

Prevention & Tuning

Right-size the buffer cache: The buffer cache is the primary defense against read-by-other-session. Use the DB Cache Advice view to estimate optimal size:

SELECT size_for_estimate, buffers_for_estimate,
       estd_physical_reads, estd_physical_read_factor
FROM v$db_cache_advice
WHERE name = 'DEFAULT' AND block_size = (
    SELECT value FROM v$parameter WHERE name = 'db_block_size'
)
ORDER BY size_for_estimate;

Use multiple buffer pools: Assign frequently accessed reference data to the KEEP pool and large, infrequently scanned objects to the RECYCLE pool to prevent cache pollution.

Implement partitioning: Table and index partitioning is the most effective structural defense against hot block contention in concurrent workloads. Partition pruning also reduces the scan footprint.

Tune SQL to avoid unnecessary full scans: Ensure that lookup queries use indexes appropriately. A missing index on a frequently queried column causes repeated full scans and amplifies read-by-other-session.

Schedule large batch jobs outside peak hours: Full-scan-heavy ETL and reporting jobs that compete with OLTP workloads are a primary driver of this wait. Use Oracle Scheduler to run them during off-peak windows.

Use Automatic Shared Memory Management (ASMM): Set SGA_TARGET and allow Oracle to dynamically balance buffer cache against shared pool as workload demands change.

Related Wait Events

• buffer busy waits — Contention on a block already in the buffer cache (class-specific contention: segment header, free list, etc.)
• db file sequential read — Single-block physical read (index range scans, rowid fetches); no session concurrency involved
• db file scattered read — Multi-block physical read (full table scans and fast full index scans)
• gc buffer busy acquire / gc buffer busy release — RAC equivalents when blocks are mastered by remote instances
• free buffer waits — Buffer cache is full; DBWR cannot write dirty blocks fast enough to free buffers for new reads