Introduction:
Stability in performance is a must for databases. SQL execution plans of SQL statements preferred by the optimizer can change due to various factors, such as optimizer statistics changes, database upgrades or system configuration changes. While Oracle’s Cost-Based Optimizer (CBO) typically makes intelligent decisions, there are cases where human intervention is required to preserve steady performance.
In this post, I explore three different methods for influencing SQL execution plans: SQL Plan Baselines, SQL Patches, and SQL Profiles. I will not discuss Stored Outlines, as they are a deprecated feature. Oracle strongly recommends using SQL Plan Management (SPM) for new applications. Additionally, hints can be applied directly to influence the optimizer’s decision-making process. In fact, all these methods rely on a set of hints, known as outlines, behind the scenes.
Optimizer use the SQL management base (SMB) which is a logical repository in the data dictionary to maintain or improve SQL performance.
The SMB contains the following:
- SQL statement log, which contains only SQL IDs
- SQL plan history, which includes the SQL plan baselines
- SQL patches
- SQL profiles
This post will cover their differences, use cases of choosing between them. I will demonstrate how to apply all these solutions to the same query.
To better illustrate the scenario, I will disable the Adaptive Cursor Sharing feature.
SQL> alter system set "_optimizer_adaptive_cursor_sharing"=FALSE scope=spfile;
SQL> alter system set "_optimizer_extended_cursor_sharing"=NONE scope=spfile;
SQL> alter system set "_optimizer_extended_cursor_sharing_rel"=NONE scope=spfile;Create test scenario.
-- Create the sales table
SQL> CREATE TABLE insanedba.sales (
sales_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
employee_id NUMBER(3) NOT NULL,
price NUMBER(12,2) NOT NULL
);
-- Insert 100 sales for employee_id = 1
SQL> INSERT INTO insanedba.sales (employee_id, price)
SELECT 1, ROUND(DBMS_RANDOM.VALUE(1, 1000), 2)
FROM dual CONNECT BY LEVEL <= 100;
-- Insert 500,000 sales for employee_id = 2
SQL> INSERT INTO insanedba.sales (employee_id, price)
SELECT 2, ROUND(DBMS_RANDOM.VALUE(1, 1000), 2)
FROM dual CONNECT BY LEVEL <= 500000;
-- Insert 400,000 sales for employees 3-10 with even distribution using MOD
SQL> INSERT INTO insanedba.sales (employee_id, price)
SELECT
MOD(LEVEL - 1, 18) + 3 AS employee_id, -- Employees 3-20 distributed evenly
ROUND(DBMS_RANDOM.VALUE(1, 1000), 2) AS price
FROM dual CONNECT BY LEVEL <= 400000;
-- Commit the transaction
SQL> COMMIT;
SQL> CREATE INDEX insanedba.in_sales_salesempid
ON INSANEDBA.sales (employee_id);
SQL> CREATE INDEX insanedba.in_sales_salesempidprice
ON INSANEDBA.sales (employee_id, price);
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'INSANEDBA', tabname => 'SALES');
END;--Data distribution
SQL> SELECT employee_id, COUNT (*) as sales_count
FROM insanedba.sales
GROUP BY employee_id
ORDER BY employee_id
EMPLOYEE_ID SALES_COUNT
1 100
2 500000
3 22223
4 22223
5 22223
6 22223
7 22222
8 22222
9 22222
10 22222
11 22222
12 22222
13 22222
14 22222
15 22222
16 22222
17 22222
18 22222
19 22222
20 22222SQL Plan Baselines:
What is a SQL Plan Baseline?
SQL Plan Baselines are part of the SQL Plan Management (SPM) framework. SQL Plan Baselines enforce stability by ensuring only known, verified plans are used. SQL plan baselines can be created in several ways: using a SQL Tuning Set (STS); from the cursor cache; from the AWR; exporting from one database and importing into another; and automatically for every statement. (if optimizer_capture_sql_plan_baselines parameter enabled)
How it works?
When the database performs a hard parse of a SQL statement, the optimizer generates the best-cost plan. By default, it then checks for a matching plan in the SQL plan baseline. If no baseline exists, the database executes the statement using the best-cost plan.
If a plan baseline is present, the optimizer’s behavior depends on whether the newly generated plan is part of the baseline:
- If the new plan exists in the baseline , the database executes the statement using the found plan. (starting from the fixed one with lowest cost)
- If the new plan is not in the baseline, the optimizer marks it as unaccepted and adds it to the plan history. The optimizer then determines execution based on the baseline’s contents:
- If fixed plans exist, the optimizer selects the fixed plan with the lowest cost.
- If no fixed plans exist, the optimizer chooses the baseline plan with the lowest cost from the accepted ones.
- If no reproducible plans exist (e.g., all baseline plans reference a dropped index), the optimizer falls back to using the newly generated cost-based plan.
Here’s how the optimizer behaves in this scenario:
If fixed plans exist in the baseline, the optimizer only considers fixed plans, even if a better-cost plan exists in the baseline.
Among the fixed plans, the optimizer chooses the one with the lowest cost.
Non-fixed plans are ignored, even if they might have a lower cost than the fixed plans.
The behavior of the optimizer in relation to SQL Plan Baseline states such as ‘ENABLED’, ‘ACCEPTED’, and ‘FIXED’ can sometimes be confusing. For a clear explanation with examples, I highly recommend the excellent blog post by Frank Pachot titled ‘Enabled, Accepted, Fixed SQL Plan Baselines‘.
This mechanism ensures that everything is under control of management team, preventing the optimizer from choosing an unexpected plan that could cause performance regressions.
What happens if the optimizer determines that another plan may perform better than the currently accepted plans? Is it completely ignored?
No, the optimizer does not totally ignore a newly generated plan that it believes may perform better than the current accepted plans. However, it does not use it immediately unless certain conditions are met. Here’s what happens:
The new plan is added to the SQL plan history as “unaccepted.” The optimizer does not execute the new plan unless it is manually or automatically accepted. This process is called as Plan Evolution. The optimizer continues using the existing accepted plan(s). If fixed plans exist, the optimizer only considers those fixed plans, regardless of cost.
SYS_AUTO_SPM_EVOLVE_TASK (starts with 12c) is responsible for evolving these plans into accepted ones. By default, it runs daily during the scheduled AutoTask maintenance window only for the sql statements with existing plan baselines. Oracle Database 19c introduces a new feature called Automatic SQL Plan Management (ASPM), which extends ALTERNATE_PLAN_BASELINE to AUTO (available in Enterprise Edition 19.22 and Enterprise Edition Engineered Systems 19.4), not only for statements with existing baselines but also for all SQL statements, and enables the high-frequency SYS_AUTO_SPM_EVOLVE_TASK. In Oracle Autonomous Database, it is enabled by default, whereas in on-premises environments, it is set to ‘EXISTING’ by default. Please refer to “Automatic SQL Plan Management (ASPM) hands-on” blog post to see it in action.
This is the general scope of SQL Plan Baselines. Much more information available in the related section of “Oracle Database SQL Tuning Guide 19c“. Now, I will demonstrate how we can fix a sql plan.
Fixing a SQL plan for the sample SQL statement:
First, I will query the sales table for employee_id = 2. The SQL statement peeked at bind value 2 because it was executed for the first time with a bind value that has low selectivity (0.5554938340184424), which is representing more than half of the sales. For that reason using a Full Scan access method was the best choice. The optimizer chose to use the index (IN_SALES_SALESEMPID) with the Full Index Scan access method. That is all normal up to here.
SQL> var p_emp number;
SQL> exec :p_emp := 2;
SQL> SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
COUNT(*)
----------
500000
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
------------------
SQL_ID cafx9pnjmr7v3, child number 0
-------------------------------------
SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 1181163556
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 482 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IN_SALES_SALESEMPID | 500K| 1464K| 482 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=:P_EMP)
19 rows selected.Now, I will run the same query for employee_id = 1. Only a small portion of the sales belong to employee_id = 1, so using an Index Range Scan access method would be more efficient. However, since ACS is disabled and the optimizer is not bind-aware, the plan remains unchanged. As a result, even though I am querying a small subset of the sales, it still performs an Index Full Scan. All the index blocks/leaves scanned. Fixing the plan with an Index Range Scan access method seems like a better approach.
SQL> exec :p_emp := 1;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
COUNT(*)
----------
100
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------
SQL_ID cafx9pnjmr7v3, child number 0
-------------------------------------
SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 1181163556
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 482 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IN_SALES_SALESEMPID | 500K| 1464K| 482 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=:P_EMP)
19 rows selected.For fixing a plan, I need to capture this execution plan for the SQL statement in an AWR report, the cursor (shared pool), or a staging table exported from another database.
In my case, I executed the same statement with employee_id = 1 from a different client, modifying an optimizer parameter that does not affect this query. Since the optimizer environment changed, the statement was parsed again, which was enough to generate a new execution plan with a different child cursor.
SQL> alter session set "_optimizer_cbqt_or_expansion"='OFF';
Session altered.
SQL> SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
COUNT(*)
----------
100
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID cafx9pnjmr7v3, child number 1
-------------------------------------
SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 1781642509
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| IN_SALES_SALESEMPID | 100 | 300 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:P_EMP)
19 rows selected.As a result, I now have two different plans for the same SQL statement:
One using an Index Range Scan => 1781642509
One using an Index Full Scan => 1181163556
SQL> SELECT sql_id,
plan_hash_value,
SUM (buffer_gets) / SUM (executions) AS buffer_gets_per_exec,
SUM (elapsed_time) / SUM (executions) / 1000000 AS elapsed_seconds_per_exec
FROM v$sql
WHERE sql_id = 'cafx9pnjmr7v3'
GROUP BY sql_id, plan_hash_value
HAVING SUM (executions) > 0
SQL_ID PLAN_HASH_VALUE BUFFER_GETS_PER_EXEC ELAPSED_SECONDS_PER_EXEC
--------- --------------- -------------------- ------------------------
cafx9pnjmr7v3 1781642509 7 0,001529
cafx9pnjmr7v3 1181163556 1773 0,0316385I want this statement to always execute using the Index Range Scan method. To enforce this, First i will add it to the accepted sql_plan_baselines.
-- To enable and accept the sql_plan
SQL> DECLARE
plans_loaded PLS_INTEGER;
BEGIN
plans_loaded :=
DBMS_SPM.load_plans_from_cursor_cache (
sql_id => 'cafx9pnjmr7v3',
plan_hash_value => 1781642509);
END;
SQL> SELECT sql_handle,
plan_name,
ENABLED,
ACCEPTED,
FIXED,
TO_CHAR (Created, 'DD-MON-YY') Created_Date
FROM dba_sql_plan_baselines
WHERE signature IN (SELECT exact_matching_signature
FROM v$sql
WHERE sql_id = 'cafx9pnjmr7v3')
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED CREATED_DATE
-------------------- ------------------------------ ------- -------- ------ ------------
SQL_4c986a09cb1bb71f SQL_PLAN_4t63a175jrdsz93013745 YES YES NO 24-MAR-25Now i will fix it.
SQL> DECLARE
plans_loaded PLS_INTEGER;
plans_modified PLS_INTEGER;
BEGIN
plans_loaded :=
DBMS_SPM.alter_sql_plan_baseline (
sql_handle => 'SQL_4c986a09cb1bb71f',
plan_name => 'SQL_PLAN_4t63a175jrdsz93013745',
attribute_name => 'FIXED',
attribute_value => 'YES');
plans_modified :=
DBMS_SPM.alter_sql_plan_baseline (
sql_handle => 'SQL_4c986a09cb1bb71f',
plan_name => 'SQL_PLAN_4t63a175jrdsz93013745',
attribute_name => 'AUTOPURGE',
attribute_value => 'NO');
END;
SQL> SELECT sql_handle,
plan_name,
ENABLED,
ACCEPTED,
FIXED,
TO_CHAR (Created, 'DD-MON-YY') Created_Date
FROM dba_sql_plan_baselines
WHERE signature IN (SELECT exact_matching_signature
FROM v$sql
WHERE sql_id = 'cafx9pnjmr7v3')
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED CREATED_DATE
-------------------- ------------------------------ ------- -------- ------ ------------
SQL_4c986a09cb1bb71f SQL_PLAN_4t63a175jrdsz93013745 YES YES YES 24-MAR-25A new DBMS_SPM function, ADD_VERIFIED_SQL_PLAN_BASELINE, was introduced in Oracle 23ai and has also been backported to Oracle 19c (starting with version 19.22). This function combines the “load from cursor” and “evolve” steps into a single operation. More information is available in the blog post “What is DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE?” by Nigel Bayliss.
To drop an existing sql plan baseline:
SQL> DECLARE
v_loaded PLS_INTEGER;
BEGIN
v_loaded :=
DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SQL_4c986a09cb1bb71f',
plan_name => 'SQL_PLAN_4t63a175jrdsz93013745');
END;
/I have covered the basics of SQL Plan Baselines, but there is much more to learn about them. I recommend the Oracle Optimizer Team’s series by Maria Colgan and Nigel Bayliss for more information.
- SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
- SQL Plan Management (Part 2 of 4) SPM Aware Optimizer
- SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines
- SQL Plan Management (Part 4 of 4): User Interfaces and Other Features
- How to Use SQL Plan Management
SQL Patches:
What is a SQL Patch?
A SQL Patch is a mechanism that allows us to modify the optimizer’s final execution plan decision for a SQL statement without altering the SQL code itself. It is primarily used to inject optimizer hints or address issues related to a specific SQL statement when modifying the query directly is not an option.
How it works?
SQL Patches are created using the DBMS_SQLDIAG package. They apply hints at the optimizer level, influencing how the execution plan is generated. Unlike SQL Profiles, which adjust optimizer estimates, SQL Patches explicitly modify the optimizer’s decision making process but do not force a plan directly like sql plan baselines.
When a problematic SQL statement requires specific optimizer hints for better performance, or when dealing with third-party applications where modifying the SQL code is not possible, a SQL Patch is the way to go. A great example of forcing direct path read for a query without modifying the SQL code is available in my blog post, “Optimizing Direct Path Read Decision with Table_Stats Hint“.
Adding hints via SQL Patches has been available from Oracle Database 12c Release 2, using the DBMS_SQLDIAG.CREATE_SQL_PATCH. It’s also possible to do it in 11g using an undocumented procedure called DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH. Also more information is available in MOS Note: How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1).
Up until now, I have created a test scenario and fixed the plan that uses the Index Range Scan method to access IN_SALES_SALESEMPID. This time, I will achieve the same result using a SQL Patch instead of a SQL Plan Baseline.
Patching the sample SQL statement:
SQL>DECLARE
patch_name VARCHAR2 (4000);
BEGIN
patch_name :=
DBMS_SQLDIAG.create_sql_patch (
sql_id => 'cafx9pnjmr7v3',
hint_text => q'{index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))}',
name => 'SQL_PATCH_EMPID_RANGESCAN',
validate => true
);
END;
/
SQL> SELECT name,
signature,
TO_CHAR (Created, 'DD-MON-YY') as Created_Date,
status
FROM dba_sql_patches;
NAME SIGNATURE CREATED_DATE STATUS
-------- --------------- ------------ --------
SQL_PATCH_EMPID_RANGESCAN 5519277933637383967 25-MAR-25 ENABLED
SQL> SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500)) AS my_hint
FROM XMLTABLE (
'/outline_data/hint'
PASSING (SELECT xmltype (comp_data) xml
FROM sys.sqlobj$data
WHERE signature = 5519277933637383967 and obj_type=3)) x;
MY_HINT
---------------
index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
By the way, while writing this post, my prior knowledge was that force_matching is only applicable to SQL Profiles, not SQL Patches or SQL Plan Baselines. However, while querying the DBA_SQL_PATCHES view, I noticed that there is also a FORCE_MATCHING column available for SQL Patches. Although there is no officially documented method or available procedure option in DBMS_SQLDIAG.CREATE_SQL_PATCH, there is a clever workaround. By utilizing staging tables, it is possible to apply SQL Patches with force-matching capability, as explained in Jonathan Lewis’s blog post “Force-Match Patch“
To drop an existing sql patch:
SQL> BEGIN
SYS.DBMS_SQLDIAG.drop_sql_patch (name => 'SQL_PATCH_EMPID_RANGESCAN');
END;
/SQL Profiles:
What is a SQL Profile?
A SQL Profile is a mechanism that helps the optimizer generate better execution plans by providing corrected statistical estimates. Unlike SQL Plan Baselines, which enforce specific execution plans, SQL Profiles do not fix a plan but instead improve the optimizer’s cost estimations, allowing it to make better decisions dynamically. Actually we are helping the optimizer decision process.
We can call it as a database object that contains auxiliary statistics specific to a SQL statement. Conceptually, a SQL profile is to a SQL statement what object-level statistics are to a table or index.
Using SQL Profiles, which are part of the SQL Tuning Advisor, requires a license for the Oracle Diagnostics and Tuning Pack.
How it works?
SQL Profiles are created using the SQL Tuning Advisor, Carlos Sierra’s scripts or importing custom outline data. When profiling a SQL statement, SQL Tuning Advisor uses a specific set of bind values as input. The advisor compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisor bundles corrective actions together in a SQL profile, and then recommends its acceptance. After accepting the sql profile advised, The optimizer still chooses the execution plan, but with more accurate cost calculations. The optimizer may still choose different plans since we are not forcing it. SQL Profiles do not require SQL code modifications.
If a Hint from the Profile cannot be applied, it does not invalidate the entire Profile. The CBO will still try to apply as many hints as possible. Imagine that one of the hints refers to an index, and this index is dropped, then the hint related with his index will get silently ignored. This may produce sub-optimal plans.
You may choose to use SQL Profiles in the following situations: when optimizer misestimates cause poor execution plans, when hints cannot be applied directly to a query, when you need better performance without enforcing a fixed plan, or when a query’s performance fluctuates due to changing statistics.
SQL Profiles support force matching, meaning they apply to all SQL statements that share the same text after normalizing literal values into bind variables (similar to the CURSOR_SHARING = FORCE parameter). However, SQL Plan Baselines are tied to specific SQL statements.
Method 1 : Using the SQL Tuning Advisor (By using Cloud Control or manually running the sql tuning advisor – DBMS_SQLTUNE)
SQL statement can be analyzed using the SQL Tuning Advisor through the Enterprise Manager Cloud Control Performance menu. After reviewing the recommendations and selecting the advised profile, you simply click the ‘Implement’ button to apply it and it is done.
Also SQL Tuning Advisor can be run manually using the DBMS_SQLTUNE.CREATE_TUNING_TASK and EXECUTE_TUNING_TASK procedures. Then implemented using the ACCEPT_SQL_PROFILE procedure of the same package.
First, I will flush the sample SQL statement from the shared pool. You may also use ALTER SYSTEM FLUSH SHARED_POOL to flush all SQL statements in the shared pool. However, do not use this in production environments, as it can cause significant performance degradation until all SQL statements are re-optimized.
SQL> SELECT address, hash_value FROM v$sqlarea WHERE sql_id = 'cafx9pnjmr7v3';
ADDRESS HASH_VALUE
---------- ----------
000000009CD606A8 591109987
SYS@bltdb> EXEC SYS.DBMS_SHARED_POOL.PURGE ('000000009CD606A8, 591109987', 'C');
PL/SQL procedure successfully completed.
This time, I will create a SQL Profile using SQL Tuning Advisor. First, I will execute the query with a low-selectivity bind value, causing it to run with the undesired SQL plan, Then, I will use the SQL Tuning Advisor to assist me in optimizing the query.
SQL> var p_emp number;
SQL> exec :p_emp := 2;
SQL> SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
SQL> DECLARE
stmt_task VARCHAR2 (64);
BEGIN
stmt_task :=
DBMS_SQLTUNE.create_tuning_task (
sql_id => 'cafx9pnjmr7v3',
time_limit => 3600,
task_name => 'STA_SPECIFIC_EMP_TASK',
description =>
'Task to tune a query on a specified employee');
END;
/
SQL> EXECUTE dbms_sqltune.execute_tuning_task('STA_SPECIFIC_EMP_TASK');
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(STA_SPECIFIC_EMP_TASK) FROM DUAL;Here is the report of the tuning task.
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : STA_SPECIFIC_EMP_TASK
Tuning Task Owner : INSANEDBA
Tuning Task ID : 9414
Workload Type : Single SQL Statement
Execution Count : 1
Current Execution : EXEC_10572
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status : COMPLETED
Started at : 03/25/2025 13:09:20
Completed at : 03/25/2025 13:09:22
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : cafx9pnjmr7v3
SQL Text : SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Bind Variables :
1 - (NUMBER):2
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 44.68%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'STA_SPECIFIC_EMP_TASK', task_owner => 'INSANEDBA', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .035723 .032122 10.08 %
CPU Time (s): .035598 .03201 10.07 %
User I/O Time (s): 0 0
Buffer Gets: 1772 979 44.75 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1181163556
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 482 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IN_SALES_SALESEMPID | 500K| 1464K| 482 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2- Original With Adjusted Cost
------------------------------
Plan hash value: 1181163556
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 482 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IN_SALES_SALESEMPID | 500K| 1464K| 482 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 6 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
2 - SEL$1 / SALES@SEL$1
- INDEX_FFS(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
3- Using SQL Profile
--------------------
Plan hash value: 1781642509
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 90 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | INDEX RANGE SCAN| IN_SALES_SALESEMPID | 45005 | 131K| 90 |
-------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - STATEMENT
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('8.1.7')
Note
-----
- cpu costing is off (consider enabling it)
-------------------------------------------------------------------------------
Accept the advised SQL Profile.
SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name =>'STA_SPECIFIC_EMP_TASK', task_owner => 'INSANEDBA', replace => TRUE);Method 2 : Using the Carlos Sierra’s script – coe_xfr_sql_profile.sql
This time, I will use Carlos Sierra’s scripts (coe_xfr_sql_profile.sql) to demonstrate the usage of SQL Profiles. It is available in his GitHub repository. Simply download it and place it in your current directory. Run it by providing the SQL ID and the desired SQL plan.
SQL > START coe_xfr_sql_profile.sql cafx9pnjmr7v3 1781642509
Parameter 1:
SQL_ID (required)
AVG_ET_SECS_MEM AVG_ET_SECS_AWR PLAN_HASH_VALUE EXECUTIONS_MEM EXECUTIONS_AWR
--------------- --------------- --------------- -------------- --------------
0.001529 0.001529 1781642509 1 1
0.031639 0.031639 1181163556 2 2
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "cafx9pnjmr7v3"
PLAN_HASH_VALUE: "1781642509"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_cafx9pnjmr7v3_1781642509.sql
on TARGET system in order to create a custom SQL Profile
with plan 1781642509 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.The script has been executed, generating the file coe_xfr_sql_profile_cafx9pnjmr7v3_1781642509.sql, which will handle everything for us. If the query being tuned uses literals, but you want the profile to apply to all literals, you can manually modify the force_match parameter in the script from FALSE to TRUE.
SQL>@coe_xfr_sql_profile_cafx9pnjmr7v3_1781642509.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_cafx9pnjmr7v3_1781642509.sql 11.4.4.4 2025/03/28 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_cafx9pnjmr7v3_1781642509.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID cafx9pnjmr7v3 based on plan hash
SQL>REM value 1781642509.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_cafx9pnjmr7v3_1781642509.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cafx9pnjmr7v3_1781642509');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
PROCEDURE wa (p_line IN VARCHAR2) IS
BEGIN
DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
END wa;
BEGIN
DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
-- SQL Text pieces below do not have to be of same length.
-- So if you edit SQL Text (i.e. removing temporary Hints),
-- there is no need to edit or re-align unmodified pieces.
wa(q'[SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp]');
DBMS_LOB.CLOSE(sql_txt);
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
q'[OPT_PARAM('_optimizer_cbqt_or_expansion' 'off')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
:signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_cafx9pnjmr7v3_1781642509',
description => 'coe cafx9pnjmr7v3 1781642509 '||:signature||' '||:signaturef||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
DBMS_LOB.FREETEMPORARY(sql_txt);
END;
/
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
5519277933637383967
SIGNATUREF
---------------------
5519277933637383967
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_cafx9pnjmr7v3_1781642509 completedTo drop a sql profile:
SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cafx9pnjmr7v3_1781642509');Method 3 : Using DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure
It is also possible to perform this manually without using coe_xfr_sql_profile.sql. In this approach, we manually create the outline data and assign it to a variable of type SYS.SQLPROF_ATTR. Then, we import it using the DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure.
In this method, we simply export the better-performing plan from a previously executed SQL statement. This could be the same statement with the same SQL_ID from a different child cursor, or a different SQL_ID whose plan can be applied to the statement we want to tune.
In my example, I will import the plan from another child cursor that belongs to the same SQL statement.
SQL> DECLARE
v_sqlprof_xml clob;
BEGIN
SELECT EXTRACT (VALUE (x), '/outline_data').getclobval ()
into v_sqlprof_xml
FROM XMLTABLE (
'/*/outline_data'
PASSING (SELECT xmltype (other_xml) xml
FROM v$sql_plan
WHERE other_xml IS NOT NULL
AND sql_id = 'cafx9pnjmr7v3'
AND child_number = 1)) x;
DBMS_SQLTUNE.import_sql_profile (
name => 'RANGESCAN_SQLPROFILE',
description => 'Sample Index Range Scan Sql Profile Created using custom outline data',
category => 'DEFAULT',
sql_text => q'[SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp]',
REPLACE => TRUE,
force_match => TRUE,
profile_xml => v_sqlprof_xml);
END;
SQL> SELECT
extractvalue(value(h), '.') AS hint
FROM
dbmshsxp_sql_profile_attr dspa,
TABLE ( xmlsequence(extract(xmltype(dspa.comp_data), '/outline_data/hint')) ) h
WHERE
dspa.profile_name = 'RANGESCAN_SQLPROFILE'In this post, I have tuned the same SQL statement using various methods: fixing a SQL Plan Baseline, applying a SQL Patch, and using a SQL Profile (with three different approaches: SQL Tuning Advisor, Carlos Sierra’s scripts, and importing custom outline data)
Related Internal Tables:
All three methods are essentially sets of directives tied to a specific SQL statement, so we can think of them as objects related to a sql statement. For this reason, the corresponding internal tables use the SQLOBJ prefix; such as SYS.SQLOBJ$, SYS.SQLOBJ$PLAN, SYS.SQLOBJ$DATA, and SYS.SQLOBJ$AUXDATA. These tables store the underlying information referenced by the views DBA_SQL_PLAN_BASELINES, DBA_SQL_PROFILES, and DBA_SQL_PATCHES.
SYS.SQLOBJ$ stores SQL Patch, SQL Profile and SQL Plan Baseline Data for a specific sql statement:
SQL> select signature, obj_type,plan_id,name from SYS.SQLOBJ$ WHERE SIGNATURE= (SELECT EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE SQL_ID = '1spsynxpcfs4c');
SIGNATURE OBJ_TYPE PLAN_ID NAME
______________________ ___________ _____________ _________________________________
7751366788954966074 3 0 SQL_PATCH_EMPIDPRICE_RANGESCAN
7751366788954966074 2 3154332485 SQL_PLAN_6r4m30upqrk1ubc034b45
7751366788954966074 1 0 TABLEFULLSCAN_SQLPROFILEIn this table, the OBJ_TYPE column indicates the type of object: 1 for SQL Profiles, 2 for SQL Plan Baselines, and 3 for SQL Patches. The PLAN_ID is set to 0 for SQL Profiles and SQL Patches, but for SQL Plan Baselines, it corresponds to the PLAN_HASH_2 value (found in the other_xml column of V$SQL). This value differs from the traditional PLAN_HASH_VALUE, as it also incorporates a hash of the predicate section of the plan. The optimizer uses PLAN_HASH_2 during its decision-making process, not PLAN_HASH_VALUE. You can observe this behavior in the 10053 trace file I’ve shared later in this post.
SYS.SQLOBJ$PLAN stores execution plan information of the sql plan baseline. For each plan, the hint set of the SQL plan baseline is stored in the OTHER_XML column of the row where the depth value is 1.
SQL> select SIGNATURE, OBJ_TYPE, PLAN_ID,OPERATION, OPTIONS, OBJECT_TYPE,DEPTH, OBJECT_NAME, ACCESS_PREDICATES, FILTER_PREDICATES, QBLOCK_NAME FROM SYS.SQLOBJ$PLAN ORDER BY PLAN_ID, DEPTH;
SIGNATURE OBJ_TYPE PLAN_ID OPERATION OPTIONS OBJECT_TYPE DEPTH OBJECT_NAME ACCESS_PREDICATES FILTER_PREDICATES QBLOCK_NAME
____________________ ________ ___________ ___________________ _________________ ______________ ______ ______________________ ____________________ _______________________ ______________
7751366788954966074 2 3154332485 SELECT STATEMENT 0
7751366788954966074 2 3154332485 SORT AGGREGATE 1 SEL$1
7751366788954966074 2 3154332485 INDEX FAST FULL SCAN INDEX 2 IN_SALES_SALESEMPID "EMPLOYEE_ID"=:P_EMP SEL$1SYS.SQLOBJ$DATA stores all the hints related in the COMP_DATA column. All the valuable hint information used by the optimizer stored over here.
As the name suggests, SYS.SQLOBJ$AUXDATA table captures auxiliary information such as the creator, creation date, last modification time, and some execution statistics.
Short Summary:
SQL Plan baselines:
Introduced in 11g.
Requires no additional license with Enterprise Edition.
Enforce specific plans and guarantee plan stability..
Can store multiple execution plans.
Allow recording new plans for potential improvement.
Needs exact match for an entire SQL statement (with all the literals)
Only accepted/fixed plans are used.
Oracle recommends using SQL Plan Management for more effective and controlled execution plan management.
SQL Profiles :
Introduced in 10g.
Requires Diagnostic and Tuning Pack additional license with Enterprise Edition.
Does not enforce a specific execution plan and does not guarantee plan stability.
Does not contain execution plans, unlike SPM baselines, which can store multiple plans.
Contains optimizer directives to help the optimizer produce better plans dynamically.
Can be applied across multiple SQL statements with matching text patterns.
Adapts to plan changes, meaning a better plan could be lost if an environmental change is reverted.
SQL Patches :
Official API introduced in 12c, also possible in 11g using an undocumented DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH procedure.
Requires no additional license with Enterprise Edition.
Influence query execution without enforcing a specific plan.
Does not contain a plan but applies optimizer hints to modify execution behavior.
Also can be generated via the SQL Repair Advisor as a workaround for problematic queries.
Plan Evolution & Stability
A new plan that performs better will not be applied automatically with an SPM baseline and it will be stored in the history. You can manually review and accept the new plan. This provides flexibility while ensuring execution plan stability. SQL Profiles and SQL Patches does not evolve over time on their own.
Partial vs. Full Plan Application
SQL Profiles and SQL Patches can apply hints partially: if an index is dropped, the optimizer ignores the hint and applies the remaining ones.
SPM Baselines apply plans as a whole: if the exact plan cannot be reproduced, a new plan is calculated, and no remnants of the previous plan remain.
Now the question arises:
What happens if a SQL statement has an active SQL Profile, SQL Plan Baseline, and SQL Patch? :
The first recommendation is to “keep it simple”. In other words, avoid mixing different methods unless necessary. While SQL Profiles, SQL Patches, and SQL Plan Baselines are not mutually exclusive, they can be used together in a complementary way. For example, a SQL Profile or SQL Patch can be applied temporarily to address a performance issue and then replaced by a SQL Plan Baseline. Once the baseline is in place, the SQL Profile or Patch can be safely dropped. This approach ensures long-term plan stability while still allowing for flexibility. If a better execution plan is possible in the future, it will be captured as a candidate plan and can later be evolved into an accepted baseline.
I applied all three methods to the same sample statement, but they all direct the query to perform an index range scan on the IN_SALES_SALESEMPID.
SQL> SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
COUNT(*)
----------
100
SQL> SELECT sql_id,
child_number,
sql_text,
plan_hash_value,
sql_profile,
sql_patch,
sql_plan_baseline
FROM v$sql
WHERE sql_id = 'cafx9pnjmr7v3'
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE
---------- ------------ --------------- ------------------ ------------------------- --------------------
cafx9pnjmr7v3 1 1781642509 RANGESCAN_SQLPROFILE SQL_PATCH_EMPID_RANGESCAN SQL_PLAN_4t63a175jrdsz93013745This is the all the execution plan data in the cursor cache.
SQL> SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
COUNT(*)
----------
100
SQL> select * from dbms_xplan.display_cursor('cafx9pnjmr7v3',1,'ADVANCED');
SQL_ID cafx9pnjmr7v3, child number 1
-------------------------------------
SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 1781642509
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| IN_SALES_SALESEMPID | 100 | 300 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_cbqt_or_expansion' 'off')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :P_EMP (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
- OPT_PARAM('_optimizer_cbqt_or_expansion' 'off')
2 - SEL$1 / SS@SEL$1
U - INDEX(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID")) / hint overridden by another in parent query block
- index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
Note
-----
- SQL profile RANGESCAN_SQLPROFILE used for this statement
- SQL patch "SQL_PATCH_EMPID_RANGESCAN" used for this statement
- SQL plan baseline SQL_PLAN_4t63a175jrdsz93013745 used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SS]]></t><s><![CDATA[SEL
$1]]></s></h></f></q>
Actually, in this example the sql plan baseline, sql profile and sql patch, all three convey the same instructions to the optimizer and do not conflict with each other. In the Note section, we can observe that all three methods are recorded as being used for this statement. However, in the Hint Report, it is recorded as the hint injected by SQL Patch overrides the hint that controls index access, which originates from the SQL Profile.
What if all three suggest different directives that conflict with each other?
I will begin with a conflicting SQL Plan Baseline and SQL Profile. Here is the test case.
-- Use it wisely, only on non-production systems. This command will flush all the shared_pool data.
SQL> alter system flush shared_pool;
-- Drop existing sql_plan_baseline
DECLARE
v_loaded PLS_INTEGER;
BEGIN
v_loaded :=
DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SQL_4c986a09cb1bb71f',
plan_name => 'SQL_PLAN_4t63a175jrdsz93013745');
END;
/
-- Accept and Fix a sql plan from AWR that uses index fast full scan, not the best choice, index range scan is better for 95% of employees.
DECLARE
plans_loaded PLS_INTEGER;
BEGIN
plans_loaded :=
DBMS_SPM.load_plans_from_awr (begin_snap => 9405,
end_snap => 9406,
basic_filter => 'sql_id=''cafx9pnjmr7v3'' and plan_hash_value=1181163556',
fixed => 'YES');
DBMS_OUTPUT.put_line ('SQL_HANDLE and PLAN_NAME: ' || plans_loaded);
END;
DECLARE
plans_loaded PLS_INTEGER;
plans_modified PLS_INTEGER;
BEGIN
plans_loaded :=
DBMS_SPM.alter_sql_plan_baseline (
sql_handle => 'SQL_4c986a09cb1bb71f',
plan_name => 'SQL_PLAN_4t63a175jrdszbc034b45',
attribute_name => 'FIXED',
END;
-- Drop the existing sql profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('RANGESCAN_SQLPROFILE');
-- Create a sql profile that uses full table scan with a sample optimizer hint(star_transformation_enabled)
SELECT /*+ FULL(ss) OPT_PARAM('star_transformation_enabled' 'true') */ COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp;
select * from dbms_xplan.display_cursor()
SQL_ID 717zkn3qq4uvt, child number 0
-------------------------------------
SELECT /*+ FULL(ss) OPT_PARAM('star_transformation_enabled' 'true') */
COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 1047182207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 622 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| SALES | 45005 | 131K| 622 (2)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=TO_NUMBER(:P_EMP))
DECLARE
v_sqlprof_xml clob;
BEGIN
SELECT EXTRACT (VALUE (x), '/outline_data').getclobval ()
into v_sqlprof_xml
FROM XMLTABLE (
'/*/outline_data'
PASSING (SELECT xmltype (other_xml) xml
FROM v$sql_plan
WHERE other_xml IS NOT NULL
AND sql_id = '717zkn3qq4uvt'
AND child_number = 0)) x;
DBMS_SQLTUNE.import_sql_profile (
name => 'TABLEFULLSCAN_SQLPROFILE',
description => 'Sample Table Full Scan Sql Profile Created using custom outline data',
category => 'DEFAULT',
sql_text => q'[SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp]',
REPLACE => TRUE,
force_match => TRUE,
profile_xml => v_sqlprof_xml);
END;In this example, I have fixed a SQL Plan Baseline that uses an Index Fast Full Scan (which is not the best choice) and created a SQL Profile that uses a Full Table Scan on the SALES table (which is not a good choice either).
SQL> select * from dbms_xplan.display_cursor('cafx9pnjmr7v3',0,'ADVANCED');
SQL_ID cafx9pnjmr7v3, child number 0
-------------------------------------
SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 1181163556
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 482 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IN_SALES_SALESEMPID | 45005 | 131K| 482 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('star_transformation_enabled' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 12 (U - Unused (5))
---------------------------------------------------------------------------
0 - STATEMENT
U - ALL_ROWS / hint overridden by another in parent query block
U - DB_VERSION('19.1.0') / hint overridden by another in parent query block
U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block
U - OPTIMIZER_FEATURES_ENABLE('19.1.0') / hint overridden by another in parent query block
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
- OPT_PARAM('_optim_peek_user_binds' 'false')
- OPT_PARAM('star_transformation_enabled' 'true')
2 - SEL$1 / SS@SEL$1
U - FULL(@"SEL$1" "SS"@"SEL$1") / hint overridden by another in parent query block
- INDEX_FFS(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
Note
-----
- SQL profile TABLEFULLSCAN_SQLPROFILE used for this statement
- SQL plan baseline SQL_PLAN_4t63a175jrdszbc034b45 used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SS]]></t><s><![CDATA[SEL$1]]
></s></h></f></q>
Both SQL Plan Baseline and SQL Profile are taken into consideration by the optimizer. The starting point for execution (or driving object) of the query and the access method to this object was conflicting with each other. (Table vs Index and FULL TABLE SCAN vs INDEX FAST FULL SCAN). In this case, fixed SQL Plan baseline hints overrides the sql profile hints but optimizer also attempted to apply as many hints as possible from the SQL Profile. So we can say both of them are used, but for conflicting hints, sql plan baseline hints overrides the sql profile. If a syntax error on one of the hints in a SQL Profile occurs, it will not invalidate the whole sql profile. The optimizer will attempt to honor any hints that are valid. For example : OPT_PARAM(‘star_transformation_enabled’ ‘true’)
Now I will raise the stakes by creating a SQL patch that conflicts with both the prior SQL baseline and SQL profile. The conflicting hints are as follows :
- INDEX FAST FULL SCAN access method in sql plan baseline
- FULL TABLE SCAN access method in sql plan profile
- INDEX RANGE SCAN on IN_SALES_SALESEMPIDPRICE access method in sql patch
- OPT_PARAM(‘star_transformation_enabled’ ‘true’) in sql profile
- OPT_PARAM(‘star_transformation_enabled’ ‘false’) in sql patch
-- Also, create a SQL patch for the SQL statement to use a different index
DECLARE
patch_name VARCHAR2 (4000);
BEGIN
patch_name :=
DBMS_SQLDIAG.create_sql_patch (
sql_id => 'cafx9pnjmr7v3',
hint_text =>
q'{index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID" "SALES"."PRICE"))}',
name => 'SQL_PATCH_EMPIDPRICE_RANGESCAN',
VALIDATE => TRUE);
END;
/At this point, I have fixed a SQL Plan Baseline that uses an Index Fast Full Scan (which is not the best choice), created a SQL Profile that uses a Full Table Scan on the SALES table (which is not a good choice either) and created a SQL Patch that enforces an Index Range Scan on the IN_SALES_SALESEMPIDPRICE index (which is also not the best choice).
SQL> select * from dbms_xplan.display_cursor('cafx9pnjmr7v3',0,'ADVANCED');
SQL_ID cafx9pnjmr7v3, child number 0
-------------------------------------
SELECT COUNT (*) FROM insanedba.sales ss WHERE employee_id = :p_emp
Plan hash value: 2303930378
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 121 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| IN_SALES_SALESEMPIDPRICE | 45005 | 131K| 121 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / SS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID" "SALES"."PRICE"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 9 (U - Unused (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - OPT_PARAM('star_transformation_enabled' 'true') / hint overridden by another in parent query block
- OPT_PARAM('star_transformation_enabled' 'false')
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
- OPT_PARAM('_optim_peek_user_binds' 'false')
2 - SEL$1 / SS@SEL$1
U - FULL(@"SEL$1" "SS"@"SEL$1") / hint overridden by another in parent query block
- index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID" "SALES"."PRICE"))
Note
-----
- SQL profile TABLEFULLSCAN_SQLPROFILE used for this statement
- SQL patch "SQL_PATCH_EMPIDPRICE_RANGESCAN" used for this statement
- Failed to use SQL plan baseline for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SS]]></t><s><![CDATA[SEL$1]]>
</s></h></f></q>What this cursor data says?
The fixed SQL Plan Baseline cannot be used because the overall applied hints do not produce the fixed plan as available plan choices, making it unreproducible with the current hints. Additionally, the hint in the SQL Profile (FULL(@”SEL$1″ “SALES”@”SEL$1”)) is overridden by the hint in the SQL Patch (INDEX_RS(@”SEL$1″ “SALES”@”SEL$1” (“SALES”.”EMPLOYEE_ID”, “SALES”.”PRICE”))). However, when no SQL Patch was present and only a SQL Profile and a SQL Plan Baseline were in place, the fixed SQL Plan Baseline was used.
I will analyze the optimizer 10053 event trace file and share the relevant lines that reflect the decision-making process.
/u01/app/oracle/diag/rdbms/bltdb/bltdb/trace/bltdb_ora_2348363.trc
...
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=167218 hint_alias="SALES"@"SEL$1"
SPM: statement found in SMB
kkoctx(): choosing CBO since stmt has outline hints/profile/patch/baseline
....
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
_optim_peek_user_binds = 'false'
star_transformation_enabled = 'false'
...
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("SS") INDEX_RS_ASC ("SS" "IN_SALES_SALESEMPIDPRICE") INDEX_FFS ("SS" "IN_SALES_SALESEMPID") */ COUNT(*) "COUNT(*)" FROM "INSANEDBA"."SALES" "SS" WHERE "SS"."EMPLOYEE_ID"=:B1
Objects referenced in the statement
SALES[SS] 167218, type = 1
Objects in the hash table
Hash table Object 167218, type = 1, ownerid = 2342845844367377261:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("SS") INDEX_RS_ASC ("SS" "IN_SALES_SALESEMPIDPRICE") INDEX_FFS ("SS" "IN_SALES_SALESEMPID") */ COUNT(*) "COUNT(*)" FROM "INSANEDBA"."SALES" "SS" WHERE "SS"."EMPLOYEE_ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)
**************************************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: SALES Alias: SS
#Rows: 900100 SSZ: 0 LGR: 0 #Blks: 2260 AvgRowLen: 13.00 NEB: 0 ChainCnt: 0.00 ScanRate: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Index Stats::
Index: IN_SALES_SALESEMPID Col#: 2
LVLS: 2 #LB: 1759 #DK: 20 LB/K: 87.00 DB/K: 949.00 CLUF: 18999.00 NRW: 900100.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
Index: IN_SALES_SALESEMPIDPRICE Col#: 2 3
LVLS: 2 #LB: 2371 #DK: 458334 LB/K: 1.00 DB/K: 1.00 CLUF: 898583.00 NRW: 900100.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
User hint to use this index
Index: SYS_C0019749 Col#: 1
LVLS: 2 #LB: 1688 #DK: 900100 LB/K: 1.00 DB/K: 1.00 CLUF: 2218.00 NRW: 900100.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
try to generate single-table filter predicates from ORs for query block SEL$1 (#0)
finally: "SS"."EMPLOYEE_ID"=:B1
Access path analysis for SALES
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SALES[SS]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"SS"."EMPLOYEE_ID"=:B1
Column (#2):
NewDensity:0.000056, OldDensity:0.000001 BktCnt:900100.000000, PopBktCnt:900100.000000, PopValCnt:20, NDV:20
Column (#2): EMPLOYEE_ID(NUMBER)
AvgLen: 3 NDV: 20 Nulls: 0 Density: 0.000056 Min: 1.000000 Max: 20.000000
Histogram: Freq #Bkts: 20 UncompBkts: 900100 EndPtVals: 20 ActualVal: yes
Estimated selectivity: 0.050000 , col: #2
Table: SALES Alias: SS
Card: Original: 900100.000000rsel = 0.050000 Rounded: 45005 Computed: 45005.000000 Non Adjusted: 45005.000000
****** Costing Index IN_SALES_SALESEMPIDPRICE
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.050000 , col: #2
Access Path: index (IndexOnly)
Index: IN_SALES_SALESEMPIDPRICE
resc_io: 121.000000 resc_cpu: 9862694
ix_sel: 0.050000 ix_sel_with_filters: 0.050000
Cost: 121.264533 Resp: 121.264533 Degree: 1
Best:: AccessPath: IndexRange
Index: IN_SALES_SALESEMPIDPRICE
Cost: 121.264533 Degree: 1 Resp: 121.264533 Card: 45005.000000 Bytes: 0.000000
***************************************
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=167218 hint_alias="SS"@"SEL$1"
kkoctx(): choosing CBO since stmt has outline hints/profile/patch/baseline
...SPM: planId in plan baseline = 3154332485, planId of reproduced plan = 3692941687
SPM: planId in plan baseline = 3154332485, planId of reproduced plan = 3692941687
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
parse_schema name : SYS
sql_profile name : TABLEFULLSCAN_SQLPROFILE
sql_profile hintset :
hint num 1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num 2 len 35 text: OPTIMIZER_FEATURES_ENABLE('19.1.0')
hint num 3 len 20 text: DB_VERSION('19.1.0')
hint num 4 len 43 text: OPT_PARAM('_optim_peek_user_binds' 'false')
hint num 5 len 47 text: OPT_PARAM('star_transformation_enabled' 'true')
hint num 6 len 8 text: ALL_ROWS
hint num 7 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num 8 len 27 text: FULL(@"SEL$1" "SS"@"SEL$1")
sql_patch name : SQL_PATCH_EMPIDPRICE_RANGESCAN
sql_patch hintset :
hint num 1 len 120 text: index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID" "SALES"."PRICE")) OPT_PARAM('star_transformation_enabled' 'false')
plan_baseline signature : 5519277933637383967
plan_baseline plan_id : 3154332485
plan_baseline hintset :
hint num 1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num 2 len 35 text: OPTIMIZER_FEATURES_ENABLE('19.1.0')
hint num 3 len 20 text: DB_VERSION('19.1.0')
hint num 4 len 8 text: ALL_ROWS
hint num 5 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num 6 len 56 text: INDEX_FFS(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
SPM: baseline plan:
============
Plan Table
============
--------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | ObjectId |
--------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 482 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | |
| 2 | INDEX FAST FULL SCAN | IN_SALES_SALESEMPID| 488K | 1465K | 482 | 00:00:01 | |
--------------------------------------------------------------+-----------------------------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "SS"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
2 - filter("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
SPM: generated non-matching plan:
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | ObjectId |
---------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 121 | | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | |
| 2 | INDEX RANGE SCAN | IN_SALES_SALESEMPIDPRICE| 44K | 132K | 121 | 00:00:02 | 167222 |
---------------------------------------------------------------+-----------------------------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "SS"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
2 - access("EMPLOYEE_ID"=:P_EMP)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Content of other_xml column
===========================
has_user_tab : yes
db_version : 19.0.0.0
parse_schema : SYS
plan_hash_full : 3692941687
plan_hash : 2303930378
plan_hash_2 : 3692941687
sql_patch : SQL_PATCH_EMPIDPRICE_RANGESCAN
sql_profile : TABLEFULLSCAN_SQLPROFILE
-----------------
Optimizer Metrics
-----------------
<stats type="compilation"><stat name="bg">0</stat></stats>
---------------------
QUERY BLOCK REGISTRY
---------------------
<qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[SS]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry>
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID" "SALES"."PRICE"))
END_OUTLINE_DATA
*/
Hint Report:
Query Block: SEL$1
Table: ("SS"@"SEL$1")
Unused: FULL(@"SEL$1" "SS"@"SEL$1")
reason: hint overridden by another in parent query block
index_rs(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID" "SALES"."PRICE"))
Unused: INDEX_FFS(@"SEL$1" "SS"@"SEL$1" ("SALES"."EMPLOYEE_ID"))
reason: hint overridden by another in parent query block
Statement:
Unused: ALL_ROWS
reason: hint overridden by another in parent query block
Unused: OPT_PARAM('star_transformation_enabled' 'true')
reason: hint overridden by another in parent query block
OPT_PARAM('_optim_peek_user_binds' 'false')
Unused: DB_VERSION('19.1.0')
reason: hint overridden by another in parent query block
Unused: OPTIMIZER_FEATURES_ENABLE('19.1.0')
reason: hint overridden by another in parent query block
Unused: IGNORE_OPTIM_EMBEDDED_HINTS
reason: hint overridden by another in parent query block
OPT_PARAM('star_transformation_enabled' 'false')
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
End Hint Report
...
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3692941687
...During optimization the optimizer has found the SQL Plan Baseline, SQL Profile and SQL Patch hintsets. We can see that the hints in the baseline are exactly the hints from the plan that we wanted – but after applying the hints from the sql patch the optimizer says, it can’t reproduce the plan wanted.
Conclusion :
In that case, The SQL Patch hints overrode both the SQL Profile and the SQL Plan Baseline. Before making a final judgment, I considered whether this behavior was influenced not by the method itself (SQL Profile, SQL Patch, or SQL Plan Baseline), but rather by the precedence of hints over one another. For instance, an INDEX RANGE SCAN hint might override a FULL or INDEX FAST FULL SCAN hint. I also evaluated whether the order of creation had any impact. To test this, I varied the creation order of the SQL Patch, SQL Profile, and SQL Plan Baseline, and altered the access methods specified in each. However, the result remained consistent: SQL Patch hints consistently overrode the others in this particular access method hints usage test case. I also ran the same test case using Oracle Database 23ai Free Release, Version 23.6.0.24.10, and the result was the same. SQL Patch hints continued to override both SQL Profiles and SQL Plan Baselines. However, I observed a small difference worth noting, although it does not affect the overall test case. Specifically, while the INDEX_RS hint works when used directly in a SQL statement, it does not work when used as the hint_text in a SQL Patch in Oracle Database 23ai. But, the INDEX_RS_ASC hint works as intended.
Hope it helps.
Osman’s DBlog 
Leave a reply to Osman DİNÇ Cancel reply