Sometimes, you reach a point in performance tuning, where you use a SQL Baseline, or SQL Patch, or SQL Profile to stabilise an execution plan. These methods all effectively inject a hint or set of hints into a statement to produce the desired execution plan. Baselines and Patches will only exactly match a SQL ID and therefore a SQL statement. However, a SQL Profile can optionally do force matching so that it applies to "all SQL statements that have the same text after the literal values in the WHERE clause have been replaced by bind variables.
This setting may be useful for applications that use only literal values because it enables SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to false (default), then the literal values in the WHERE clause are not replaced by bind variables." [Oracle Database SQL Tuning Guide]
I often work with PeopleSoft, whose batch processes often dynamically generate SQL with literal values. Therefore, I usually create force matching profiles when I need to control an execution plan. However, sometimes I come across situations where some exact matching (i.e. not force matching) profiles have been created (often by production DBAs using the tuning advisor) on different statements that have the same force matching signature, and then maybe a force matching profile has also been applied.
Note: SQL Profiles require the Tuning Pack licence.
Where both exact and force matching profiles apply to a SQL statement, the exact matching profile will take precedence over the force matching profile, and even if disabled it will prevent the force matching profile from being applied.
I will demonstrate this with a simple test. I will create a table with a couple of indexes, collect statistics, and generate an execution plan for a query. I am using explain plan for command to force a parse of the statement every time.
CREATE TABLE t (a not null, b) AS
SELECT rownum, ceil(sqrt(rownum)) FROM dual CONNECT BY LEVEL <= 100;
CREATE UNIQUE INDEX t_idx on t(a);
CREATE INDEX t_idx2 on t(b,a);
EXEC dbms_stats.gather_table_stats(user,'T');Without Any SQL Profiles
Without any profiles in place, I get a skip scan of T_IDX2, and there is no note in the execution plan.
EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));
Plan hash value: 3418618943
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_IDX2 | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_SS(@"SEL$1" "T"@"SEL$1" ("T"."B" "T"."A"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=42)
filter("A"=42)
…Force Matching Profile
Now I will create an exact matching SQL profile on the query that will force the use of the unique index. The query is the same except the literal value is different (it is 54 instead of 42).
DECLARE
signature INTEGER;
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
SELECT * FROM t WHERE a = 54
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[FULL(@"SEL$1" "T"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'clashing_profile_test_force',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE
);
END;
/ Execution plan with force matching profile (full scan)
NAME CATEGORY SIGNATURE SQL_TEXT CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED DESCRIPTION TYPE STATUS FOR TASK_ID TASK_EXEC_NAME TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_force DEFAULT 11431056000319719221 27-JUL-21 01.35.43.854691 PM
SELECT * FROM t WHERE a = 54
27-JUL-21 01.35.43.000000 PM MANUAL ENABLED YESEXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));
Plan hash value: 1601196873
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| T | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("A"=42)
filter("A"=42)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - STATEMENT
- IGNORE_OPTIM_EMBEDDED_HINTS
1 - SEL$1 / T@SEL$1
- FULL(@"SEL$1" "T"@"SEL$1")
Note
-----
- SQL profile "clashing_profile_test_force" used for this statementExact Matching Profile
I will now add an exact matching profile
DECLARE
signature INTEGER;
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
SELECT * FROM t WHERE a = 42
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."A"))]',
q'[END_OUTLINE_DATA]');
signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'clashing_profile_test_exact',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE
);
END;
/ Execution plan with force matching profile (unique index lookup)
NAME CATEGORY SIGNATURE SQL_TEXT CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED DESCRIPTION TYPE STATUS FOR TASK_ID TASK_EXEC_NAME TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_exact DEFAULT 14843900676141266266 27-JUL-21 01.35.46.825697 PM
SELECT * FROM t WHERE a = 42
27-JUL-21 01.35.46.000000 PM MANUAL ENABLED NO
clashing_profile_test_force DEFAULT 11431056000319719221 27-JUL-21 01.35.43.854691 PM
SELECT * FROM t WHERE a = 54
27-JUL-21 01.35.43.000000 PM MANUAL ENABLED YESEXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));
Plan hash value: 2929955852
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 6 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_IDX | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."A"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=42)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - STATEMENT
- IGNORE_OPTIM_EMBEDDED_HINTS
1 - SEL$1 / T@SEL$1
- INDEX(@"SEL$1" "T"@"SEL$1" ("T"."A"))
Note
-----
- SQL profile "clashing_profile_test_exact" used for this statementDifferent Query
If I run the query with a different literal value, the plan changes back to the full scan, and the note reports the force matching profile was usedEXPLAIN PLAN FOR SELECT * FROM t WHERE a = 54;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));
Plan hash value: 1601196873
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| T | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("A"=54)
filter("A"=54)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - STATEMENT
- IGNORE_OPTIM_EMBEDDED_HINTS
1 - SEL$1 / T@SEL$1
- FULL(@"SEL$1" "T"@"SEL$1")
Note
-----
- SQL profile "clashing_profile_test_force" used for this statementDisable Exact Matching SQL Profile
I will now disable the exact matching profile.exec dbms_sqltune.alter_sql_profile(name=>'clashing_profile_test_exact', attribute_name=>'STATUS',value=>'DISABLED');
SELECT * FROM dba_sql_profiles where name like 'clashing%';
Disable Exact Profile - Execution plan with no profile (skip scan) - Odd
NAME CATEGORY SIGNATURE SQL_TEXT CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED DESCRIPTION TYPE STATUS FOR TASK_ID TASK_EXEC_NAME TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_exact DEFAULT 14843900676141266266 27-JUL-21 01.35.46.825697 PM
SELECT * FROM t WHERE a = 42
27-JUL-21 01.35.52.000000 PM MANUAL DISABLED NO
clashing_profile_test_force DEFAULT 11431056000319719221 27-JUL-21 01.35.43.854691 PM
SELECT * FROM t WHERE a = 54
27-JUL-21 01.35.43.000000 PM MANUAL ENABLED YESEXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));
Plan hash value: 3418618943
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_IDX2 | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_SS(@"SEL$1" "T"@"SEL$1" ("T"."B" "T"."A"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=42)
filter("A"=42)Alter Category of Exact Matching SQL Profile
I could have dropped the SQL Profile, but I might want to retain it for documentation and in case I need to reinstate it. So instead I will move it to a different category.exec dbms_sqltune.alter_sql_profile(name=>'clashing_profile_test_exact', attribute_name=>'CATEGORY',value=>'DO_NOT_USE');
SELECT * FROM dba_sql_profiles where name like 'clashing%';
Change Category of Exact Profile - Execution plan with force matching profile (full scan)
NAME CATEGORY SIGNATURE SQL_TEXT CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED DESCRIPTION TYPE STATUS FOR TASK_ID TASK_EXEC_NAME TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_exact DO_NOT_USE 14843900676141266266 27-JUL-21 02.57.11.343291 PM
SELECT * FROM t WHERE a = 42
27-JUL-21 02.57.19.000000 PM MANUAL DISABLED NO
clashing_profile_test_force DEFAULT 11431056000319719221 27-JUL-21 02.57.08.390801 PM
SELECT * FROM t WHERE a = 54
27-JUL-21 02.57.08.000000 PM MANUAL ENABLED YESEXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));
Plan hash value: 1601196873
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| T | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
…
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("A"=42)
filter("A"=42)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - STATEMENT
- IGNORE_OPTIM_EMBEDDED_HINTS
1 - SEL$1 / T@SEL$1
- FULL(@"SEL$1" "T"@"SEL$1")
Note
-----
- SQL profile "clashing_profile_test_force" used for this statementConclusion
An exact matching profile will be matched to a SQL statement before a force matching SQL statement, even if it is disabled, in which case neither profile will be applied.If you have exact matching SQL profiles that provide the same hints to produce the same execution plan on various similar SQL statements that have the same force matching signature (i.e. they only differ in their literal values), and you wish to replace them with a single force matching profile, then rather than disable the exact matching profiles you should either drop them or if you prefer to retain them for documentation then alter them to a different category.
- The script GitHub used in this blog to demonstrate this behaviour is available on GitHub. They were run in Oracle 19.9 for this post.
- The script disabled_profiles_category.sql moves all disabled profiles from the category DEFAULT to DO_NOT_USE.
In a subsequent post, I will show how to detect conflicting SQL profiles.
@go-faster.co.uk on BlueSky
Posts
