Scops
Docs Download

Oracle problems

One SQL Taking the Whole Database: Find It, and the Fix

3 November 2026

When a database is slow for everyone at once, one statement is usually to blame: a report with a plan that scans a billion rows, a statement that is cheap but runs ten thousand times a minute, or a job that was fine until the table doubled. The sessions are in V$SESSION right now, and Oracle keeps the totals per statement in V$SQL, which its reference says is normally updated at the end of query execution but, for long running queries, every 5 seconds. Three queries find the statement, say whether it is one session or a hundred, and show where its time goes, without the Diagnostics Pack.

Right now, by active sessions

SELECT sql_id, COUNT(*) AS active_sessions,
       SUM(CASE WHEN state = 'WAITING' THEN 0 ELSE 1 END) AS on_cpu,
       MIN(sql_exec_start) AS running_since,
       MIN(username) AS a_user, MIN(module) AS a_module
FROM   v$session
WHERE  status = 'ACTIVE' AND type = 'USER' AND sql_id IS NOT NULL
GROUP  BY sql_id
ORDER  BY active_sessions DESC;

SQL_ID is, in Oracle's words, the SQL identifier of the SQL statement that is currently being executed, and SQL_EXEC_START the time when its execution started. One SQL_ID holding half of the active sessions is the statement; whether it is one session that has run since nine or sixty sessions that each started a second ago is the first thing the fix depends on. Run twice a minute apart, because a single reading catches whatever happened to be running.

The totals, by statement

SELECT sql_id, plan_hash_value, executions, users_executing,
       ROUND(cpu_time/1e6)     AS cpu_s,
       ROUND(elapsed_time/1e6) AS ela_s,
       buffer_gets, disk_reads,
       ROUND(buffer_gets/NULLIF(executions,0)) AS gets_per_exec,
       parsing_schema_name, module
FROM   v$sql
WHERE  last_active_time > SYSDATE - 1/24
ORDER  BY cpu_time DESC
FETCH  FIRST 10 ROWS ONLY;

Oracle's reference: CPU_TIME is the CPU time in microseconds used by this cursor for parsing, executing and fetching, ELAPSED_TIME the elapsed database time, which for a parallel statement is the coordinator plus all its workers, BUFFER_GETS the buffer gets and DISK_READS the disk reads for the child, and USERS_EXECUTING the number of users executing the statement. The figures are totals since the cursor was loaded, so two readings ten minutes apart, subtracted, are the last ten minutes; order by disk_reads when the host is waiting on I/O rather than CPU. V$SQLSTATS has the same columns, one row per SQL_ID, and Oracle's reference notes it is faster, more scalable, and has a greater data retention, so the statement is still there after its cursor has aged out.

What it is doing, and what it waits on

SELECT event, state, COUNT(*) AS sessions,
       MAX(last_call_et)     AS longest_s,
       MAX(blocking_session) AS blocker
FROM   v$session
WHERE  sql_id = '&sql_id' AND status = 'ACTIVE'
GROUP  BY event, state
ORDER  BY sessions DESC;

Sessions on CPU are the plan's work; sessions on db file scattered read or direct path read are a full scan; on db file sequential read, an index that fetches too many rows; on enq: TX - row lock contention, a blocker and not the statement at all. The plan itself, with the operation Oracle chose for each table:

SELECT id, operation, options, object_name, cardinality, cost
FROM   v$sql_plan
WHERE  sql_id = '&sql_id' AND child_number = 0
ORDER  BY id;

A TABLE ACCESS FULL on the largest table with a cardinality in the millions, under a NESTED LOOPS, is the usual shape of a statement that takes a database. If V$SQL shows two PLAN_HASH_VALUEs for the statement, the plan changed, and that guide is the next page.

The fix, in order

  1. Decide between the session and the statement. One session, running since nine, with a plan that will take hours: ALTER SYSTEM KILL SESSION ends it now, and the fix happens before the rerun. Sixty sessions each running a 20 ms statement: nothing to kill, the load is the application's call rate, and the fix is in the next two steps.
  2. Fix the plan when the gets per execution are the problem. Thousands of buffer gets for one row returned is a missing or wrong index, a predicate that cannot use one, or stale statistics; the plan above names the table. A hint, an index, fresh statistics, or on Enterprise Edition a SQL plan baseline for the good plan.
  3. Cut the executions when the statement is cheap. A statement run a million times an hour from a loop is fixed in the loop: one query for the set instead of one per row, a cache in the application, or a join that replaces the lookup. The database cannot make a million calls cheaper than the round trip.
  4. Cap the group that runs reports. The Resource Manager gives the reporting consumer group a share of the CPU and cancels or switches a statement whose estimated run time passes a limit, so one report never takes the OLTP sessions with it again.
  5. Move it. The nightly job that takes the database at 08:00 because it started late is scheduled, not tuned.

See it coming

Radar Engine, the detection layer of Scops, samples every session every 5 seconds and knows what share of the active sessions each statement usually holds at this hour, so a report that takes half the database at ten in the morning is a finding in its first minutes, with the statement, the sessions and the plan, while a batch that does the same every night at two is known and quiet:

DetectorClockWhen it fires
SQL taking most of the loadLive. Half or more of the active sessions.Above the usual of this hour: Warning. More sessions than CPUs: Critical. Always so at this hour: Watch.
CPU saturatedLive. Sessions on CPU or in the queue, against the usable CPUs.As many as the CPUs: Critical. Rising toward them: Warning, with an ETA.

The finding names the statement, its share of the sessions, the user and module that run it, and whether its plan changed. The Activity chart shows the same statement by session over the day, on any edition, with no Diagnostics Pack: Scops keeps its own session history, sampled every 5 seconds.

Questions

How do I find the SQL that is consuming my Oracle database right now?
Group V$SESSION by SQL_ID for active user sessions; the statement with the most sessions is the one. V$SQL ordered by CPU_TIME or DISK_READS for cursors active in the last hour gives the totals and the gets per execution.

What is the difference between V$SQL and V$SQLSTATS?
V$SQL has one row per child cursor and is updated at the end of execution, or every 5 seconds for long-running statements. V$SQLSTATS has one row per SQL_ID, is faster, and keeps the statistics after the cursor has aged out of the shared pool.

Do I need the Diagnostics Pack for this?
No. V$SESSION, V$SQL, V$SQLSTATS and V$SQL_PLAN are free on every edition. The history views DBA_HIST_* and V$ACTIVE_SESSION_HISTORY belong to the pack; Scops keeps its own history instead.

Should I kill the session?
When it is one session with a plan that will run for hours and a rerun can wait for the fix, yes. When the load is many sessions running a cheap statement often, there is nothing to kill; the fix is in the application's call rate or the Resource Manager.

Related problems

Sources