Oracle slow query diagnosis: top SQL by buffer gets and elapsed time

The application is slow and you suspect one or a few SQL statements, but you do not know which. Oracle answers this precisely: rank statements in V$SQL by BUFFER_GETS (logical I/O) and ELAPSED_TIME (database time), convert cumulative counters into per-execution rates, and compare against the wait-event profile.

This is a triage article for the case where the instance is OPEN and ACTIVE, the listener responds, and the symptom is degraded query or transaction latency rather than a total hang. If every session is frozen on a single wait event, start with the blocking sessions guide instead.

The core method: rank by total work to find the heavy hitters, then rank by per-execution work to find the ones whose plan changed. Total work points at the loudest consumers. Per-execution work points at regressions.

What this means

V$SQL exposes cumulative statistics for each child cursor in the library cache. The two columns that matter most are BUFFER_GETS and ELAPSED_TIME.

BUFFER_GETS counts buffer cache block accesses (consistent gets plus current-mode gets) for this child cursor. It is logical I/O, not physical reads. A statement doing 1,000,000 buffer gets per execution is doing a lot of work whether or not it hits disk.

ELAPSED_TIME is cumulative database time in microseconds used by this cursor for parse, execute, and fetch. Two traps: the units are microseconds, so divide by 1,000,000 for seconds or 1,000 for milliseconds. And for parallel queries, ELAPSED_TIME accumulates across the query coordinator and all parallel slaves, so it can exceed wall-clock duration. A two-second parallel query at degree 8 can report 16 seconds or more of ELAPSED_TIME. Do not compare parallel ELAPSED_TIME directly against user-facing latency.

Both columns are cumulative since the cursor was loaded into the shared pool. Divide by EXECUTIONS for per-statement numbers and filter out EXECUTIONS = 0 to avoid divide-by-zero. The derived metrics are gets_per_exec = BUFFER_GETS / EXECUTIONS and ms_per_exec = ELAPSED_TIME / EXECUTIONS / 1000.

V$SQL has one row per child cursor, identified by SQL_ID plus CHILD_NUMBER. A single SQL_ID can have many child cursors from different optimizer environments, bind peeking, or adaptive cursor sharing. For one row per SQL_ID, use V$SQLAREA or V$SQLSTATS, which aggregate children. V$SQLSTATS is lighter-weight and retains statistics even after the cursor ages out of the shared pool. For plan-stability work where you need per-child PLAN_HASH_VALUE, stay with V$SQL.

Common causes

Quick checks

These are read-only. Run them as a user with SELECT on the V$ views.

-- 1. Dominant non-idle wait class right now
SELECT NVL(WAIT_CLASS, 'ON CPU') AS wait_class, COUNT(*) AS sessions
FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND TYPE = 'USER'
  AND NVL(WAIT_CLASS, 'ON CPU') != 'Idle'
GROUP BY NVL(WAIT_CLASS, 'ON CPU')
ORDER BY COUNT(*) DESC;

-- 2. Top 20 SQL by total buffer gets (logical I/O)
SELECT SQL_ID, PLAN_HASH_VALUE, EXECUTIONS,
       BUFFER_GETS / NULLIF(EXECUTIONS, 0) AS gets_per_exec,
       ELAPSED_TIME / NULLIF(EXECUTIONS, 0) / 1000 AS ms_per_exec
FROM V$SQL
WHERE EXECUTIONS > 0
ORDER BY BUFFER_GETS DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+. On 11g wrap in an outer SELECT with ROWNUM <= 20.

-- 3. Top 20 SQL by elapsed time
SELECT SQL_ID, PLAN_HASH_VALUE, EXECUTIONS,
       BUFFER_GETS / NULLIF(EXECUTIONS, 0) AS gets_per_exec,
       ELAPSED_TIME / NULLIF(EXECUTIONS, 0) / 1000 AS ms_per_exec
FROM V$SQL
WHERE EXECUTIONS > 0
ORDER BY ELAPSED_TIME DESC
FETCH FIRST 20 ROWS ONLY;

-- 4. SQL_IDs with multiple plans (plan instability candidates)
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;

-- 5. Active sessions and the SQL they are running right now
SELECT SID, SERIAL#, USERNAME, SQL_ID, SQL_EXEC_START, EVENT,
       STATE, SECONDS_IN_WAIT
FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND TYPE = 'USER'
ORDER BY SECONDS_IN_WAIT DESC;

-- 6. Long-running operations in progress.
-- Note: V$SESSION_LONGOPS only shows operations that registered with it
-- (full scans, sorts, hash joins, RMAN, etc.). Short operations do not appear.
SELECT SID, SERIAL#, OPNAME, SOFAR, TOTALWORK,
       ROUND(SOFAR / NULLIF(TOTALWORK, 0) * 100, 1) AS pct_done,
       TIME_REMAINING, SQL_ID
FROM V$SESSION_LONGOPS
WHERE SOFAR < TOTALWORK AND TIME_REMAINING > 0
ORDER BY TIME_REMAINING DESC;

How to diagnose it

flowchart TD
    A["Slowness reported"] --> B["Top non-idle wait class"]
    B --> C{"User I/O or CPU?"}
    C -->|Yes| D["Rank V$SQL by BUFFER_GETS"]
    C -->|Concurrency| E["See blocking sessions guide"]
    C -->|Commit| F["Diagnose log file sync"]
    D --> G["gets_per_exec and ms_per_exec"]
    G --> H{"High gets_per_exec?"}
    H -->|Yes| I["Check PLAN_HASH_VALUE history"]
    H -->|No| J["Volume-driven, not plan"]
    I --> K{"New vs old plan?"}
    K -->|Yes| L["Baseline the good plan"]
    K -->|No| M["Missing index or stale stats"]

1. Start with the wait-event profile. If the dominant non-idle wait class is Concurrency, the problem is locking, not slow SQL; go to the blocking sessions guide. If it is Commit, the problem is redo I/O; diagnose log file sync. If it is User I/O or the system is ON CPU, proceed.

2. Rank SQL by total BUFFER_GETS. This finds the statements doing the most logical I/O since they were loaded. Note the gets_per_exec for each.

3. Rank SQL by total ELAPSED_TIME. Cross-reference with the BUFFER_GETS list. Statements near the top of both lists are your prime suspects. Remember the parallel-query cumulative-time caveat when interpreting ELAPSED_TIME.

4. Classify each suspect as high-frequency-cheap or low-frequency-expensive. A statement with 1,000,000 executions and 5 gets_per_exec is high-frequency-cheap. Its total BUFFER_GETS is large but each call is fine; the fix is workload reduction or caching, not plan tuning. A statement with 100 executions and 500,000 gets_per_exec is low-frequency-expensive and is where plan regressions hide.

5. Check plan history for the expensive statements. Run query 4 above. If a SQL_ID has multiple PLAN_HASH_VALUES in V$SQL, pull per-plan statistics and compare gets_per_exec across them. A 10x or greater jump between plan hashes for the same SQL_ID is a plan regression.

6. Correlate with active sessions. Run query 5. V$SESSION.SQL_ID and SQL_EXEC_START tell you which statements are executing right now and how long the current execution has been running. If a SQL_ID from your top list is actively executing across many sessions, you have live impact.

7. Examine the execution plan. For the regressed SQL_ID, pull the plan from V$SQL_PLAN. Look for TABLE ACCESS FULL where an index scan used to be, MERGE JOIN CARTESIAN, or a join order that scans a large table early. Compare estimated rows versus actual rows if V$SQL_PLAN_STATISTICS_ALL is available (rowsource statistics are collected when STATISTICS_LEVEL = ALL, or per statement with the GATHER_PLAN_STATISTICS hint).

8. Confirm the trigger. Check DBA_TABLES.LAST_ANALYZED for the tables in the regressed query. A recent timestamp correlating with the onset of slowness implicates statistics gathering. Check the alert log for any DDL or parameter changes in the same window.

Metrics and signals to monitor

Fixes

Plan regression: pin the good plan

The fastest stabilizer is a SQL Plan Baseline. If the old plan is still in the cursor cache, load it directly:

-- Load a known-good plan from the cursor cache as a baseline
BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id',
    plan_hash_value => &good_plan_hash
  );
END;
/

This whitelists the plan so the optimizer will not accept a worse one without confirmation. If the old plan has aged out, a SQL Profile or SQL Patch can force the desired shape. After stabilizing, investigate the root cause: stale or misleading statistics, a histogram change, a bind-variable-peeking issue, or an optimizer parameter change. SQL Plan Baselines are the intended long-term mechanism for plan stability. Without them, regressions are a matter of when, not if.

Missing index or full scan on OLTP

If the plan is doing a TABLE ACCESS FULL on what should be an OLTP lookup, verify the index exists and is visible. Check V$SQL_PLAN for the operation. If an index was dropped or made invisible, restoring it resolves the regression. If no suitable index exists, the fix is an application or schema change, not a runtime intervention.

Statistics-driven regression

If DBMS_STATS gathering triggered the regression, you can lock statistics on the affected table to prevent re-gathering until the issue is understood, or restore prior statistics with DBMS_STATS.RESTORE_TABLE_STATS. Many organizations stage statistics gathering: gather, check for regressions, then publish only if safe. After a major version upgrade, plan regressions are common; OPTIMIZER_FEATURES_ENABLE can revert optimizer behavior temporarily.

High-frequency-cheap SQL

If the top consumers are high-frequency-cheap statements, the plan is probably fine. The problem is call volume. Options include result caching, application-side caching, reducing round trips, or moving read-heavy reporting to an Active Data Guard standby. Do not tune the plan of a statement doing 5 gets per execution.

Prevention

• Track gets_per_exec for your top SQL_IDs over time. A 10x jump is the earliest reliable signal of a plan regression, before users notice. This is the most under-deployed Oracle monitoring signal.
• Use SQL Plan Baselines for critical statements. They whitelist known-good plans and prevent the optimizer from silently accepting a worse one.
• Stage statistics gathering. Gather, verify no regressions, then publish. Lock statistics on volatile tables if gathering produces unstable plans.
• Watch DBMS_STATS timing. The auto-gather job runs in the maintenance window (default weeknight window is approximately 10pm to 2am; check DBA_SCHEDULER_WINDOWS for exact schedules). If it overlaps a batch window it both competes for resources and risks triggering regressions.
• Alert on db file scattered read dominating OLTP wait time. Full scans replacing index scans is the signature of a plan regression in flight.

How Netdata helps

• Per-second SQL and wait-event metrics show the exact moment gets_per_exec or ELAPSED_TIME spiked, and let you correlate it with statistics gathering, a deploy, or a load change.
• Top-SQL rankings alongside the wait-event profile in one view. When logical reads spike, you can immediately check whether db file scattered read or CPU is the dominant sink.
• ML anomaly detection on logical reads and active sessions catches the 2x sustained increase that precedes a user-visible plan regression.
• Trend baselines for per-execution metrics surface slow-moving regressions that static thresholds miss.
• Session-count and resource-utilization alerts catch a runaway high-frequency statement exhausting PROCESSES before it becomes ORA-00020.

For integrated Oracle monitoring, see Oracle Database monitoring with Netdata.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ‘cursor: pin S wait on X’: mutex contention on hot cursors
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle hard parse storm: literal SQL, bind variables, and shared pool churn
• Oracle ’library cache: mutex X’ waits: parsing pressure and cursor contention