Oracle DBA Lesson 102 Part 1 — Run a bounded SQL tuning task

SQL Tuning Advisor analyzes a statement and explains possible improvements. A useful investigation starts with an actual performance question: which statement is expensive, under which inputs, and according to which measurements? An advisor report adds evidence to that investigation. A completed analysis is not a reason to implement every recommendation.

Oracle DBA Lesson 102 Part 1 — Run a bounded SQL tuning task

This lesson follows one order query through create → execute → report, with a specified user context, an unused task name, and a 30-second tuning-session limit. A recommendation and a measured improvement are different results. A proposed benefit still needs a representative workload test.

These commands target Oracle Database 19c. SQL Tuning Advisor is a Tuning Pack feature, and Diagnostics Pack is a prerequisite for Tuning Pack. An installed package or an enabled CONTROL_MANAGEMENT_PACK_ACCESS setting does not establish entitlement. The task owner needs the ADVISOR privilege and permission to run the package. Without that entitlement, use the cursor measurement below and omit every DBMS_SQLTUNE call. See Licensing Information.

Establish the baseline

SELECT order_id, amount
FROM orders
WHERE customer_id = 501;

The predicate selects orders for one customer. The projected columns are the order identifier and amount. Save elapsed time, logical reads, the actual execution plan, row count, data and statistics context, and relevant session settings. Fetch all result rows consistently. Record the input values and repeat the measurement under comparable conditions. A tiny table may be fast enough to provide little useful tuning advice. That is a valid result.

For an actual-cursor measurement, collect execution statistics for the statement, then display its cursor immediately:

SET TIMING ON
SELECT /*+ GATHER_PLAN_STATISTICS */ order_id, amount
FROM orders
WHERE customer_id = 501;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

The hint requests plan execution statistics. ALLSTATS LAST reports available statistics for the last execution rather than cumulative plan totals. Compare estimated and actual rows, starts, and buffers at the relevant operations. SQL*Plus elapsed time also includes client and fetch effects. If another statement intervenes, identify the exact SQL ID and child cursor and pass them explicitly. The caller needs access to the fixed views used by DISPLAY_CURSOR, including V$SQL, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS_ALL. A missing-statistics or access error is not a performance result.

The instrumented statement contains a hint, so record that text difference when comparing it with the unhinted advisor input. For an application statement that uses binds, retain representative bind types and values. The SQL-text overload supports bind_list. This literal-value statement does not demonstrate bind-sensitive workload coverage.

See DBMS_XPLAN.

Create one bounded task

Check that the chosen name is unused for the current task owner:

SELECT task_name, status
FROM user_advisor_tasks
WHERE task_name = 'TUNE_ORDERS_01';

No row means this owner has no task with that name. The name is unique to its owner. A returned row means choose another unused name, or deliberately reuse an existing task under its documented lifecycle. Do not drop an unrelated task to free the name.

VARIABLE task_name VARCHAR2(128)
BEGIN
  :task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT order_id, amount FROM orders ' ||
                'WHERE customer_id = 501',
    user_name => 'APP_USER',
    scope => 'COMPREHENSIVE',
    time_limit => 30,
    task_name => 'TUNE_ORDERS_01');
END;
/

CREATE_TUNING_TASK returns the task name, retained in the SQL*Plus bind variable :task_name. Creating the task records its input and settings. Execute it separately to perform the analysis.

• sql_text contains one statement. The concatenated strings preserve a space between the table name and WHERE.
• user_name identifies the user for whom the statement is tuned. This context affects name resolution and object access. It does not switch the interactive connection to that account. APP_USER must resolve and access ORDERS.
• COMPREHENSIVE includes the analysis available under LIMITED plus SQL profiling. LIMITED excludes SQL profile recommendations.
• time_limit => 30 specifies the maximum tuning-session duration in seconds. It is an analysis budget, not a query-response target, a promise of a finding, or the length of this lesson. Interpret completion information and messages from the actual run.
• The unused task name provides a stable identity for execution, status inspection, report retrieval, and later cleanup. The task owner and the statement's user context can differ. Retain both.

Analysis can use sampling and partial statement execution. Budget resources and choose the statement carefully. This task is local and invoked manually. Automated tuning and Active Data Guard remote tuning have additional behavior.

See SQL Tuning Advisor and DBMS_SQLTUNE.

Execute and inspect the status

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:task_name);

SELECT status, status_message, error_message
FROM user_advisor_tasks
WHERE task_name = :task_name;

Run these in the same SQL*Plus session so the bind variable remains populated. EXECUTE_TUNING_TASK performs a new analysis execution for the previously created task. USER_ADVISOR_TASKS shows the current user's tasks. The name filter keeps the interpretation attached to this task.

Status	Interpretation
INITIAL	The task is in its initial state; no recommendations are present.
EXECUTING	Analysis is running.
COMPLETED	The analysis operation finished successfully. Read the report to learn what it found.
INTERRUPTED	Analysis was interrupted; available recommendation data can still be reported.
ERROR	Analysis encountered an error; inspect the messages and any available findings.

Do not infer a particular status solely from the configured time limit. Check the actual status, messages, and report. No matching task row, an access error, an incomplete analysis, and a completed analysis with no findings are different observations.

See USER_ADVISOR_TASKS and DBA_ADVISOR_TASKS.

Read and preserve the report

SET LONG 100000
SET LINESIZE 180
SET PAGESIZE 50000
SPOOL tune_orders_01_report.txt
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task_name)
FROM dual;
SPOOL OFF

The function returns a CLOB report. By default it targets the most recent execution of the task. SET LONG raises the SQL*Plus display limit to 100,000 bytes. If the report exceeds that limit, increase it and retrieve the report again. A truncated display is not a complete report. Spooling saves what SQL*Plus displays, so confirm completeness before treating the file as retained evidence. Restore changed client settings afterward if needed.

First establish the run's identity: statement, user and schema context, task owner, analysis scope, execution, and completion information. Then read each finding's rationale, estimated benefit, and proposed action together. Depending on the statement and analysis, advice can concern statistics, access paths such as an index, SQL structure, or a SQL profile. Use only categories and recommendations actually present in the report.

A completed task with no useful recommendation is valid. It shows what this bounded analysis produced. The original workload problem may still need investigation. Preserve incomplete-analysis messages as well. Do not summarize an error as "no issue found."

See SQL*Plus SET variables.

Turn advice into a testable decision

An on-demand task can recommend a SQL profile but does not implement it automatically. Do not execute generated implementation statements as a batch merely because the task completed. Automated tuning configuration is a separate scope.

For any proposed change, identify its mechanism, the expected benefit, the representative inputs, repeated baseline and candidate measurements, possible regressions, and the exact backout. An index might improve the customer lookup while consuming storage and adding insert, update, and delete maintenance. A successful test must account for the wider workload. An estimated benefit in a report is not an observed improvement.

Profile creation, test-category activation, comparison with plan baselines, and profile backout belong to the following part. If this task produces no profile recommendation, do not invent one.

Remove only this task after the report is saved

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('TUNE_ORDERS_01');

DROP_TUNING_TASK deletes the task and all of its result data. Preserve the complete report first, then confirm the exact task name and owner. Drop only this task. Retain it when the next part needs the same task. The drop does not create or remove an index or a SQL profile, and it does not change a table, statistics, a feature setting, or any unrelated task.

Recap

SQL Tuning Advisor analyzes a statement and reports possible improvements. A completed analysis does not implement those recommendations. Follow one statement through create, execute, and report. Measure it first: elapsed time, logical reads, the actual plan, the row count, and the session settings. Fetch every result row. GATHER_PLAN_STATISTICS with ALLSTATS LAST shows statistics for the last execution, and that hinted text differs from the unhinted advisor input. For binds, keep representative types and values. The SQL-text overload accepts bind_list. Create a task only when the name is unused for the current owner. CREATE_TUNING_TASK records the input. user_name is the parsing user and does not change the connected session. COMPREHENSIVE includes LIMITED analysis plus SQL profiling. time_limit => 30 is the maximum tuning-session duration in seconds. It is not a query response-time limit or a guaranteed finding. Execute in the same SQL*Plus session, then read status, status message, and error message from USER_ADVISOR_TASKS. COMPLETED means the analysis finished. Read the report for the findings. INTERRUPTED and ERROR can still carry messages or partial recommendations. REPORT_TUNING_TASK returns a CLOB for the latest execution. Raise SET LONG above a truncated display and keep only a complete spool. Read each finding's rationale, estimated benefit, and action together, and use only categories the report actually contains. No useful recommendation is a valid result. An estimated benefit is not a measured improvement. A SQL profile recommendation is not applied automatically. Test a proposed change on a representative workload and keep a backout. DROP_TUNING_TASK deletes the task and its results, so save the report and confirm the name and owner first. Tuning Pack requires Diagnostics Pack. CONTROL_MANAGEMENT_PACK_ACCESS does not prove entitlement. Without the pack, stop at the cursor measurement and do not call DBMS_SQLTUNE.

Quiz

1. What does time_limit => 30 specify?

Every execution of the application query must finish within 30 seconds. The maximum duration in seconds for the tuning session. Thirty seconds of stored performance history. A guaranteed 30 percent improvement.

2. Why retain the same task name across the calls?

It grants object privileges to the parsing user. It automatically implements the first recommendation. It ties execution, status, report retrieval and cleanup to the intended task. It makes the task name globally unique across all owners.

3. Does a completed tuning task mean you should implement all its recommendations?

Yes, because completion includes workload approval. Yes, if the report estimates a benefit. Only when every proposed action is an index. No; each proposed change needs workload validation and a backout.

4. A bounded task produces no useful recommendation. What follows?

Record that outcome and continue investigating the original problem with the measurements. The application has no performance issue. Create a profile even though none was recommended. Remove the analysis limit and immediately run against production.

5. What should happen before dropping the exercise task?

Drop all SQL profiles in the database. Change the global optimizer settings. Preserve the complete report and verify the exact task identity and owner. Delete the baseline measurements.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily