Oracle SQL Tuning with AWR and ASH, Part 2: Diagnose with ASH and Runtime Plans

Oracle SQL Tuning with AWR and ASH — Part 2 of 3

Download: runnable lab scripts and raw SQL*Plus captures (ZIP).

Part 1: AWR and plan comparison · Part 3: Advisor and stabilization

Part 1 established the SQL ID, time window, and historical plan behavior. Part 2 asks a different question:

While the SQL was active, what was the database doing?

ASH classifies the time. Runtime plans explain the work that produced it.

Licensing: V$ACTIVE_SESSION_HISTORY, historical ASH, and ASH reports require Diagnostics Pack. Real-Time SQL Monitoring requires Tuning Pack.

1. Inspect the Reproduced Execution

After running Part 1’s lab scripts, execute:

SQL> @oracle-sql-tuning-series/lab/03-inspect-bad.sql

The script reads the real SQL ID from DBC_LAB_RUN_LOG and prints:

• cursor statistics;
• the actual cursor plan;
• recent ASH samples; and
• the most recent SQL Monitor report.

Because the lab statement is tagged with DBACORNER_LAB_BAD, it can be found without guessing its SQL ID.

2. Separate CPU from Waits

DBA_HIST_ACTIVE_SESS_HISTORY uses the column EVENT, not WAIT_EVENT. For ON-CPU samples, EVENT is null, so label CPU explicitly:

DEFINE begin_time = '2026-08-04 01:00'
DEFINE end_time   = '2026-08-04 02:00'

SELECT sql_plan_hash_value,
       CASE
         WHEN session_state = 'ON CPU' THEN 'ON CPU'
         ELSE event
       END AS activity,
       COUNT(*) AS archived_samples,
       ROUND(SUM(usecs_per_row) / 1e6, 1) AS estimated_db_seconds
FROM   dba_hist_active_sess_history
WHERE  sql_id = '&sql_id'
AND    sample_time BETWEEN TO_TIMESTAMP('&begin_time', 'YYYY-MM-DD HH24:MI')
                       AND TO_TIMESTAMP('&end_time', 'YYYY-MM-DD HH24:MI')
GROUP BY sql_plan_hash_value,
         CASE
           WHEN session_state = 'ON CPU' THEN 'ON CPU'
           ELSE event
         END
ORDER BY estimated_db_seconds DESC;

Always include the incident window. Without it, activity from unrelated executions and plans is mixed together.

ASH is sampled evidence. V$ACTIVE_SESSION_HISTORY normally samples each active session once per second. Roughly one sample in ten is retained in DBA_HIST_ACTIVE_SESS_HISTORY, so a historical row commonly represents about ten seconds of database time; USECS_PER_ROW is the better weight when available. For a short or very recent lab run, use V$ACTIVE_SESSION_HISTORY, as lab/03-inspect-bad.sql does. Parallel execution can contribute several active sessions simultaneously, so ASH database time can be much greater than wall-clock time.

3. Map Activity to Plan Operations

SELECT sql_plan_hash_value,
       sql_plan_line_id,
       sql_plan_operation,
       sql_plan_options,
       CASE
         WHEN session_state = 'ON CPU' THEN 'ON CPU'
         ELSE event
       END AS activity,
       COUNT(*) AS archived_samples,
       ROUND(SUM(usecs_per_row) / 1e6, 1) AS estimated_db_seconds
FROM   dba_hist_active_sess_history
WHERE  sql_id = '&sql_id'
AND    sample_time BETWEEN TO_TIMESTAMP('&begin_time', 'YYYY-MM-DD HH24:MI')
                       AND TO_TIMESTAMP('&end_time', 'YYYY-MM-DD HH24:MI')
GROUP BY sql_plan_hash_value,
         sql_plan_line_id,
         sql_plan_operation,
         sql_plan_options,
         CASE
           WHEN session_state = 'ON CPU' THEN 'ON CPU'
           ELSE event
         END
ORDER BY estimated_db_seconds DESC;

A hot plan line is a location, not a verdict. A full table scan can be correct for a large result. An index lookup can be disastrous if it performs millions of random table accesses.

4. Investigate Blocking as a Separate Failure Mode

SELECT blocking_inst_id,
       blocking_session,
       event,
       COUNT(*) AS archived_samples,
       ROUND(SUM(usecs_per_row) / 1e6, 1) AS estimated_db_seconds
FROM   dba_hist_active_sess_history
WHERE  sql_id = '&sql_id'
AND    blocking_session IS NOT NULL
AND    sample_time BETWEEN TO_TIMESTAMP('&begin_time', 'YYYY-MM-DD HH24:MI')
                       AND TO_TIMESTAMP('&end_time', 'YYYY-MM-DD HH24:MI')
GROUP BY blocking_inst_id, blocking_session, event
ORDER BY estimated_db_seconds DESC;

Do not kill a session based only on this aggregation. Session IDs are reused. Correlate the blocker with instance, serial number, sample time, application identity, and transaction context.

If application or enqueue waits dominate, SQL text tuning may not solve the incident. The correction may belong in transaction scope, commit behavior, row-access order, or application concurrency.

5. Validate Actual Row-Source Statistics

An explain plan is a prediction. When runtime statistics were collected, use the executed cursor:

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           '&sql_id',
           NULL,
           'ALLSTATS LAST +PEEKED_BINDS'
         )
       );

Check:

• estimated rows versus actual rows;
• starts per operation;
• rows discarded by late filters;
• buffer gets and physical reads;
• TEMP spills;
• partition pruning;
• unexpected Cartesian joins;
• peeked bind values;
• child cursors using different plans.

ALLSTATS LAST only works when row-source statistics were collected. Use /*+ GATHER_PLAN_STATISTICS */ for a controlled execution. Do not enable STATISTICS_LEVEL=ALL system-wide merely to inspect one query.

6. Use SQL Monitor for One Expensive Execution

SQL Monitor is usually the clearest evidence for long-running or parallel SQL. It exposes elapsed time, waits, row flow, parallel distribution, and per-operation resource consumption.

SQL is normally monitored when it:

• runs in parallel;
• consumes at least five seconds of CPU or I/O in one execution; or
• includes the /*+ MONITOR */ hint.

Generate a text report:

SET LONG 1000000 LONGCHUNKSIZE 1000000 PAGESIZE 0

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id       => '&sql_id',
         type         => 'TEXT',
         report_level => 'ALL'
       )
FROM   dual;

For repeated executions, identify SQL_EXEC_ID and SQL_EXEC_START so the report targets the correct run rather than whichever monitored execution Oracle selects by default.

7. Convert Evidence into a Root-Cause Hypothesis

For the lab, the full scan is intentionally imposed on a selective predicate. The runtime plan should show that the query visits the large orders table for a small result. In production, prove the same imbalance before deciding that an index is appropriate.

What the Captured Table Lab Showed

The 75-execution SQL*Plus run lasted 3.700 seconds. Recent V$ACTIVE_SESSION_HISTORY contained four samples, all ON CPU at the full-scan operation:

ON CPU | plan line 2 | TABLE ACCESS FULL | 4 samples

The actual plan confirmed 9,688 buffers for one execution to return 48 matching rows. SQL Monitor identified sqlplus.exe, the lab module/action, the same full-scan line, and about 0.051 seconds elapsed for the monitored execution.

After adding the (CUSTOMER_ID, STATUS) index, the actual plan changed to INDEX RANGE SCAN and used three buffers. AWR elapsed time fell from 49.148 ms to 0.168 ms per execution. ASH located the active operation; ALLSTATS LAST quantified its work. See the full captured plans and measurements.

8. The Performance Hub Route

OCI Performance Hub presents the same workflow visually:

1. set the exact time range and time zone;
2. filter ASH Analytics by SQL ID;
3. group by wait class or event;
4. break down by plan hash, session, service, or plan operation;
5. open SQL Monitoring for the individual execution;
6. use Blocking Sessions if concurrency dominates.

The UI does not change the reasoning. It reduces the amount of SQL needed to reach the same evidence.

What Part 2 Should Conclude

State the root cause as an evidence-backed sentence:

During the incident window, SQL <SQL_ID> used plan <PHV>. Most database time accumulated on <activity> at plan operation <line>. Runtime statistics showed <specific mismatch or excess work>.

That statement is strong enough to test a correction. “The plan contains a full scan” is not.

Next

Part 3 runs sqltrpt.sql, compares it with newer reporting and UI options, applies the lab index, and verifies the before/after result.

References

• Oracle: DBA_HIST_ACTIVE_SESS_HISTORY
• Oracle: DBMS_XPLAN
• Oracle: Real-Time SQL Monitoring
• Oracle: OCI Performance Hub Features