cell single block physical read - Exadata I/O Wait Analysis

cell single block physical read

Overview

The cell single block physical read wait event is the Exadata-specific equivalent of the standard db file sequential read event. It fires when an Oracle database session on an Exadata Database Machine issues a single-block physical I/O request to an Exadata storage cell server and waits for the response.

On standard Oracle systems, single-block reads (index range scans, rowid fetches, segment header reads) generate db file sequential read waits. On Exadata, these same operations generate cell single block physical read waits because the physical I/O is serviced by the Exadata cell server (cellsrv) rather than the local OS I/O stack. The cell server intercepts the I/O request, applies Exadata-specific optimizations (such as flash cache lookup), and returns the data block.

Understanding the Exadata I/O hierarchy is essential for diagnosing this event:

1. DRAM buffer cache (fastest — no wait event fires)
2. Exadata Smart Flash Cache on the storage cells (fast — still generates cell single block physical read but with very low latency)
3. Spinning disk or NVMe on the storage cells (slower — cell single block physical read with higher latency)

The latency you observe for cell single block physical read depends entirely on which tier of the hierarchy is serving the request. Flash cache hits typically produce sub-1ms latency. Disk reads typically produce 2–15ms latency. High average wait times indicate flash cache misses and disk-based reads dominating.

cell single block physical read is distinct from cell multiblock physical read (Exadata Smart Scans and full table scans), cell list of blocks physical read (random multi-block reads for direct path), and cell smart table scan (the actual Smart Scan offload operation).

When This Wait Is a Problem

Acceptable: Average wait times under 1ms indicate flash cache hits. Under 5ms indicates a mix of flash and disk. These are normal for Exadata workloads.

Investigate when:

• Average wait time consistently exceeds 5–10ms — indicates high disk (non-flash) read rates
• The event appears in the top 3 foreground wait events in AWR
• Flash cache hit ratios are below 70–80% for OLTP workloads
• The ratio of cell single block physical read to cell multiblock physical read is very high — OLTP-style I/O pattern with poor Smart Scan utilization

Critical: If average wait time exceeds 20ms on Exadata, something is significantly wrong — either flash cache is too small, storage cells are unhealthy, or the workload has unexpectedly shifted to full scan patterns.

The P1 parameter is the cell disk file number, P2 is the block number, and P3 is the number of blocks read (always 1 for single-block reads). Cross-reference P1 with cell-level diagnostics to identify which storage cell and disk is serving the I/O.

Diagnostic Queries

1. Identify Sessions with cell single block physical read Waits

-- Sessions currently waiting for cell single block physical reads
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.module,
    s.event,
    s.seconds_in_wait,
    s.p1 AS cell_file#,
    s.p2 AS block#,
    s.sql_id,
    sq.sql_text
FROM
    v$session s
    LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE
    s.event = 'cell single block physical read'
ORDER BY
    s.seconds_in_wait DESC;

2. V$SYSTEM_EVENT — Compare Exadata and Standard I/O Wait Times

-- Exadata I/O wait events with latency comparison
SELECT
    event,
    total_waits,
    ROUND(time_waited / 100, 2) AS total_wait_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(average_wait / 100 * 1000, 2) AS avg_wait_ms,
    ROUND(max_wait / 100 * 1000, 2) AS max_wait_ms
FROM
    v$system_event
WHERE
    event IN (
        'cell single block physical read',
        'cell multiblock physical read',
        'cell list of blocks physical read',
        'cell smart table scan',
        'cell smart index scan',
        'db file sequential read',       -- Should be rare on Exadata
        'db file scattered read'         -- Should be rare on Exadata
    )
ORDER BY
    total_wait_secs DESC;

3. Exadata Smart Scan Offload Statistics

-- Smart Scan offload statistics from V$SYSSTAT
-- These show how much I/O work is being offloaded to storage cells
SELECT
    name,
    value,
    CASE
        WHEN name = 'cell physical IO interconnect bytes'
            THEN 'Total bytes sent from cells to DB nodes'
        WHEN name = 'cell physical IO bytes eligible for predicate offload'
            THEN 'Bytes that could have been Smart Scanned'
        WHEN name = 'cell physical IO bytes saved by storage index'
            THEN 'Bytes eliminated by Exadata Storage Indexes'
        WHEN name = 'cell blocks processed by cache layer'
            THEN 'Blocks served from Smart Flash Cache'
        WHEN name = 'cell blocks processed by data layer'
            THEN 'Blocks read from disk (flash cache miss)'
        WHEN name = 'cell blocks helped by minscn optimization'
            THEN 'Blocks skipped by minscn pruning'
        ELSE name
    END AS description
FROM
    v$sysstat
WHERE
    name LIKE 'cell%'
    OR name LIKE 'physical read%'
ORDER BY
    name;

4. Flash Cache Hit Ratio Analysis

-- Calculate Exadata Smart Flash Cache hit ratio
-- High hit ratio (>80%) = most OLTP I/O served from flash
WITH cell_stats AS (
    SELECT
        SUM(CASE WHEN name = 'cell blocks processed by cache layer'
                THEN value ELSE 0 END) AS flash_hits,
        SUM(CASE WHEN name = 'cell blocks processed by data layer'
                THEN value ELSE 0 END) AS disk_reads,
        SUM(CASE WHEN name = 'cell physical IO bytes eligible for predicate offload'
                THEN value ELSE 0 END) AS eligible_bytes,
        SUM(CASE WHEN name = 'cell physical IO bytes saved by storage index'
                THEN value ELSE 0 END) AS si_saved_bytes
    FROM v$sysstat
    WHERE name IN (
        'cell blocks processed by cache layer',
        'cell blocks processed by data layer',
        'cell physical IO bytes eligible for predicate offload',
        'cell physical IO bytes saved by storage index'
    )
)
SELECT
    flash_hits,
    disk_reads,
    flash_hits + disk_reads AS total_cell_reads,
    CASE
        WHEN flash_hits + disk_reads > 0
        THEN ROUND(100 * flash_hits / (flash_hits + disk_reads), 2)
        ELSE 0
    END AS flash_cache_hit_pct,
    ROUND(eligible_bytes / 1024 / 1024 / 1024, 2) AS eligible_smart_scan_gb,
    ROUND(si_saved_bytes / 1024 / 1024 / 1024, 2) AS storage_index_saved_gb
FROM cell_stats;

5. ASH — Identify SQL Driving Single-Block Reads on Exadata

-- Top SQL statements driving cell single block physical reads
SELECT
    ash.sql_id,
    COUNT(*) AS ash_samples,
    ROUND(COUNT(*) * 10, 0) AS approx_wait_secs,
    o.object_name,
    o.object_type,
    o.owner,
    ash.current_obj#
FROM
    v$active_session_history ash
    LEFT JOIN dba_objects o ON ash.current_obj# = o.object_id
WHERE
    ash.event = 'cell single block physical read'
    AND ash.sample_time > SYSDATE - 1/24  -- Last hour
GROUP BY
    ash.sql_id, o.object_name, o.object_type, o.owner, ash.current_obj#
ORDER BY
    ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

6. V$CELL_STATE — Storage Cell Performance (If V$CELL_STATE Is Accessible)

-- Cell-level I/O statistics (requires cellmonitor or DBA access to cell views)
-- Available on Exadata from DB node if connected to the cells
SELECT
    cell_name,
    metric_name,
    metric_value
FROM
    v$cell_state
WHERE
    metric_name IN (
        'Megabytes read from flash cache',
        'Megabytes read from hard disk',
        'IO requests completed',
        'IO requests completed in less than 1 ms',
        'IO requests completed in 1 to 2 ms',
        'IO requests completed in 2 to 4 ms',
        'IO requests completed in greater than 1 s'
    )
ORDER BY
    cell_name, metric_name;

7. Identify Segments Not Benefiting from Smart Scan (Causing Single-Block Load)

-- Tables with high single-block read rates but no Smart Scan activity
-- These may be candidates for storage index or compression improvements
SELECT
    o.owner,
    o.object_name,
    o.object_type,
    COUNT(*) AS single_block_ash_samples
FROM
    v$active_session_history ash
    JOIN dba_objects o ON ash.current_obj# = o.object_id
WHERE
    ash.event = 'cell single block physical read'
    AND ash.sample_time > SYSDATE - 7  -- Last 7 days
    AND o.object_type IN ('TABLE', 'INDEX', 'INDEX PARTITION', 'TABLE PARTITION')
GROUP BY
    o.owner, o.object_name, o.object_type
ORDER BY
    single_block_ash_samples DESC
FETCH FIRST 30 ROWS ONLY;

Root Causes

1. Flash Cache Misses — High Disk Read Rate

When the Exadata Smart Flash Cache cannot hold the working set of frequently accessed single-block data (indexes, OLTP table blocks), blocks must be read from spinning disk or NVMe disk. Flash cache misses produce the same cell single block physical read event but with latency 5–20x higher than flash hits. A flash cache that is too small for the OLTP working set causes persistent high-latency single-block reads.

2. OLTP Workload Dominated by Index Range Scans

Exadata is optimized for large analytical queries that benefit from Smart Scan (full table scan with predicate offload). Pure OLTP workloads with many index range scans, rowid lookups, and single-row fetches generate predominantly cell single block physical read rather than Smart Scan events. This is not necessarily a problem — the flash cache is designed to accelerate exactly this access pattern — but it means Exadata’s Smart Scan advantage is not being leveraged.

3. Missing or Stale Optimizer Statistics Causing Full Table Scans Degraded to Single-Block

In some cases, poor execution plans cause queries to use indexes when a Smart Scan would be more efficient, or vice versa. Stale statistics can cause the optimizer to choose index scans over Smart Scans for large table access, generating single-block reads where Smart Scan bulk reads would be faster and more Exadata-appropriate.

4. Exadata Storage Index Not Effective for the Workload

Exadata Storage Indexes automatically cache the min/max values of column data regions on each disk. For range predicate queries (WHERE date_col BETWEEN x AND y), the storage index can eliminate entire disk regions without reading any blocks. When queries use predicates that the storage index cannot prune (non-range predicates, high-cardinality columns), blocks are read individually, generating more cell single block physical read waits.

5. Storage Cell Health Issues

A degraded storage cell, a failed disk within a cell, or a network issue on the Exadata InfiniBand interconnect can increase I/O latency for a subset of cell reads. If the average wait time for cell single block physical read spikes suddenly, check the cell alert log, InfiniBand connectivity, and disk health via cellcli.

6. Database Buffer Cache Undersized for Exadata Workload

Even on Exadata, the database server’s DRAM buffer cache is the fastest tier. If the buffer cache is undersized, frequently-accessed blocks are constantly evicted and re-read from cells (even if served from flash cache). Increasing DB_CACHE_SIZE on the DB nodes can eliminate cell I/O entirely for hot OLTP data.

Resolution Steps

Step 1: Verify Flash Cache Configuration and Size

-- Check Smart Flash Cache utilization from DB node
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'cell blocks processed by cache layer',    -- Flash hits
    'cell blocks processed by data layer'      -- Disk reads (flash misses)
);

-- If flash hit ratio is below 70%, consider:
-- 1. Increasing the flash cache size (hardware change or Exadata config)
-- 2. Restricting which objects use the flash cache
-- 3. Using CELL_FLASH_CACHE storage attribute to prioritize objects

-- Pin critical tables in the flash cache
ALTER TABLE hr.employees STORAGE (CELL_FLASH_CACHE KEEP);
ALTER INDEX hr.idx_emp_dept STORAGE (CELL_FLASH_CACHE KEEP);

Step 2: Increase the DB Node Buffer Cache for Hot OLTP Data

-- Increase buffer cache to reduce cell I/O for hot blocks
SHOW PARAMETER db_cache_size;
SHOW PARAMETER sga_target;

ALTER SYSTEM SET db_cache_size = 32G SCOPE=BOTH;
-- Or increase SGA_TARGET if using ASMM:
ALTER SYSTEM SET sga_target = 128G SCOPE=BOTH;

Step 3: Ensure Execution Plans Use Smart Scan for Large Table Scans

-- Check if a query is using Smart Scan (look for TABLE ACCESS STORAGE FULL)
-- vs. index scan (TABLE ACCESS BY INDEX ROWID)
EXPLAIN PLAN FOR
SELECT * FROM large_fact_table WHERE region = 'WEST' AND sale_date > SYSDATE - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Force Smart Scan for a specific table access:
SELECT /*+ FULL(t) */ * FROM large_fact_table t WHERE conditions;

-- Ensure statistics are current so optimizer makes the right choice:
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'LARGE_FACT_TABLE',
    degree => DBMS_STATS.AUTO_DEGREE);

Step 4: Tune Index Design for OLTP Access Patterns

-- On Exadata, single-block read performance depends on index design
-- Covering indexes eliminate table block fetches:
CREATE INDEX hr.idx_orders_covering
ON hr.orders(customer_id, order_date, status, total_amount)
ONLINE;
-- A covering index means zero table block fetches = fewer cell single block reads

Step 5: Use Compression to Reduce I/O Volume

-- Exadata Hybrid Columnar Compression (HCC) reduces block count
-- Fewer blocks = fewer cell single block physical reads for the same data
ALTER TABLE hr.historical_orders COMPRESS FOR QUERY HIGH;
-- (HCC requires direct-path insert/CTAS; applies to future data or via rebuild)

Prevention & Tuning

Size the Smart Flash Cache for your OLTP working set: Profile the hot data set size using V$BH (buffer headers) statistics and ensure the flash cache is at least 150% of the frequently-accessed OLTP working set.

Use CELL_FLASH_CACHE KEEP for critical OLTP tables and indexes: Explicitly protect the most critical objects from flash cache eviction caused by large analytical scans.

Separate OLTP and analytics workloads: On mixed Exadata deployments, use Resource Manager I/O plans to allocate flash cache I/O priority to OLTP sessions and restrict large analytics scans from evicting OLTP-critical blocks.

Monitor flash cache hit ratio weekly: Include flash cache hit ratio in your DBA health check. A declining ratio over weeks indicates the working set is growing beyond flash capacity.

Keep optimizer statistics current: Stale statistics on Exadata can cause the optimizer to choose index scans over Smart Scans inappropriately. Implement a regular statistics gathering schedule using DBMS_STATS.GATHER_DATABASE_STATS with OPTIONS => 'GATHER STALE'.

Related Wait Events

• cell multiblock physical read — Exadata multi-block read (full table scans, direct path); the Smart Scan event
• cell smart table scan — Smart Scan with predicate offload; Exadata’s flagship performance feature
• cell smart index scan — Index scan offloaded to storage cell
• db file sequential read — Standard single-block read on non-Exadata; rare on Exadata but may appear for local file access
• cell list of blocks physical read — Random multi-block read from Exadata cells; used for direct path operations and some parallel query operations