Oracle SQL Tuning with AWR and ASH, Part 1: Find and Prove the Regression

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

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

Part 2: ASH and runtime plans · Part 3: Advisor and stabilization

When a user reports that “the query was slow,” the first job is not to tune it. The first job is to define the incident and establish whether the SQL actually changed.

AWR is best used here as historical evidence. For one statement, it can answer:

Was this SQL significant during the reported interval?
Which execution plans ran?
Did elapsed time, CPU, logical I/O, or physical I/O change per execution?
Was the problem a regression or simply more executions and more rows?

Licensing: AWR reports and most DBA_HIST_* performance data require Oracle Diagnostics Pack. The lab’s MONITOR hint and SQL Monitor report use Tuning Pack. Confirm entitlement and CONTROL_MANAGEMENT_PACK_ACCESS before running the examples.

1. Define the Incident Before Opening AWR

Capture these inputs:

exact start and end time, with time zone;
database, PDB, and instance;
service, module, and action when available;
user-visible symptom;
SQL text or SQL_ID;
representative bind values;
a known-good comparison window.

The comparison window matters. A plan that appears expensive in total may simply have executed more often.

2. Reproduce the Series Lab

The lab creates two clearly named tables in the current schema and intentionally runs a selective lookup as repeated full table scans. Run it only in an approved non-production schema. Check the environment first:

SQL> @oracle-sql-tuning-series/lab/00-check-environment.sql
SQL> @oracle-sql-tuning-series/lab/01-setup.sql
SQL> @oracle-sql-tuning-series/lab/02-run-bad.sql

The second script:

creates a beginning AWR snapshot;
executes the tagged SQL 75 times;
creates an ending snapshot;
retrieves the real SQL_ID and plan hash from V$SQLSTATS; and
stores the run details in DBC_LAB_RUN_LOG.

The executed statement is:

select /*+ gather_plan_statistics monitor full(o)
          qb_name(DBACORNER_LAB_BAD) */
       count(*)
from   dbc_lab_orders o
where  o.customer_id = 4242
and    o.status = 'COMPLETE';

The FULL hint is deliberate. It creates a known problem for diagnosis. Do not add hints to production SQL merely to make it appear in a lab report.

Review the captured identifiers:

SELECT run_label,
       sql_id,
       plan_hash_value,
       executions,
       elapsed_seconds,
       begin_snap,
       end_snap
FROM   dbc_lab_run_log
WHERE  run_label = 'BAD';

This is an actual execution workflow: the SQL ID, snapshots, timing, and plan hash come from the database rather than being hard-coded into the article.

Captured Oracle 21c Run

The shipped table lab was executed directly through SQL*Plus 21.3 in an Oracle 21c PDB. The original run produced SQL ID bk3p70gc73zat, plan hash 2392565234, and PDB AWR snapshots 3–4.

Measurement	Captured value
Executions	75
Test-loop wall clock	3.700 s
AWR elapsed per execution	49.148 ms
AWR CPU per execution	44.970 ms
Buffer gets per execution	9,688
Disk reads per execution	0

The zero disk reads show why logical I/O matters: this cache-resident full scan was CPU and buffer-work bound, not storage bound. After Part 3’s correction, elapsed time fell to 0.168 ms and buffer gets to 3 per execution.

Review the complete captured execution and raw output in the lab bundle.

3. Find the SQL ID in a Real Incident

If the SQL is still in memory, use V$SQLAREA when filtering by application identity. Unlike V$SQLSTATS, it exposes MODULE and ACTION:

SELECT sql_id,
       plan_hash_value,
       executions,
       elapsed_time / 1e6 AS elapsed_seconds,
       module,
       action,
       substr(sql_text, 1, 100) AS sql_text
FROM   v$sqlarea
WHERE  module = '&module_name'
OR     sql_text LIKE '%&text_fragment%'
ORDER BY last_active_time DESC;

Use V$SQLSTATS when you want its lower-overhead cumulative statistics and already know the SQL ID or text. DBA_HIST_SQLSTAT retains MODULE and ACTION for historical analysis.

If the statement has aged out, use a full AWR report only to identify it. Start with:

SQL ordered by elapsed time;
SQL ordered by CPU time;
SQL ordered by gets;
SQL ordered by reads;
SQL ordered by executions.

The correct list depends on the symptom. A response-time incident and a throughput incident are not the same investigation.

4. Generate the Focused AWR SQL Report

Once the SQL ID is known, use the single-SQL report:

@?/rdbms/admin/awrsqrpt.sql

Supply:

HTML or text output;
the beginning and ending snapshot from the incident;
the SQL ID.

For the lab, the required values are recorded in DBC_LAB_RUN_LOG. The report shows SQL-level statistics and historical plans without burying the statement inside a system-wide report.

Use awrsqrpi.sql when you must explicitly choose the DBID and instance. In RAC, do not assume that an instance-local report represents the whole cluster.

5. List Historical Plans

SELECT plan_hash_value,
       timestamp AS first_seen_in_plan_history
FROM   dba_hist_sql_plan
WHERE  sql_id = '&sql_id'
AND    id = 0
ORDER BY timestamp;

The plan hash is an identifier, not a quality score. A higher or newer hash is not inherently worse.

Use the AWR SQL report or DBMS_XPLAN.DISPLAY_AWR to inspect a plan that is no longer in the cursor cache:

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_AWR(
           sql_id          => '&sql_id',
           plan_hash_value => NULL,
           db_id           => NULL,
           format          => 'TYPICAL'
         )
       );

6. Compare Plans Per Execution

Totals answer “how much did this SQL consume?” Tuning usually needs “how expensive was each execution?”

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

WITH plan_performance AS (
  SELECT s.plan_hash_value,
         SUM(s.executions_delta)     AS executions,
         SUM(s.elapsed_time_delta)   AS elapsed_us,
         SUM(s.cpu_time_delta)       AS cpu_us,
         SUM(s.buffer_gets_delta)    AS buffer_gets,
         SUM(s.disk_reads_delta)     AS disk_reads,
         SUM(s.rows_processed_delta) AS rows_processed
  FROM   dba_hist_sqlstat s
  JOIN   dba_hist_snapshot sn
    ON   sn.dbid            = s.dbid
   AND   sn.instance_number = s.instance_number
   AND   sn.snap_id         = s.snap_id
  WHERE  s.sql_id = '&sql_id'
  AND    sn.end_interval_time > TO_TIMESTAMP('&begin_time', 'YYYY-MM-DD HH24:MI')
  AND    sn.begin_interval_time < TO_TIMESTAMP('&end_time', 'YYYY-MM-DD HH24:MI')
  GROUP BY s.plan_hash_value
)
SELECT plan_hash_value,
       executions,
       ROUND(elapsed_us / NULLIF(executions, 0) / 1e6, 3)
         AS avg_elapsed_sec,
       ROUND(cpu_us / NULLIF(executions, 0) / 1e6, 3)
         AS avg_cpu_sec,
       ROUND(buffer_gets / NULLIF(executions, 0))
         AS avg_buffer_gets,
       ROUND(disk_reads / NULLIF(executions, 0))
         AS avg_disk_reads,
       ROUND(rows_processed / NULLIF(executions, 0))
         AS avg_rows
FROM   plan_performance
ORDER BY avg_elapsed_sec DESC NULLS LAST;

Repeat the query for the known-good window. In RAC or a CDB, decide whether to group or filter by INSTANCE_NUMBER and CON_ID.

AWR retains top SQL rather than guaranteeing every statement in every snapshot. After a correction, a now-cheap statement may fall below the snapshot’s TOPNSQL threshold and disappear from DBA_HIST_SQLSTAT on a busy system. That is a valid outcome; verify it in the cursor cache with V$SQLSTATS, application timings, or a controlled test instead of treating a missing AWR row as failure.

If EXECUTIONS_DELTA is zero, the execution may not have completed during the snapshot. Per-execution metrics will be null even while the SQL consumes resources. That is a signal to use ASH or SQL Monitor in Part 2.

7. Check What Changed

When a new plan is slower, inspect changes rather than stopping at the plan hash:

DBA_TAB_STATS_HISTORY shows when object statistics changed;
DBA_OPTSTAT_OPERATIONS shows statistics-gathering operations; and
DBA_HIST_PARAMETER shows initialization-parameter values captured by AWR.

Correlate these timelines with deployment, DDL, bind, and workload changes. They generate hypotheses; the per-execution and runtime evidence still decides whether a change caused the regression.

What Part 1 Should Conclude

End the AWR analysis with one of these conclusions:

Plan regression: a different plan is measurably slower per execution.
Same plan, more work: rows, binds, executions, or workload volume changed.
Same SQL, external delay: the statement spent time waiting on I/O, locks, TEMP, RAC, or resource pressure.
Insufficient evidence: the SQL was not captured or the snapshot interval is too broad.

Only the first conclusion points directly toward plan stabilization. The others require activity and runtime evidence.

Part 2 uses the same SQL ID and time window to determine where database time accumulated and whether the full scan is truly the root cause.