Home > Database, Oracle, Query Tuning > SQL Plan Management -1-

SQL Plan Management -1-

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)

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: