使用SPM来稳定执行计划

我们可以发现，我们现在走的是索引，对索引IDX_T2的索引范围扫描，因为只执行过一次，所以不会自动捕获其SQL Plan Baseline

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';


no rows selected


SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE   YES
YES
select object_id,object_name from t2 where object_id between 103 and 108
基线出现，再次执行试探
SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8vtdn0kgytfxr, child number 1
-------------------------------------
select object_id,object_name from t2 where object_id between 103 and 108


Plan hash value: 2008370210


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


| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


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


|   0 | SELECT STATEMENT        |         |         |         |       3 (100)|
     |


|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |       7 |     210 |       3   (0)| 00:0
0:01 |


|*  2 |   INDEX RANGE SCAN        | IDX_T2 |       7 |         |       2   (0)| 00:0


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:01 |


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




Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]




45 rows selected.


SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE   YES
YES
select object_id,object_name from t2 where object_id between 103 and 108
并没有生成新的基线。
为了使执行计划变化，我们修改聚簇因子


SQL> exec dbms_stats.set_index_stats(ownname=>'sys',indname=>'IDX_T2',clstfct=>24000000,no_invalidate=>false);


PL/SQL procedure successfully completed.


SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';


INDEX_NAME               CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T2                    24000000


SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8vtdn0kgytfxr, child number 1


An uncaught error happened in prepare_sql_statement : ORA-01403: no data found


NOTE: cannot fetch plan for SQL_ID: 8vtdn0kgytfxr, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)






8 rows selected.
这里刚执行的sql执行计划就被age out了，继续执行。


SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8vtdn0kgytfxr, child number 1
-------------------------------------
select object_id,object_name from t2 where object_id between 103 and 108


Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     |     |   339 (100)|      |
|*  1 |  TABLE ACCESS FULL| T2     |     7 |   210 |   339   (1)| 00:00:05 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T2@SEL$1


Outline Data
-------------


  /*+


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------


   1 - filter(("OBJECT_ID"<=108 AND "OBJECT_ID">=103))


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]




42 rows selected.
此时走的是全表扫描。查看此时的基线


SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE   YES
YES
select object_id,object_name from t2 where object_id between 103 and 108


SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE   YES
NO
select object_id,object_name from t2 where object_id between 103 and 108


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
打开SPM，恢复默认设置


SQL> alter session set optimizer_capture_sql_plan_baselines=false;


Session altered.


SQL> alter system set optimizer_use_sql_plan_baselines=true;


System altered.


SQL> show parameter sql_plan;


NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';


INDEX_NAME               CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T2                    24000000


SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8vtdn0kgytfxr, child number 3
-------------------------------------
select object_id,object_name from t2 where object_id between 103 and 108


Plan hash value: 2008370210


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


| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


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


|   0 | SELECT STATEMENT        |         |         |         |    1907 (100)|
     |


|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |       7 |     210 |    1907   (0)| 00:0
0:23 |


|*  2 |   INDEX RANGE SCAN        | IDX_T2 |       7 |         |       2   (0)| 00:0


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:01 |


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




Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]


Note
-----
   - SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement




49 rows selected.
可以看到目标sql并没有走全表扫描，说明SPM确实可以稳定执行计划，但是如果我们想让他走全表扫描该如何设置呢？
引入两个包dbms_spm.alter _sql_plan_baseline和dbms_spm.evolve_sql_plan_baseline
语法：DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
   sql_handle        IN VARCHAR2 := NULL,
   plan_name         IN VARCHAR2 := NULL,
   attribute_name    IN VARCHAR2,
   attribute_value   IN VARCHAR2)
 RETURN PLS_INTEGER; 
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
   sql_handle   IN VARCHAR2 := NULL,
   plan_name    IN VARCHAR2 := NULL,
   time_limit   IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
   verify       IN VARCHAR2 := 'YES',
   commit       IN VARCHAR2 := 'YES')
  RETURN CLOB; 
各字段意义参考见官方文档
在11gR2环境中不容许把已经是accepted的修改，所以我们只能先把新的基线改为accepted，然后再把原基线的第一个值改为no即可。
SQL> var temp varchar2(1000);
SQL> exec :temp:=dbms_spm.alter _sql_plan_baseline(sql_handle=>'SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'accepted',attribute_value=>'no');
BEGIN :temp:=dbms_spm.alter _sql_plan_baseline(sql_handle=>'SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'accepted',attribute_value=>'no'); END;


                       *
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
current delete exists prior




SQL> exec :temp:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk40b860bcf2',verify=>'no',commit=>'yes');


PL/SQL procedure successfully completed.


SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE   YES
YES
select object_id,object_name from t2 where object_id between 103 and 108


SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE   YES
YES
select object_id,object_name from t2 where object_id between 103 and 108


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------




SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_ac526b1e4be74880',plan_name=>'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name=>'enabled',attribute_value=>'no');


PL/SQL procedure successfully completed.
查看修改结果
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE   NO
YES
select object_id,object_name from t2 where object_id between 103 and 108


SQL_ac526b1e4be74880           SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE   YES
YES
select object_id,object_name from t2 where object_id between 103 and 108


SQL_HANDLE               PLAN_NAME              ORIGIN         ENA
------------------------------ ------------------------------ -------------- ---
ACC
---
SQL_TEXT
--------------------------------------------------------------------------------
实验结果
SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8vtdn0kgytfxr, child number 2


An uncaught error happened in prepare_sql_statement : ORA-01403: no data found


NOTE: cannot fetch plan for SQL_ID: 8vtdn0kgytfxr, CHILD_NUMBER: 2
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)






8 rows selected.
原因同上，


SQL> select object_id,object_name from t2 where object_id between 103 and 108;


 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       103
MIGRATE$


       104
DEPENDENCY$


       105
ACCESS$




 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
       106
I_DEPENDENCY1


       107
I_DEPENDENCY2


       108
I_ACCESS1




6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID    8vtdn0kgytfxr, child number 2
-------------------------------------
select object_id,object_name from t2 where object_id between 103 and 108


Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     |     |   339 (100)|      |
|*  1 |  TABLE ACCESS FULL| T2     |     7 |   210 |   339   (1)| 00:00:05 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T2@SEL$1


Outline Data
-------------


  /*+


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------


   1 - filter(("OBJECT_ID"<=108 AND "OBJECT_ID">=103))


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]


Note
-----


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - SQL plan baseline SQL_PLAN_asnmb3t5yfk40b860bcf2 used for this statement




46 rows selected.
此时已经变为走全表扫描。和sqlprofile比较起来，sqlprofile的automatic模式只能起到不调整sql的同时，调整执行计划。sqlprofile的manual模式是可以稳定执行计划的，但是这又给以后的调整带来麻烦，而SPM刚好发挥了完美的作用，既可以稳定执行计划，又可以为以后的更好的执行计划提供可能。