Oracle SQL Tuning with AWR and ASH, Part 3: SQL Tuning Advisor, sqltrpt.sql, and Plan Stabilization

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

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

Part 1: AWR and plan comparison · Part 2: ASH and runtime plans

Parts 1 and 2 established the statement, incident interval, plan behavior, activity profile, and runtime cause. Only now is it useful to ask Oracle for a recommendation.

This is where sqltrpt.sql belongs.

Licensing: The SQL Tuning Advisor and reporting APIs used here, SQL Monitor, and sqltrpt.sql are Oracle Tuning Pack features. SQL Plan Management does not require Diagnostics or Tuning Pack, although database-edition restrictions apply. Confirm entitlement and CONTROL_MANAGEMENT_PACK_ACCESS before using pack features.

1. What sqltrpt.sql Actually Does

sqltrpt.sql is more than a formatting script. In the Oracle 21c version shipped under $ORACLE_HOME/rdbms/admin, it:

1. lists the 15 most expensive statements in V$SQLSTATS;
2. lists the 15 most expensive statements in AWR;
3. prompts for a SQL ID;
4. creates a SQL Tuning Advisor task from the cursor cache if the SQL remains there;
5. otherwise finds the minimum and maximum AWR snapshots containing the SQL and creates the task from that range;
6. executes the tuning task; and
7. displays DBMS_SQLTUNE.REPORT_TUNING_TASK.

Run it directly (? is SQL*Plus shorthand for ORACLE_HOME and works on Windows and Unix-like systems):

@?/rdbms/admin/sqltrpt.sql

Or run the series wrapper, which supplies the lab’s captured SQL ID:

SQL> @oracle-sql-tuning-series/lab/04-run-sqltrpt.sql

The shipped Oracle 21c script references &&sqlid; it does not issue an ACCEPT command. Because the wrapper defines that substitution variable first, this version does not prompt for the SQL ID again. Check a materially different Oracle release before assuming its bundled script is identical.

The advisor can report:

• missing or stale statistics;
• possible indexes;
• SQL restructuring opportunities;
• SQL Profile recommendations;
• alternative plans or SQL Plan Baseline recommendations.

Do not implement a recommendation merely because it has a large estimated benefit. Test representative binds and the wider workload.

Captured sqltrpt.sql Result

The series ran Oracle’s shipped 21c script against the table lab’s original SQL ID, bk3p70gc73zat:

Task name:          TASK_14
Scope:              COMPREHENSIVE
Completion status:  COMPLETED
Runtime:            approximately 2 seconds

Its conclusion was: There are no recommendations to improve the statement.

The measured correction nevertheless reduced buffer gets from 9,688 to 3 and AWR elapsed time from 49.148 ms to 0.168 ms per execution. This is a useful negative result: a completed advisor task can miss an important workload-specific correction. See the captured advisor report and before/after evidence in the lab bundle.

2. The Important sqltrpt.sql Limitation

When the SQL is no longer in memory, the shipped script chooses the minimum and maximum AWR snapshots containing that SQL. For a statement that has existed for months, this may analyze a much broader period than the incident.

The shortcut is appropriate when:

• you want a fast terminal workflow for one SQL ID;
• the statement is still in the cursor cache; or
• its AWR history is short and unambiguous.

Use the API when you need:

• exact AWR snapshots;
• a specific plan hash;
• a named task;
• a controlled time limit;
• repeatable automation;
• a task that can be reported or dropped later.

3. Controlled Alternative: DBMS_SQLTUNE

For a statement still in the cursor cache:

DECLARE
  l_task_name VARCHAR2(128);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                   sql_id      => '&sql_id',
                   scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
                   time_limit  => 300,
                   task_name   => 'dbc_tune_current_sql',
                   description => 'Controlled tuning task for one SQL ID'
                 );

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task_name);
END;
/

For historical SQL, use the exact incident snapshots:

DEFINE begin_snap = 3
DEFINE end_snap   = 4

DECLARE
  l_task_name VARCHAR2(128);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                   begin_snap  => TO_NUMBER('&begin_snap'),
                   end_snap    => TO_NUMBER('&end_snap'),
                   sql_id      => '&sql_id',
                   scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
                   time_limit  => 300,
                   task_name   => 'dbc_tune_incident_sql',
                   description => 'Tuning task bounded to incident snapshots'
                 );

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task_name);
END;
/

Report the result:

SET LONG 1000000 LONGCHUNKSIZE 1000000 PAGESIZE 0

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('dbc_tune_incident_sql')
FROM   dual;

Remove a disposable task when its output has been retained:

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('dbc_tune_incident_sql');

The short CREATE_TUNING_TASK(sql_id => ...) form is the cursor-cache interface. It does not automatically mean “use AWR.”

4. Modern Alternatives and Complements to sqltrpt.sql

No newer tool replaces every part of sqltrpt.sql. Choose based on whether you need diagnosis, recommendations, or runtime evidence.

DBMS_SQLTUNE.REPORT_SQL_DETAIL

This is one of the most useful modern command-line reports for a single SQL ID. It can combine statistics, ASH activity, plan timelines, execution plans, SQL Monitor output, binds, child-cursor mismatch reasons, and SPM information from memory or AWR.

SET LONG 10000000 LONGCHUNKSIZE 10000000 PAGESIZE 0
SET FEEDBACK OFF HEADING OFF ECHO OFF TRIMSPOOL ON
SPOOL sql_detail.html

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

SELECT DBMS_SQLTUNE.REPORT_SQL_DETAIL(
         sql_id       => '&sql_id',
         start_time   => TO_TIMESTAMP('&begin_time', 'YYYY-MM-DD HH24:MI'),
         end_time     => TO_TIMESTAMP('&end_time', 'YYYY-MM-DD HH24:MI'),
         report_level => 'ALL',
         type         => 'ACTIVE',
         data_source  => 'AUTO'
       )
FROM   dual;

SPOOL OFF

Use it as a consolidated diagnostic report, not as a source of advisor recommendations.

For the captured SQL ID, lab/08-run-sql-detail.sql produced an approximately 13 KB ACTIVE/ALL HTML report. It embeds the diagnostic payload for SQL ID bk3p70gc73zat, Oracle 21c, and ORCLPDB. The downloadable lab bundle includes the captured report.

The lab runner accepts the destination as its first argument:

SQL> @oracle-sql-tuning-series/lab/08-run-sql-detail.sql sql_detail.html

DBMS_SQLDIAG.REPORT_SQL on 19c RU 19.28+

Oracle 19c RU 19.28 added a deeper SQL diagnostic report containing plan history, optimizer-statistics history, non-default parameters, index information, and captured SQL Monitor reports when available.

SET LONG 10000000 LONGCHUNKSIZE 10000000 PAGESIZE 0
SET FEEDBACK OFF HEADING OFF ECHO OFF TRIMSPOOL ON
SPOOL sql_diagnostic_report.html

SELECT DBMS_SQLDIAG.REPORT_SQL(
         sql_id    => '&sql_id',
         directory => NULL,
         level     => 'ALL'
       )
FROM   dual;

SPOOL OFF

The 19.28 release note describes the deliverable as a ZIP archive containing an HTML report. The current 19c package reference documents two interfaces: with DIRECTORY => NULL, the function returns the HTML as a CLOB; with a directory object, it writes an SQLR_<SQL_ID>_<timestamp>.html file. Verify the behavior of the installed RU when automating the artifact. Treat it as a diagnostic dossier, not as a replacement for the advisor engine.

The tested 21c installation did not expose DBMS_SQLDIAG.REPORT_SQL, while it did expose DBMS_SQLTUNE.REPORT_SQL_DETAIL. Check the installed release instead of assuming that a feature backported to a newer 19c RU is also present in every intermediate database release.

OCI Performance Hub

In OCI Database Management, select a SQL ID in Performance Hub’s ASH Analytics view and choose Tune SQL. This launches SQL Tuning Advisor through a guided workflow and preserves the connection to the incident’s ASH evidence.

This is the closest UI equivalent to the sqltrpt.sql experience, with better filtering, task tracking, recommendation review, and implementation controls.

Automatic SQL Tuning Advisor

Automatic SQL Tuning Advisor runs during maintenance windows and selects qualifying high-load SQL. It is useful for continuous coverage, but it is not incident reconstruction. Review why a statement was selected, which binds and workload were represented, and whether automatic profile implementation is enabled.

5. Choose the Correction

Prefer the durable cause-level fix when possible:

• correct optimizer statistics or add needed extended statistics;
• rewrite SQL to reduce rows or repeated work;
• add a workload-appropriate index;
• correct partition pruning;
• repair transaction or concurrency behavior;
• address bind skew and child cursor behavior.

Use optimizer controls deliberately:

• SQL Profile: supplies auxiliary optimizer information. It does not freeze one execution plan.
• SQL Patch: applies hints without changing the application SQL text.
• SQL Plan Baseline: restricts the optimizer to accepted plans and protects against unverified plan changes.

An accepted baseline does not simply “force the old plan.” Oracle normally chooses among enabled, accepted plans; fixed plans have additional selection rules.

A Small, Reversible Baseline Example

Run the optional example after the lab index exists:

SQL> @oracle-sql-tuning-series/lab/07-spm-baseline-demo.sql

The script keeps the statement text—and therefore its SQL ID—unchanged while making the lab index invisible and visible to produce two plans. It then:

1. loads only the verified index plan with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;
2. queries DBA_SQL_PLAN_BASELINES;
3. reparses the statement and checks V$SQL.SQL_PLAN_BASELINE; and
4. drops the baseline with DBMS_SPM.DROP_SQL_PLAN_BASELINE.

It requires ADMINISTER SQL MANAGEMENT OBJECT. Loading from the cursor cache keeps this example independent of AWR; loading a plan from AWR has separate Diagnostics Pack implications.

On the captured run it loaded one accepted plan, showed SQL_PLAN_d8y47aqck8jka3baf76d4 in V$SQL.SQL_PLAN_BASELINE after a hard parse, then dropped that plan and verified that no baseline rows remained. See the complete SPM execution in the lab bundle.

6. Execute the Lab Correction

The lab’s selective predicate needs an index. After reviewing the advisor output, run:

SQL> @oracle-sql-tuning-series/lab/05-add-index-and-run-good.sql
SQL> @oracle-sql-tuning-series/lab/06-compare.sql

The first script:

1. creates DBC_LAB_ORDERS_CUST_ST_I on (CUSTOMER_ID, STATUS);
2. gathers table and index statistics;
3. creates a new beginning snapshot;
4. runs the improved tagged SQL 75 times;
5. creates an ending snapshot; and
6. stores the new SQL ID, plan hash, and wall-clock result.

The improved statement has a different SQL ID because its identifying comment is different. That is useful for the lab’s side-by-side comparison; production validation usually compares the same SQL ID across child cursors and plan hashes.

Use awrsqrpt.sql for both recorded snapshot ranges and compare:

• elapsed time per execution;
• CPU time per execution;
• buffer gets and reads per execution;
• rows processed;
• actual plan operations;
• ASH activity;
• application response time and throughput.

If the corrected statement is absent from the second AWR SQL report, check whether it became too cheap to qualify as top SQL. AWR’s TOPNSQL setting means absence can be expected on a busy system; fall back to V$SQLSTATS, the runtime plan, and application timings.

7. Verify Before Stabilizing

Test:

• representative selective and nonselective binds;
• realistic concurrency;
• DML cost of a new index;
• plan behavior after statistics collection;
• correctness as well as performance;
• at least one normal workload cycle.

Document how to disable or reverse every profile, patch, baseline, index, SQL rewrite, or statistics change.

If the lab is no longer needed, explicitly run:

SQL> @oracle-sql-tuning-series/lab/99-cleanup.sql

The cleanup removes only the DBC_LAB_* tables and index. It does not remove AWR snapshots or advisor history.

The Full Series in One Sentence

Define the incident, compare plans per execution, use ASH to classify the time, validate actual row-source work, ask the advisor for options, implement the smallest safe correction, and prove the result.

References

• Oracle: DBMS_SQLTUNE
• Oracle: SQL Tuning Advisor
• Oracle: Managing SQL Plan Baselines
• Oracle: OCI SQL Tuning Advisor Workflow
• Oracle: SQL Diagnostic Report in 19c RU 19.28
• Oracle: DBMS_SQLDIAG
• Oracle: Database Licensing Information