$ guides / oracle-database / oracle-database-high-logical-reads ▌

Operations Guides

Oracle logical reads spiking: the system-wide symptom of a bad plan

A sudden spike in session logical reads from V$SYSSTAT is usually the system-wide fingerprint of a plan regression. One query switches from an index scan doing 10 buffer gets per execution to a full table scan doing a million, and at 100 executions per second the database is suddenly doing 100 million additional buffer gets per second. CPU saturates, response times climb, and the whole system feels slow even though no individual component has failed.

The metric: session logical reads = db block gets + consistent gets. Both measure buffer cache block accesses. A 2x or greater sustained increase from baseline warrants investigation, and the most common cause is a bad execution plan replacing a good one.

What this means

Every SELECT, every DML, every recursive query touches blocks, and each buffer cache access is a logical read. Physical reads are the subset where the block was not in cache and had to be fetched from disk.

A spike means the database is doing more block accesses than before for the same or similar workload. Two scenarios dominate:

High logical reads, low physical reads. The working set fits in cache, but a plan regression (index scan replaced by full scan) reads far more cached blocks. Physical I/O does not rise proportionally because the table is cached.
High logical reads, high physical reads. The working set has grown beyond cache, or new full scans are pulling cold blocks from disk. This can also be a plan regression on a table too large to cache.

The diagnostic fork: is the spike caused by more work per execution (plan regression or missing index), or more executions (batch job, application change, retry storm)? The per-execution metrics in V$SQL answer this.

Direct path reads (parallel query, serial direct reads on large segments, temp) bypass the buffer cache. They are tracked separately in physical reads direct, not in session logical reads, which counts only buffer cache operations (db block gets + consistent gets). If your spike correlates with direct path read wait events, check physical reads direct in V$SYSSTAT.

flowchart TD
    A["Logical reads spike 2x baseline"] --> B{"Physical reads also high?"}
    B -- "No" --> C["Cached table: index scan replaced by full scan"]
    B -- "Yes" --> D["Cold blocks from disk: large table full scan"]
    C --> E["Find top SQL by gets/exec"]
    D --> E
    E --> F{"Plan hash changed?"}
    F -- "Yes" --> G["Plan regression confirmed"]
    F -- "No" --> H["New query or batch job"]

Common causes

Cause	What it looks like	First thing to check
Plan regression after stats gather	Spike correlates with `DBMS_STATS` job completion. One SQL_ID has a new `PLAN_HASH_VALUE` with 10x+ `BUFFER_GETS` per execution.	`V$SQL` for the SQL_ID, compare old and new plan hashes. Check `DBA_TABLES.LAST_ANALYZED`.
Bind variable peeking	Same SQL_ID, multiple child cursors. One bind value produces a good plan, another produces a full scan.	`V$SQL` child cursors for the SQL_ID. Check `V$SQL_CS_STATISTICS` for bind peeking.
Missing index on new query	New deployment introduces a query with no supporting index. Full scan from day one.	`V$SQL` for recently active SQL with high `BUFFER_GETS / EXECUTIONS` and growing `EXECUTIONS`.
Batch job or ETL	Spike is time-bounded, matches a scheduled window. Multiple sessions active, parallel query in use.	`V$SESSION.PROGRAM` and `V$SESSION.MODULE` for active sessions. Check scheduler windows.
Parallel query storm	PX wait events visible. Logical reads spread across PX slaves. `physical reads direct` elevated.	`V$SQL` for parallel degree. Check `direct path read` waits in `V$SYSTEM_EVENT`.

Quick checks

All read-only and safe during an active incident.

-- Confirm the spike: sample logical reads twice, N seconds apart
SELECT name, value FROM v$sysstat
WHERE name IN ('session logical reads', 'db block gets',
               'consistent gets', 'physical reads');
-- Rate = (value_t2 - value_t1) / N

-- Top SQL by buffer gets. Also compute gets_per_exec; re-sort by that
-- column to find per-execution regressions rather than total volume.
SELECT sql_id, plan_hash_value, executions,
       ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1000, 2) AS ms_per_exec,
       SUBSTR(sql_text, 1, 120) AS sql_preview
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+ syntax; on 11g use WHERE ROWNUM <= 20

-- SQL with multiple plan hashes (plan instability)
SELECT sql_id, COUNT(DISTINCT plan_hash_value) AS plan_count
FROM v$sql
GROUP BY sql_id
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC;

-- Full scan vs index read wait events
SELECT event, total_waits, time_waited_micro,
       ROUND(time_waited_micro / NULLIF(total_waits, 0) / 1000, 2) AS avg_ms
FROM v$system_event
WHERE event IN ('db file scattered read', 'db file sequential read',
                'direct path read', 'direct path read temp');

-- Dominant wait class among active sessions (fastest triage view)
SELECT NVL(wait_class, 'ON CPU') AS wait_class, COUNT(*) AS sessions
FROM v$session
WHERE status = 'ACTIVE' AND type = 'USER' AND wait_class != 'Idle'
GROUP BY NVL(wait_class, 'ON CPU')
ORDER BY COUNT(*) DESC;

-- When statistics were last gathered on suspect tables
SELECT owner, table_name, last_analyzed, num_rows
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY last_analyzed DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+ syntax; on 11g use WHERE ROWNUM <= 20

How to diagnose

Confirm the spike is a rate change, not a cumulative counter artifact. V$SYSSTAT counters are cumulative since instance startup. Sample twice, compute the delta, divide by the interval. Compare the rate against a 7-day baseline by hour of day.
Check physical reads alongside logical reads. If physical reads are flat while logical reads spike, the working set is cached but the workload got heavier: the classic plan regression signature. If physical reads are also spiking, the system is pulling cold blocks from disk.
Find the top SQL by buffer gets per execution, not just total gets. A batch job doing 1 billion gets in one execution dominates total gets, but a plan regression on a query doing 1 million gets per exec at 100 execs/sec is more damaging system-wide. Sort the V$SQL query by gets_per_exec and look for values far above historical baseline.
Check whether the plan hash changed. Query V$SQL for the suspect SQL_ID across child cursors. If you see the same SQL_ID with an old plan hash (low gets/exec) and a new plan hash (high gets/exec), plan regression is confirmed.
Correlate with statistics gathering. Check DBA_TABLES.LAST_ANALYZED for the tables in the regressed SQL. If the timestamp is minutes before the spike, the stats gather triggered the regression.
Generate and compare both execution plans. The most common regression is a full table scan replacing an index range scan, or a hash join replacing a nested loops join. Look at estimated vs actual cardinality in V$SQL_PLAN_STATISTICS_ALL if available (requires STATISTICS_LEVEL = ALL, which adds parsing overhead; do not leave it on permanently in production).

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`session logical reads` rate	Broadest measure of read workload. Sudden 2x spike from baseline is the primary symptom.	Sustained more than 2x the 7-day rolling baseline for the same hour.
`buffer_gets / executions` per SQL_ID	Per-execution cost. A change here means the plan changed, not the call rate.	More than 10x increase from baseline for any high-frequency SQL.
`PLAN_HASH_VALUE` count per SQL_ID	Multiple plans for the same SQL text indicate instability.	Any high-frequency SQL_ID with more than 2 distinct plan hashes.
`db file scattered read` wait time	Full scan wait. Spike confirms scans replacing index reads.	Dominating wait time on an OLTP system.
`physical reads` rate	Whether the working set still fits in cache.	Rising in lockstep with logical reads (cold block flood).
CPU utilization	Logical reads consume CPU for latch acquisition and block traversal.	Saturating alongside logical reads spike, with no I/O bottleneck.
Active sessions vs CPU cores	Plan regression causes sessions to pile up as each execution takes longer.	Active sessions sustained above 2x CPU core count.

Fixes

Stabilize immediately: load the known-good plan

If the old (good) plan hash value is still in the cursor cache, load it as a SQL Plan Baseline. This pins the known-good plan and prevents the optimizer from using the regressed plan.

-- Load the known-good plan from cursor cache
DECLARE
  v_plans_loaded PLS_INTEGER;
BEGIN
  v_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&good_sql_id',
    plan_hash_value => &good_plan_hash
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || v_plans_loaded);
END;
/

Verify the baseline exists and is accepted:

SELECT sql_handle, plan_name, enabled, accepted, origin
FROM dba_sql_plan_baselines
WHERE parsing_schema_name = '&schema';

SQL Plan Baselines are persistent across restarts. Once accepted, the optimizer will only choose from accepted baselines for that SQL text.

When the good plan is no longer in cache

If the old plan has aged out (instance restart, shared pool pressure), a SQL Profile or SQL Patch can force the optimizer toward a specific plan. SQL Profiles require the Tuning Pack license. SQL Patches are applied via DBMS_SQLDIAG. Both are stopgaps until you restore the good plan or fix the root cause.

Fix the root cause

After stabilizing, investigate why the optimizer chose the bad plan:

Stale or skewed statistics. Check DBA_TABLES.NUM_ROWS and DBA_INDEXES.CLUSTERING_FACTOR against the actual data volume. Re-gather statistics with appropriate method options if histograms are needed for skewed columns.
Histogram changes. A gather job that added or removed a histogram on a skewed column can flip the plan. Check DBA_TAB_COL_STATISTICS.HISTOGRAM.
Index visibility. An index made invisible or dropped will force full scans. Check DBA_INDEXES.VISIBILITY.
Parameter changes. OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING, or DB_FILE_MULTIBLOCK_READ_COUNT changes make full scans look cheaper. Check V$SYSTEM_PARAMETER for recent changes.

Do NOT flush the shared pool. ALTER SYSTEM FLUSH SHARED_POOL causes a hard parse storm and makes the situation worse, not better.

Prevention

Use SQL Plan Baselines proactively. Without baselines, plan regressions are a matter of when, not if. DBMS_SPM lets you capture and evolve accepted plans. Once a baseline exists, the optimizer will only use accepted plans, and new plans must be verified before use. This is the single most effective preventative measure.

Monitor buffer_gets per execution for top SQL. Track BUFFER_GETS / EXECUTIONS for your top 20 SQL_IDs and alert on changes greater than 10x. This catches regressions before they become production incidents. This is one of the most underdeployed monitoring signals in Oracle environments.

Stage statistics gathering. The default auto-gather job runs during maintenance windows, which often overlap with batch or ETL windows. On critical tables, consider gathering statistics with PUBLISH => FALSE (pending stats), comparing pending against published with DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING, and publishing only if plans are stable.

On Oracle 23ai, Automatic SQL Plan Management compares new plans against a reference plan at hard parse time and can create a baseline automatically if the new plan performs worse. This real-time SPM behavior is governed by DBA_SQL_MANAGEMENT_CONFIG.AUTO_SPM_EVOLVE_TASK (set with DBMS_SPM.CONFIGURE). On 19c and earlier, regression detection is a background task, and a bad plan can execute before SPM reacts. Baselines captured proactively remain essential on 19c and earlier.

How Netdata helps

Per-second logical reads rate from V$SYSSTAT shows spike onset within seconds and compares against a multi-day baseline by hour of day.
Correlate logical reads with physical reads and CPU in the same dashboard. If logical reads spike while physical reads stay flat and CPU saturates, the pattern points to a cached-workload regression, not an I/O problem.
Wait event breakdown (db file scattered read, db file sequential read, direct path read) alongside logical reads confirms whether full scans are driving the spike.
Active sessions vs CPU cores shows the saturation cascade: plan regression causes sessions to pile up as each execution takes longer.
Anomaly detection on the logical reads rate flags a 2x deviation from baseline automatically, even without a static threshold. Baseline-relative alerting is essential for workload-dependent metrics like this one.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

The Netdata solution

Oracle Database monitoring with Netdata

Netdata monitors Oracle Database with per-second metrics and automatic dashboards. Watch wait events, redo and archive-log activity, tablespace and undo space, and session and lock activity so the failure modes in these runbooks surface before the instance hangs.

See Oracle monitoring → Start monitoring free