Implementing Hints through Baseline

Baselines

SQL Plan Management(SPM) provides a mechanism to prevent unwanted plan flips.

Whenever a query is hard parsed, oracle produces several execution plan and compares it with the accepted plan in a baseline. If a match is found, oracle picks that plan.

The behaviour of baseline is governed by 2 parameters.

1. Optimizer_capture_sql_plan_baselines
2. Optimizer_use_sql_plan_baseline

optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines. The default value is false

Optimizer_use_sql_plan_baseline, if set to true, force oracle to use the sql plan baseline (if present). The default is true.

Implementing HINTS without changing code

Environment Setting :-

• Optimizer_capture_sql_plan_baselines (false)
• Optimizer_use_sql_plan_baseline (true)

I created mytab table CTAS dba_objects

Session1>desc mytab
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

Step 1> Query the mytab table without index.

select /*+woindex */ * from mytab where GENERATED='FOR';

no rows selected

SELECT * FROM TABLE(dbms_xplan.display_cursor('0ytzpgnhg1g83'));
SQL_ID  0ytzpgnhg1g83, child number 0
-------------------------------------
select /*+woindex */ * from mytab where GENERATED='FOR'
Plan hash value: 96696846
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   124K(100)|          |
|*  1 |  TABLE ACCESS FULL| MYTAB |  4819K|   422M|   124K  (3)| 00:24:54 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GENERATED"='FOR')

select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;
no rows selected

Step 2> No baseline yet exists for the current query. Lets baseline the plan to make sure it does not pick index automatically so that we can simulate the test.

var v_num number;
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '0ytzpgnhg1g83',plan_hash_value => 96696846 );
PL/SQL procedure successfully completed.
select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT                                                                         SQL_HANDLE                     PLAN_NAME                      ENA ACC

-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---

select /*+woindex */ * from mytab where GENERATED='FOR'                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aada00620d  YES YES

1 row selected.

Step 3> Lets create an index

create index mytab_idx2 on mytab(GENERATED) parallel 4;
Index created.

alter index mytab_idx2 noparallel;

Index altered.

Step4> Execute the query to confirm its not using the index since optimizer_use_sql_plan_baseline is set to true.

select * FROM TABLE(dbms_xplan.display_cursor('0ytzpgnhg1g83',1));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0ytzpgnhg1g83, child number 1
-------------------------------------
select /*+woindex */ * from mytab where GENERATED='FOR'
Plan hash value: 96696846
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   124K(100)|          |
|*  1 |  TABLE ACCESS FULL| MYTAB |  4819K|   422M|   124K  (3)| 00:24:54 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("GENERATED"='FOR')
Note
-----
   - SQL plan baseline SYS_SQL_PLAN_fdd635aada00620d used for this statement
22 rows selected.

Step5>Execute the query with index hint and create the baseline.

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED='FOR';

no rows selected

Select * FROM TABLE(dbms_xplan.display_cursor('ay757nb7anm56'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  ay757nb7anm56, child number 0
-------------------------------------
select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED='FOR'
Plan hash value: 3007699452
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |   995 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYTAB      |  4819K|   422M|   995   (1)| 00:00:12 |
|*  2 |   INDEX RANGE SCAN          | MYTAB_IDX2 |  4896K|       |    90   (2)| 00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GENERATED"='FOR')
19 rows selected.

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'ay757nb7anm56',plan_hash_value => '3007699452' );
select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT                                                                         SQL_HANDLE                     PLAN_NAME                      ENA ACC

-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+woindex */ * from mytab where GENERATED='FOR'                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aa041dae64  YES NO

select /*+woindex */ * from mytab where GENERATED='FOR'                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aada00620d  YES YES

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED='FOR'          SYS_SQL_5229297eca7bb2c7       SYS_SQL_PLAN_ca7bb2c7041dae64  YES YES

3 rows selected.

Step6> Now here, you can see that plan(SYS_SQL_PLAN_fdd635aa041dae64 ) being automatically linked with sql_handle (SYS_SQL_50969e88fdd635aa).
You can accept it.
In case, plan is not linked with sql_handle, use the below method to link plan manually.

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'ay757nb7anm56',plan_hash_value => 3007699452 ,sql_handle => 'SYS_SQL_50969e88fdd635aa');

select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT                                                                         SQL_HANDLE                     PLAN_NAME                      ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+woindex */ * from mytab where GENERATED='FOR'                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aa041dae64  YES YES

select /*+woindex */ * from mytab where GENERATED='FOR'                          SYS_SQL_50969e88fdd635aa       SYS_SQL_PLAN_fdd635aada00620d  YES YES

select /*+index(mytab_idx2 mytab) */ * from mytab where GENERATED='FOR'          SYS_SQL_5229297eca7bb2c7       SYS_SQL_PLAN_ca7bb2c7041dae64  YES YES

3 rows selected.

dbms_spm.load_plans_from_cursor_cache(sql_id => ‘<hinted_sqlid>’,plan_hash_value=><hinted_plan_value>,sql_handle=>’<sql handle of original query>’)