A SQL profile supplies auxiliary information for a particular SQL statement. It can correct optimizer estimates, such as the number of rows expected from an operation. The optimizer uses this information with the current environment when choosing an execution plan. A profile does not specify one immutable plan: changes in statistics, data or available indexes can affect the choice.
Oracle DBA Lesson 102 Part 2 — Test a SQL profile and preserve a backout
A SQL plan baseline serves a different purpose: it records a set of accepted plans for plan selection. The set may contain several plans. Enabled, accepted plans must be reproducible to be used; fixed-plan settings also affect selection. If no baseline plan can be reproduced, the optimizer can use its newly generated cost-based plan. Neither mechanism guarantees good performance for every workload. A profile and a baseline can coexist. See Overview of SQL Plan Management.
These commands target Oracle Database 19c. SQL Tuning Advisor and SQL profiles require the Oracle Tuning Pack, with Diagnostics Pack as a prerequisite. An installed API or an enabled management setting does not establish licensing rights. Profile management requires the ADMINISTER SQL MANAGEMENT OBJECT privilege. Querying the dictionary and cursor views requires suitable access. SQL plan baselines have their own offering and licensing conditions. Without Tuning Pack entitlement, omit every DBMS_SQLTUNE profile call and use the cursor measurements in the sections below. See Licensing Information.
Accept only the actual recommendation
Accept a profile from the retained task TUNE_ORDERS_01 only when its report recommends a SQL profile for this statement. If the report has no profile recommendation, do not create or accept one.
SELECT order_id, amount
FROM orders
WHERE customer_id = 501;Preserve the report, the original statement, the baseline measurements, and the original session category. Check that the proposed profile name is unused and that the trial category is coordinated with other administrators. The normalized statement and category also form a uniqueness key. An unused name alone does not rule out a conflict.
SHOW PARAMETER sqltune_category
BEGIN
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'TUNE_ORDERS_01',
name => 'ORDERS_PROFILE_TRIAL',
category => 'PROFILE_TRIAL',
replace => FALSE,
force_match => FALSE);
END;
/This single-statement task accepts the recommendation into the named profile. For a task that contains several statements, identify the intended report object and supply the correct object_id. If the task belongs to another owner, pass that owner with the supported task_owner argument.
replace => FALSE avoids replacing an existing profile through this call. force_match => FALSE preserves literal-sensitive matching: it does not transform literals into bind variables for matching. The profile category is separate from DEFAULT, so sessions that keep their original category do not automatically select this trial profile.
Do not change customer_id = 501 to customer_id = 702, or rewrite the predicate to use a bind, and then assume this exact profile will match. A bind-based variant needs its own retained recommendation and matching assessment. force_match => TRUE broadens matching by transforming literals under the documented rules. Mixed bind and literal statements have additional restrictions. Broadening matching is a separate design decision. It is not a shortcut for making a test appear successful.
See Managing SQL Profiles and DBMS_SQLTUNE.
Limit eligibility, then inspect actual use
In the trial session, select the trial category, then read the profile row:
ALTER SESSION SET SQLTUNE_CATEGORY = 'PROFILE_TRIAL';
SELECT name, category, status, force_matching
FROM dba_sql_profiles
WHERE name = 'ORDERS_PROFILE_TRIAL';The session category qualifies profile lookup during SQL compilation. A matching, enabled profile in the selected category is eligible. The category is not a security boundary: any other session that selects that category can also use eligible profiles. Coordinate a category with a limited, understood scope.
The dictionary query establishes identity and configuration. It does not prove that an execution used the profile. Run the intended statement through the client that will execute it, fully fetch its results, and identify its actual SQL ID and child cursor. Inspect those exact identities. Do not select an unrelated cursor with similar text:
SELECT sql_id, child_number, sql_profile, plan_hash_value
FROM v$sql
WHERE sql_id = '&target_sql_id';
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
'&target_sql_id', &target_child, 'ALLSTATS LAST'));Enter the captured SQL ID and numeric child number for the substitution variables. V$SQL.SQL_PROFILE identifies the profile used by a cursor, if any. DISPLAY_CURSOR can show a profile-use note and the actual cursor plan. ALLSTATS LAST requires execution statistics to have been collected appropriately and sufficient view access. A plan hash identifies a plan shape. It is not a performance verdict. Keep the statement text, category, actual profile identity, child cursor, and plan with the measurements.
See SQLTUNE_CATEGORY, DBA_SQL_PROFILES, V$SQL, and DBMS_XPLAN.
Decide from a representative comparison
Collect repeated baseline and trial executions under comparable data, statistics, cache, client-fetch, and concurrency conditions. Fully fetch the query each time. Record elapsed time, logical reads, and the actual plan, together with any wider workload regressions. Do not compare a partial fetch with a full fetch. Do not treat cumulative cursor totals from different numbers of executions as directly comparable per-execution measurements.
For a statement that already uses binds and has its own recommended profile, include representative bind values and selectivities. Preserve the same statement identity across the baseline and the trial. Repeat cases that matter to everyday work. Do not report only the fastest run. Check concurrency and resource tradeoffs before any wider adoption.
| Evidence to record | Decision |
|---|---|
| Statement, inputs, category and actual profile use | Confirm a valid comparison |
| Elapsed time and logical reads per complete execution | Compare repeated observations |
| Actual cursor plan and workload regressions | Keep or reject from measured evidence |
The advisor's estimated benefit is a reason to investigate. It is not an observed improvement. If the profile is unused, explain matching, compilation, and cursor evidence before interpreting performance. An index recommendation is an independent physical-design change with storage and write costs. Test one change at a time, and keep a separate backout for that change.
Back out the exact trial
To stop using the trial profile while retaining its object, disable that exact name:
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'ORDERS_PROFILE_TRIAL',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/Restore the trial session's recorded prior category. If, and only if, the recorded category was DEFAULT, restore it with:
ALTER SESSION SET SQLTUNE_CATEGORY = 'DEFAULT';Inspect the exact profile status and the subsequent cursor evidence, and remeasure performance. Do not assume the old plan will necessarily return. Disabling a profile does not cancel an execution already in progress.
When the owned trial profile is no longer needed, remove that exact name:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'ORDERS_PROFILE_TRIAL');Keep this EXEC call on one physical SQL*Plus line. Names are case sensitive. Use the stored identity. Preserve the measurements, the report, and the keep or reject decision before removing the profile. Remove only the trial object you own, unless a retention decision keeps it. Disabling or dropping a profile does not undo an index, a SQL rewrite, a statistics change, or another separate experiment. Restore those changes independently.
Recap
A SQL profile supplies auxiliary optimizer information for one statement. It can correct estimates, such as expected row counts, while the optimizer still chooses the plan from that information and the current environment. Statistics, data, or available indexes can change the choice. A profile does not store one immutable plan. A SQL plan baseline records accepted plans. Enabled, accepted plans must be reproducible, and fixed-plan settings also affect selection. If no baseline plan can be reproduced, the optimizer can use its newly generated cost-based plan. A profile and a baseline can coexist. Neither guarantees good performance for every workload.
Accept a profile from TUNE_ORDERS_01 only when the report recommends one for the statement. replace => FALSE does not replace an existing profile. force_match => FALSE keeps literal-sensitive matching. A category other than DEFAULT keeps sessions on their original category from selecting the trial automatically. An unused name is not enough: the normalized statement and category also form a uniqueness key. Changing customer_id = 501 to another literal, or to a bind, is a different statement and needs its own recommendation. force_match => TRUE is a separate decision to broaden literal matching. For several statements, pass object_id. For another owner's task, pass task_owner.
SQLTUNE_CATEGORY makes a matching, enabled profile eligible when the statement is compiled. The category is not a security boundary, and the dictionary row does not prove use. Confirm use from V$SQL.SQL_PROFILE and DISPLAY_CURSOR for the captured SQL ID and child, with ALLSTATS LAST when execution statistics were collected. A plan hash is a shape, not a performance verdict. Keep the profile only after repeated, fully fetched comparisons of elapsed time, logical reads, the actual plan, and workload effects under comparable conditions. A completed task, an enabled status, or an advisor estimate is not that evidence. Test an index or other separate change on its own, with its own backout.
To keep the object but stop using it, set STATUS to DISABLED, restore the recorded prior category, and remeasure. Use DEFAULT only when that was the recorded value. Disabling a profile does not cancel an execution already in progress, and the previous plan is not guaranteed to return. DROP_SQL_PROFILE removes the exact owned name and stays on one SQL*Plus line. Names are case sensitive. Preserve the measurements and the keep or reject decision first. Dropping or disabling the profile does not undo an index, a SQL rewrite, or a statistics change. SQL profiles require Tuning Pack, with Diagnostics Pack as a prerequisite, and profile management requires ADMINISTER SQL MANAGEMENT OBJECT.
No comments:
Post a Comment