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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s