Resources

Oracle Root Cause Analysis: From the Symptom to the Cause, Fast

4 November 2026

The root cause of an Oracle slowdown is found with five questions, always in the same order: when did it start, how much more load than usual, where did the time go, who spent it, and what changed for them. A plan, statistics, a parameter, a release, a lock or simply more data: the answer is almost always a change, and the work is to find it fast. By hand, that is an hour of queries for each incident, and three of the five steps need history that Oracle keeps only in the licensed Diagnostics Pack. Scops answers all five on every database every 5 minutes, names the cause with a grade, and Scops AI writes it up in plain words with the numbers, with no agent and no extra Oracle license.

The five questions

1. When exactly? "Slow this afternoon" is not a window. The load curve is: find the minute the active sessions left their usual level, because the cause is whatever changed just before that minute.
2. How much more than usual? Measure the load in average active sessions and compare it with the same hour on other days of the same kind. Twice the usual is an incident; the usual Monday peak is not, however high it looks.
3. Where did the time go? Split the extra load by wait class. CPU, User I/O, Application, Commit, Concurrency and Cluster point at different causes, and the largest part of the increase is the one to follow.
4. Who spent it? Find the statements, sessions, modules or jobs behind that part. Usually one statement or one blocker carries most of the increase.
5. What changed for them? A new plan, fresh statistics, a parameter, new code, a data volume, a blocking session. A cause is a change with a time, and the time must come before the start of the incident.

By hand, in SQL

The statements that spent the most in the last hour, from the shared pool, which needs no pack:

SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time / 1e6) AS elapsed_s,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1e3, 1)
                                   AS ms_per_exec,
       ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
       last_active_time
FROM   v$sql
WHERE  last_active_time > SYSDATE - 1 / 24
ORDER  BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

A statement with two rows and two plan hash values has changed its plan, and the one with the higher time per execution is the new, slow one. The totals are since the cursor was loaded, not for the hour, which is the first weakness of doing this by hand.

Statistics that were gathered just before the incident, the most common trigger of a plan change:

SELECT owner, table_name, stats_update_time
FROM   dba_tab_stats_history
WHERE  stats_update_time > SYSTIMESTAMP - INTERVAL '1' DAY
AND    owner NOT IN ('SYS', 'SYSTEM')
ORDER  BY stats_update_time DESC;

Oracle's reference describes the view in one line: it "provides a history of table statistics modifications for all tables in the database." Fresh statistics on a table, followed minutes later by a new plan on a statement that reads it, is a cause with a time.

Who blocks whom, now:

SELECT sid, serial#, username, sql_id, event,
       blocking_session, final_blocking_session,
       seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL
ORDER  BY seconds_in_wait DESC;

Then the history, and that is where the manual method breaks. The load of the same hour on other days, the wait class split of the incident minutes, the sessions that were active at 14:10, and the plans a statement used last week live in ASH and the DBA_HIST_ views. Oracle's licensing manual puts V$ACTIVE_SESSION_HISTORY and all DBA_HIST_ views, with six exceptions, in the Diagnostics Pack, an extra-cost option of Enterprise Edition that Standard Edition 2 cannot have at all. Without it, steps 2, 3 and 5 have nothing to compare with, and the incident is reconstructed from memory.

A real case: SALESPRD

One afternoon on SALESPRD, as the Scops screens show it. After statistics were gathered on SALES.ORDERS, the CustomerPortal order search, 8grtv94d850vg, moved to a new plan at 12:14, and the catalog search, gtg6un5vf1yxx, at 12:41. At 12:47 the portal's busy stretch found both, and the load doubled: 29.6 active sessions against 14.0 just before. The five questions, answered:

The important line is the timing. The plans changed at 12:14 and 12:41, but nothing hurt until 12:47, when the traffic came. A threshold on CPU or active sessions sees the problem only at 12:47 and leaves a person to find the rest. Radar Engine in Scops raised the order search's new plan as a finding by 12:24, more than 20 minutes before the busy stretch, so the cause was named before the symptom arrived.

How Radar names a cause

Radar Engine checks every database every 5 minutes and grades the cause of each finding, so the answer to question 5 comes with a measure of how sure it is:

The finding dialog shows the cause with its grade and what changed and when, the numbers against the normal, a chart that marks when it started, and the first step with its command to copy. The other findings of the same incident sit beside it, for example the slow statements behind a blocking chain, so one root cause does not arrive as ten alarms. Radar uses statistics and rules, not an AI model, so every grade can be checked against the numbers.

The write-up, from Scops AI

Ask the question your manager asks, in plain words, and Scops AI answers from Scops' own data with 40+ tools:

Every answer has the same shape: the first line gives the answer with the key number and its window, the evidence follows as charts of what the tools returned, a check that could not run shows as a gap and never as "no problem", and a recommendation carries its evidence, the check that confirms the cause and the risk of acting on it. To act, ask for the fix, and a card with the statement, the database and what Scops checked waits for your click.

See it coming

Questions

What is root cause analysis for an Oracle database?
Finding the change that made the database slow or unstable, not only the symptom. The method: fix the window, measure the load against the usual level, split it by wait class, find the statements or sessions behind the largest part, and find what changed for them.

How do I find what changed when Oracle got slow?
Look for a new plan on the top statements in V$SQL, statistics gathered just before in DBA_TAB_STATS_HISTORY, a blocking session in V$SESSION, a parameter change and a new release. The cause comes before the start of the incident.

Can I do root cause analysis without AWR or the Diagnostics Pack?
For the moment itself, yes, from the V$ views. For any past window, the history of sessions and plans is ASH and AWR, the Diagnostics Pack. Scops keeps that history itself, on Standard Edition 2 and Enterprise Edition alike, with no pack.

How long does Oracle root cause analysis take?
By hand, typically an hour per incident. In Scops, Radar checks every database every 5 minutes and names the cause with a grade, and Scops AI answers "why" in one question.

Does Scops use AI to find the cause?
Radar Engine finds and grades causes with statistics and rules, so every finding can be checked against its numbers. Scops AI, the assistant, then explains the incident in plain words and prepares the fix.

Related

• Average active sessions
• Oracle SQL plan changed
• Blocking sessions: find the root blocker
• Oracle anomaly detection
• The most capable Oracle monitoring platform
• All Oracle problems

Sources

• Oracle: Oracle Database performance method, DBA_TAB_STATS_HISTORY, V$SQL, V$SESSION, Database Licensing Information User Manual
• Scops docs: How Radar works, Findings and forecasts, Detector reference, Ask Scops AI