An automatic tuning report can describe SQL that was examined, actions that were recommended and changes that were implemented. The operational task is to establish what happened in a particular run, identify the affected object and preserve enough evidence for another administrator to review the decision, monitor its result and perform a scoped reversal when required.
Oracle DBA Lesson 103 Part 3 — Review automatic tuning and hand off evidence
These commands read existing configuration, reports, and objects. A completed execution of SYS_AUTO_SQL_TUNING_TASK must already exist. They do not accept a profile, start a tuning run, alter a task, change maintenance windows, or disable a profile.
Automatic SQL tuning and its reports require the Oracle Tuning Pack and the prerequisite Diagnostics Pack. A parameter setting, an installed package, or a visible dictionary view does not grant a licence. See Licensing Information.
Establish the outcome of each statement
Analyzed means the advisor examined a statement. Recommended means it proposed an action. Implemented means an action was applied. These outcomes need individual inspection. Analysis may produce no recommendation, and a recommendation may remain unapplied.
A SQL profile is persistent information associated with a statement. It helps the optimizer improve estimates and choose a plan. Acceptance creates the profile in the database. Its availability depends on the profile status, statement matching, and session category. An executed plan supplies evidence of actual use for that execution. A profile's existence alone cannot describe all application behavior or every later execution.
Automatic statistics collection and automatic SQL tuning have separate responsibilities. Statistics collection maintains optimizer statistics. Automatic SQL tuning examines selected SQL during maintenance activity and, when configured, can accept eligible SQL profiles. Recommendations to create an index, gather statistics, or create a plan baseline require their own implementation. Identify the recorded action before deciding which object and change need review. See SQL Tuning Advisor and automated database maintenance tasks.
Check current policy and select the recorded run
Check the identity first, then read the automatic task's profile acceptance parameter:
SHOW USER
SHOW CON_NAME
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN (
'ACCEPT_SQL_PROFILE',
'ACCEPT_SQL_PROFILES');Oracle's 19c tuning guide uses the singular spelling in prose, while its parameter examples and the DBMS_AUTO_SQLTUNE reference use the plural. Looking for both avoids silently treating a missing singular row as disabled acceptance. Record the exact returned name, value, and release. If no row appears, investigate task visibility, container, access, and the installed-release documentation. Do not infer a value from an empty result. See SQL Tuning Advisor and DBMS_AUTO_SQLTUNE.
Documentation also describes differing defaults and conditional behavior. The actual installed configuration is the starting point for present policy. If the value is AUTO, resolve its behavior against the installed-release documentation and existing profile conditions. Preserve the current setting separately from evidence of what a previous execution did. Policy may have changed since the run.
List the task's executions and choose the period relevant to the problem:
SELECT execution_name, execution_start, execution_end, status
FROM dba_advisor_executions
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
ORDER BY execution_start DESC;Each run has a distinct execution name. Retain that name, the start and end times, the status, the task owner and name, the database and container, and the release in the handoff. A failed, interrupted, or incomplete execution needs interpretation before its report can support a decision. Do not assume the most recent run is the overnight run under investigation. See DBA_ADVISOR_EXECUTIONS.
Generate and retain the report for that execution
Set :exec_name to the exact selected execution name. Replace the bracketed value before execution:
VARIABLE exec_name VARCHAR2(128)
EXEC :exec_name := '<SELECTED_EXECUTION_NAME>';
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 220
SET TRIMSPOOL ON
SPOOL automatic-tuning-review.txt
SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => :exec_name,
end_exec => :exec_name,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL')
FROM dual;
SPOOL OFFUsing the same name for both execution bounds selects a single run. Null bounds default to the most recent execution, so explicit names make a saved review reproducible. For a reviewed range, supply its actual beginning and ending execution names and record that range.
TYPICAL includes analyzed statements and recommendations that were not implemented. ALL as the level adds more detail, including skipped-statement annotations. ALL as the section selects all report sections. The two arguments have different purposes. Request a detailed report when coverage, skips, or errors matter. Inspect the summary, findings, plans, and errors as needed. A missing statement or an unsuccessful execution is not a successful acceptance check.
The older DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK call is deprecated in favor of DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK. DBMS_AUTO_SQLTUNE permits the DBA role or an explicit package EXECUTE grant. Increase the client CLOB display limit if the report exceeds it, and verify the saved ending and expected sections. A truncated spool is incomplete evidence. Keep reports under the applicable access and retention controls, because actual SQL text and object identities may be sensitive. See DBMS_AUTO_SQLTUNE and DBMS_SQLTUNE.
Connect an implemented profile to its actual identity
Obtain the exact profile name from the selected report. Supply it as a client bind and inspect the current object:
VARIABLE profile_name VARCHAR2(128)
EXEC :profile_name := '<PROFILE_NAME_FROM_REPORT>';
SELECT name, category, status, force_matching,
type, signature, created, last_modified,
task_id, task_exec_name, task_obj_id,
task_fnd_id, task_rec_id, task_con_dbid
FROM dba_sql_profiles
WHERE name = :profile_name;Use the fields for specific questions:
| Field | What to establish |
|---|---|
NAME, SIGNATURE | Which profile and normalized statement identity are involved? Retain the relevant SQL text/context through controlled evidence when needed. |
TASK_ID, TASK_EXEC_NAME | Which originating advisor task and execution generated the profile? Match these to the selected report. |
CREATED, LAST_MODIFIED | When was the current object created or changed? Compare those timestamps with the run and later change records. |
TYPE | Was the profile created manually or automatically? Correlate this with the task and execution rather than using the type alone as lineage. |
STATUS | Is the current profile enabled, disabled or void? Preserve the current observation time. |
CATEGORY | Which session category can make the profile available during compilation? |
FORCE_MATCHING | Does matching normalize literals, subject to the documented mixed-bind/literal condition? What SQL population could be affected? |
| Task object/finding/recommendation identifiers | Which precise recommendation is linked when a run contains multiple statements or findings? |
See Managing SQL profiles and DBA_SQL_PROFILES.
Resolve the originating task ID through DBA_ADVISOR_TASKS when needed:
SELECT owner, task_id, task_name
FROM dba_advisor_tasks
WHERE task_id = :selected_task_id;
SHOW PARAMETER sqltune_categoryDefine and populate the numeric :selected_task_id bind from the profile record first. SQLTUNE_CATEGORY qualifies profile lookup for the current session. A session using a different category does not establish what the application sessions use. Verify the relevant session conditions. Profile matching and category determine scope, while status records current availability. See SQLTUNE_CATEGORY.
To inspect actual use, examine the appropriate executed cursor and child with DBMS_XPLAN.DISPLAY_CURSOR, including the plan's note about the SQL profile used. Identify that cursor from the actual test. Do not assume the last cursor is the application statement after running dictionary queries. Use runtime-statistics collection when the acceptance question requires actual row and work measurements. Record execution conditions and correct results, repeat comparable measurements, and include application tests. See DBMS_XPLAN.
A report may establish historical implementation while the current object is disabled, modified, removed, or outside the reviewer's visibility. Investigate that history and visibility before concluding that the report or object is wrong. An unavailable profile needs a recorded explanation. Unrelated profiles must remain outside a scoped review.
Record the operational handoff
When the selected report records that a profile was implemented, but the change record describes only a recommendation, preserve both records and the exact object details. Escalate that identified change through the change-review process. Record the application's acceptance checks as pending until they actually pass. Do not silently disable a collection of profiles, or change the global task policy, to resolve one discrepancy.
A handoff records these items. Each item needs actual evidence:
| Handoff item | Evidence or decision to record |
|---|---|
| Workload/problem | Affected SQL and application operation, important input coverage, observed symptom and business acceptance criterion. |
| Baseline | Original configuration and comparable measurement interval, units, execution counts and correct-result check. |
| Recorded action | Exact task/execution/report, profile identity and current dictionary observation, plus the mismatch with the change record. |
| Proposed/applied change | Explicit status, scope and approval record; distinguish future work from the existing profile. |
| Test outcome | Comparable before/after metrics, critical regressions, application behavior and unresolved exceptions. Pending until demonstrated. |
| Monitoring | Named operational role, metric, threshold, review interval and escalation route. Assign the actual responsible person in the private operating record. |
| Backout | Exact target, recorded original state, approved reversal procedure and post-reversal behavior check. Pending until tested. |
| Acceptance decision | Who may accept the change, evidence still needed and the final decision with time. |
A separate profile change needs its own exact-object reversal and validation. Do not drop the system tuning task, delete tuning history, or alter scheduler or task settings as cleanup.
The acceptance goal is practical: another administrator can identify exactly what changed and reproduce the check without guessing which report, execution, statement, or profile was meant. A report's estimated tuning benefit is not an application acceptance result.
Recap
Analyzed, Recommended, and Implemented are separate outcomes. Analysis can produce no recommendation, and a recommendation can remain unapplied. A SQL profile is persistent information for a statement. Acceptance creates it. Status, statement matching, and session category control availability, and an executed plan is evidence of use for that execution. Automatic statistics collection maintains optimizer statistics. Automatic SQL tuning can accept eligible SQL profiles during maintenance. Recommendations to create an index, gather statistics, or create a plan baseline need their own implementation.
Read both ACCEPT_SQL_PROFILE and ACCEPT_SQL_PROFILES on SYS_AUTO_SQL_TUNING_TASK. The 19c tuning guide uses the singular spelling, and the parameter examples use the plural. Do not treat a missing row as disabled acceptance. If the value is AUTO, resolve it from the installed-release documentation and the existing profile conditions, and keep that current setting separate from what an earlier execution did. Select the execution by name, start, end, and status. Interpret a failed, interrupted, or incomplete run before using its report. The most recent run is not necessarily the run under review.
Call DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK with the same execution name for both bounds to save one run. Null bounds default to the most recent execution. TYPICAL includes recommendations that were not implemented. ALL as the level adds skipped-statement annotations. ALL as the section selects every report section. The older DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK interface is deprecated. A truncated spool is incomplete evidence. Match the implemented profile in DBA_SQL_PROFILES by the name from the report, and tie TASK_ID and TASK_EXEC_NAME back to that run. SQLTUNE_CATEGORY qualifies the current session only. Use DBMS_XPLAN.DISPLAY_CURSOR on the intended cursor for evidence of actual use. A report can show a historical implementation while the current profile is disabled, modified, removed, or not visible.
The handoff names the workload, baseline, recorded action, proposed or applied change, test outcome, monitoring, backout, and acceptance decision. When the report says a profile was implemented and the change record says recommendation only, preserve both and review that identified change. Do not disable every profile, or drop the system tuning task, to resolve the discrepancy. Estimated tuning benefit is not application acceptance. Another administrator should be able to reproduce the check from the named report, execution, statement, and profile.
No comments:
Post a Comment