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.
- Optimizer_capture_sql_plan_baselines
- 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)
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>’)
