SQL PROFILE IS NOT USED – WHY ORACLE DIDNOT USED MY SQL PROFILE?
Have you ever wondered why sql profile is not used even though they contain hints for good plan?
Test case makes things clear. A sample table contains data for couple of countries.
kIsH@Xhydra<>select distinct country from xtbl;
COUNTRY
----------------------------------------------------------------------------------------------------
Russia
Italy
Spain
Portugal
France
Germany
Mexico
USA
Australia
UK
India
11 rows selected.
Sum up the salary for a country
kIsH@Xhydra<>select sum(salary) from xtbl where country ='USA';
SUM(SALARY)
-----------
1.9112E+11
Certain output has been removed for brevity from the plan.
Full table scan is the good plan used for the previous query.
kIsH@Xhydra<>select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7ch1wj027qs9b, child number 0
-------------------------------------
select sum(salary) from xtbl where country ='USA'
Plan hash value: 4207297521
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Tim
e | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 6270 (100)|
| 1 |00:00:00.21 | 22833 | 22830 |
| 1 | SORT AGGREGATE | | 1 | 1 | 12 | |
| 1 |00:00:00.21 | 22833 | 22830 |
|* 2 | TABLE ACCESS FULL| XTBL | 1 | 399K| 4684K| 6270 (1)| 00:00
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COUNTRY"='USA')
Pin the plan 4207297521 using sql profile for 7ch1wj027qs9b
kIsH@Xhydra<>@coe_xfr_sql_profile.sql 7ch1wj027qs9b 4207297521
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
4207297521 .214
Again the same query is executed but the plan is used with the help of newly created sql profile. Observe the last line.
SQL>select sum(salary) from xtbl where country ='USA';
SUM(SALARY)
-----------
1.9112E+11
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7ch1wj027qs9b, child number 0
-------------------------------------
select sum(salary) from xtbl where country ='USA'
Plan hash value: 4207297521
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Tim
e | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 6270 (100)|
| 1 |00:00:00.20 | 22833 | 22830 |
| 1 | SORT AGGREGATE | | 1 | 1 | 12 | |
| 1 |00:00:00.20 | 22833 | 22830 |
|* 2 | TABLE ACCESS FULL| XTBL | 1 | 399K| 4684K| 6270 (1)| 00:00
Note
-----
- SQL profile coe_7ch1wj027qs9b_4207297521 used for this statement <<==used
If literal is changed to a new value, then the SQL profile is skipped as per below example. I changed ‘USA’ to ‘UK’ for country predicate and the default plan is used but without the assistance of SQL profile. This is expected behavior due to exact matching signature.
SQL>select sum(salary) from xtbl where country ='UK';
SUM(SALARY)
-----------
1.9030E+11
Plan used is full table scan but without any SQL profile.
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fc7hya159gu5w, child number 0
-------------------------------------
select sum(salary) from xtbl where country ='UK'
Plan hash value: 4207297521
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Tim
e | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 6270 (100)|
| 1 |00:00:00.21 | 22833 | 22830 |
| 1 | SORT AGGREGATE | | 1 | 1 | 12 | |
| 1 |00:00:00.21 | 22833 | 22830 |
|* 2 | TABLE ACCESS FULL| XTBL | 1 | 398K| 4670K| 6270 (1)| 00:00
Next generate a COE profile with FORCE_MATCH => TRUE and new profile name coe_7ch1wj027qs9b_4207297521fm with prefix ‘fm’ to differentiate previous profile coe_7ch1wj027qs9b_4207297521 created with exact matching signature to use force matching signature. Edit the secondary script generated using vi editor or favorite editor. So far none of the profile is used.
kIsH@Xhydra<>@coe_xfr_sql_profile.sql 7ch1wj027qs9b 4207297521
SQL>!vi coe_xfr_sql_profile_7ch1wj027qs9b_4207297521.sql
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_7ch1wj027qs9b_4207297521fm',
description => 'coe 7ch1wj027qs9b 4207297521fm '||:signature||' '||:signaturef||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
DBMS_LOB.FREETEMPORARY(sql_txt);
END;
/
kIsH@Xhydra<>@coe_xfr_sql_profile_7ch1wj027qs9b_4207297521.sql
kIsH@Xhydra<>REM
When FORCE_MATCH is enabled, the new literal ‘UK’ irrespective of the value, uses the SQL profile with the same plan. This works similar to cursor_sharing=force parameter to force the literal values for the SQL to reuse the cursor
kIsH@Xhydra<>select sum(salary) from xtbl where country ='UK';
SUM(SALARY)
-----------
1.9030E+11
kIsH@Xhydra<>select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fc7hya159gu5w, child number 0
-------------------------------------
select sum(salary) from xtbl where country ='UK'
Plan hash value: 4207297521
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Tim
e | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 6270 (100)|
| 1 |00:00:00.23 | 22836 | 22830 |
| 1 | SORT AGGREGATE | | 1 | 1 | 12 | |
| 1 |00:00:00.23 | 22836 | 22830 |
|* 2 | TABLE ACCESS FULL| XTBL | 1 | 398K| 4670K| 6270 (1)| 00:00
Note
-----
- SQL profile coe_7ch1wj027qs9b_4207297521fm used for this statement <<== used
Similarly multiple SQL statements were executed with different literal values and all of them have the same signature even though SQL_ID are different. Only identical queries having literal changes, use coe_7ch1wj027qs9b_4207297521fm which helps in an environment with multiple queries which should be forced to use same plan only for literal changes.
kIsH@Xhydra<>set lines 200 pages 1000
kIsH@Xhydra<>col SQL_PROFILE for a20
kIsH@Xhydra<>col SQL_TEXT for a20
kIsH@Xhydra<>select sql_id,
2 sql_text,
3 hash_value,
4 plan_hash_value,
5 force_matching_signature,
6 exact_matching_signature,
7 sql_profile
8 from v$sql
9 where sql_id in ('547kxzvmh0sgq','ccjk1jwahhxwh','6k5nyg6s11gbt','25napz5185r7c','0mgztyudga64q','7ch1wj027qs9b','fc7hya159gu5w');
SQL_ID SQL_TEXT HASH_VALUE PLAN_HASH_VALUE FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE SQL_PROFILE
------------- -------------------- ---------- --------------- ------------------------ ------------------------ --------------------
547kxzvmh0sgq select sum(salary) f 3875561974 4207297521 3.7866E+18 1.7154E+19 coe_7ch1wj027qs9b_42
rom xtbl where count 07297521fm <<<<===
ry ='India'
ccjk1jwahhxwh select /* kishan_new 352876432 4207297521 7.5621E+18 2.6738E+18
*/ sum(salary) from
xtbl where country
='UK'
6k5nyg6s11gbt select /* kishan_new 2953887097 4207297521 7.5621E+18 1.2995E+19
*/ sum(salary) from
xtbl where country
='Germany'
25napz5185r7c select /* kishan_new 1115872492 4207297521 7.5621E+18 2.1146E+18
*/ sum(salary) from
xtbl where country
='India'
0mgztyudga64q select sum(salary) f 2599753878 4207297521 3.7866E+18 1.5243E+19 coe_7ch1wj027qs9b_42
rom xtbl where count 07297521fm <<<<===
ry ='Germany'
7ch1wj027qs9b select sum(salary) f 75194667 4207297521 3.7866E+18 1.4224E+19 coe_7ch1wj027qs9b_42
rom xtbl where count 07297521
ry ='USA'
fc7hya159gu5w select sum(salary) f 1251469500 4207297521 3.7866E+18 6.9141E+18 coe_7ch1wj027qs9b_42
rom xtbl where count 07297521fm <<<<===
ry ='UK'
7 rows selected.
Finally, a dummy hint /* kishan_new */ is used with the same ‘UK’ literal query. This query ccjk1jwahhxwh is considered totally different from below query (fc7hya159gu5w)
“select sum(salary) from xtbl where country =’UK’;” .
Point to be Noted: Here, SQL profile is skipped even though FORCE_MATCH=TRUE due to syntactic change. This is where most of DBA’s confuse about the behavior of SQL profile.
kIsH@Xhydra<>select /* kishan_new */ sum(salary) from xtbl where country ='UK';
SUM(SALARY)
-----------
1.9030E+11
kIsH@Xhydra<>select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ccjk1jwahhxwh, child number 0
-------------------------------------
select /* kishan_new */ sum(salary) from xtbl where country ='UK'
Plan hash value: 4207297521
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Tim
e | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 6270 (100)|
| 1 |00:00:00.19 | 22833 | 22830 |
| 1 | SORT AGGREGATE | | 1 | 1 | 12 | |
| 1 |00:00:00.19 | 22833 | 22830 |
|* 2 | TABLE ACCESS FULL| XTBL | 1 | 398K| 4670K| 6270 (1)| 00:00
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
:01 | 398K|00:00:00.18 | 22833 | 22830 |
Testing can be performed with optimizer traces where the sql text length differ each other
SQL profile will not be used due to extra hint
user_id=0 user_name=SYS module=sqlplus@xhydra (TNS V1-V3) action=
sql_id=0ywqpz48527v0 plan_hash_value=-87669775 problem_type=3 command_type=3
----- Current SQL Statement for this session (sql_id=0ywqpz48527v0) -----
select /* kishan_new */ sum(salary) from kish.xtbl where country ='USA'
sql_text_length=72 <<<===== syntax length
sql=select /* kishan_new */ sum(salary) from kish.xtbl where country ='USA'
SQL profile will be used as created
user_id=0 user_name=SYS module=sqlplus@xhydra (TNS V1-V3) action=
sql_id=4crdnpwxpvak5 plan_hash_value=-87669775 problem_type=3 command_type=3
----- Current SQL Statement for this session (sql_id=4crdnpwxpvak5) -----
select sum(salary) from kish.xtbl where country ='USA'
sql_text_length=55 <<<=== syntax length
sql=select sum(salary) from kish.xtbl where country ='USA'
Content of other_xml column
===========================
has_user_tab : yes
db_version : 19.0.0.0
parse_schema : SYS
plan_hash_full : 92547527
plan_hash : 4207297521
plan_hash_2 : 92547527
Note
-----
- SQL profile coe_7ch1wj027qs9b_4207297521 used for this statement <<<===
SUMMARY:
- SQL statements with different syntax (including spaces, characters, dummy hints etc) is considered by oracle as different SQL with different SQL_ID with same or different plan.
- Syntactic difference between two SQL, avoid using SQL profiles
- If exact matching signature is different between two queries , then SQL profile will not be used for another query
- If force matching signature is same for two query with changes in literal, then SQL profile will be shared for all the query except syntactic change.