Scops
Docs Download

Oracle problems

SQL Suddenly Slow, Plan Unchanged: The Five Causes and the Fix

3 November 2026

The statement ran in a second yesterday and takes a minute today, and the plan is the same: same PLAN_HASH_VALUE, same operations, same indexes. The plan is the recipe; what changed is the kitchen. Five things make the same plan slow: more rows under it, data that is no longer in the cache, a lock or a slower disk, a bind value for which the plan was never right, and more sessions running it at once. Each shows in a different column of V$SQL and V$SESSION, and the queries below read them in that order, without the Diagnostics Pack.

Is the plan really unchanged?

SELECT sql_id, child_number, plan_hash_value, executions,
       ROUND(elapsed_time/NULLIF(executions,0)/1000) AS ms_per_exec,
       ROUND(buffer_gets/NULLIF(executions,0))    AS gets_per_exec,
       ROUND(disk_reads/NULLIF(executions,0))     AS reads_per_exec,
       ROUND(rows_processed/NULLIF(executions,0)) AS rows_per_exec,
       is_bind_sensitive, is_bind_aware, last_active_time
FROM   v$sql
WHERE  sql_id = '&sql_id'
ORDER  BY child_number;

Oracle's reference says comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same. Two children with different values is a plan change, and that guide applies. One value, and the per-execution columns say which of the five it is: more rows per execution is the first cause, more reads per execution with the same gets is the second, time up with gets and reads unchanged is the third or the fifth, and IS_BIND_SENSITIVE at Y points at the fourth. The figures are totals since the cursor was loaded; two readings an hour apart, subtracted, are today.

Where the time goes

SELECT ROUND(elapsed_time/1e6)          AS ela_s,
       ROUND(cpu_time/1e6)              AS cpu_s,
       ROUND(user_io_wait_time/1e6)     AS io_s,
       ROUND(concurrency_wait_time/1e6) AS conc_s,
       ROUND(application_wait_time/1e6) AS lock_s,
       ROUND(plsql_exec_time/1e6)       AS plsql_s
FROM   v$sql
WHERE  sql_id = '&sql_id' AND child_number = 0;

V$SQL splits the elapsed time of a cursor into CPU, User I/O wait, Concurrency wait, Application wait and PL/SQL execution, all in microseconds. I/O that is most of the elapsed time is cause two or three; application wait is a lock, cause three; concurrency wait is a hot block or a latch, cause five; CPU that grew with the rows is cause one. The sessions running it now say the same thing live:

SELECT sid, status, event, state, last_call_et, blocking_session,
       sql_exec_start
FROM   v$session
WHERE  sql_id = '&sql_id'
ORDER  BY sql_exec_start;

The bind value it ran with

SELECT child_number, name, value_string, last_captured, was_captured
FROM   v$sql_bind_capture
WHERE  sql_id = '&sql_id'
ORDER  BY child_number, position;

Oracle's reference: a query is bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and a change in a bind variable value may cause the optimizer to generate a different plan, and bind-aware once it has been marked to use extended cursor sharing. Between those two states the same plan runs for every value, and the value for status = 'CLOSED', which is most of the table, takes a minute on the plan built for 'NEW', which is a few rows. Binds are captured at most every 15 minutes for a given cursor, so the value shown is a recent one, not necessarily the slow one.

The fix, in order

  1. More rows under the same plan. ROWS_PER_EXEC or GETS_PER_EXEC doubled: the table grew, a partition kept filling, or the predicate matches more than it did. The plan was right for the old size. Bound the work: a date predicate, a partition, an index that covers the new selectivity, or an archive of the rows the query never needs.
  2. The data left the cache. READS_PER_EXEC up, gets unchanged: the blocks are the same, they now come from disk. A larger statement flushed them, the cache shrank with an SGA resize, or a restart emptied it. The single-block read guide has the cache advice; the statement that did the flushing is the one taking the load.
  3. A lock, or a slower disk. Application wait time is a row lock; the root blocker query ends it. I/O time up with reads unchanged is the storage, not the statement: the average single-block read time in V$SYSMETRIC says whether every statement is slower today.
  4. A bind value the plan was never right for. For a bind-sensitive cursor, adaptive cursor sharing marks it bind-aware after it has seen executions with different selectivities, and then builds a second child with a plan for the other value; it needs those executions to learn. Where that is too slow or too unstable, two statements for the two cases, a literal for the skewed column, or a histogram with a plan that stays put.
  5. More sessions running it at once. Concurrency waits and sessions on the same blocks: the statement is the same, there are ten times more of them, and they queue on a hot block or a sequence. The fix is the application's call rate, or the hot block, not the plan.

See it coming

Radar Engine, the detection layer of Scops, keeps SQL statistics every 10 minutes for every database and compares each statement's time per execution with its own usual, so a statement that is three times slower is a finding in the first ten minutes, with the plan hash, the per-execution figures and the five causes already ranked:

DetectorClockWhen it fires
SQL running slowerLive. Three times slower per run.Warning. Five times, and it adds a full active session or holds two: Critical.
SQL slowing downLive. 1.5 times slower and still rising.Warning.

The finding says whether the plan changed, and when it did not, whether the rows, the reads, the waits or the sessions grew. The SQL page in Scops shows the statement's time per execution, gets and reads over the days, on any edition, with no Diagnostics Pack: Scops keeps the history itself.

Questions

Why is my Oracle query slow when the execution plan has not changed?
Because the plan is only the recipe. More rows under it, data that must now come from disk, a lock or slower storage, a bind value the plan does not fit, or many more sessions running it at once each make the same plan slow, and V$SQL's per-execution columns say which.

How do I check that the plan really did not change?
V$SQL lists every child cursor of the SQL_ID with its PLAN_HASH_VALUE. One value across the children is one plan; two values is a plan change, and the SQL plan changed guide applies.

Can a bind variable make the same plan slow?
Yes. A bind-sensitive cursor runs the plan built for the first peeked value for every value until adaptive cursor sharing marks it bind-aware. V$SQL_BIND_CAPTURE shows the values, captured at most every 15 minutes per cursor.

Do I need the Diagnostics Pack to see yesterday's figures?
Not with Scops, which keeps SQL statistics every 10 minutes itself. In Oracle alone, V$SQL holds totals since the cursor was loaded, and the history views belong to the pack.

Related problems

Sources