SPM-MANUAL

手动生成SPB

1根据sql_id、plan_hash_value导入目标sql至SPB中
2加入hint改写sql，导入改写后的执行计划至SPB
3删除步骤1中产生执行计划对于的SPB

设置试验环境

SYS@fyl>create table t2 as select * from dba_objects;
Table created.
SYS@fyl>create index idx_t2_id on t2(object_id);
Index created.
SYS@fyl>execute dbms_stats.gather_table_stats(ownname => 'sys',tabname => 't2' ,estimate_percent => 100 ,method_opt => 'for all columns size auto' ,cascade => true);
PL/SQL procedure successfully completed.
SYS@fyl>exec dbms_stats.set_index_stats(ownname=>'sys',indname=>'IDX_T2_ID',clstfct=>20000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2_ID';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T1_ID                               20000000
SYS@fyl>select object_id,object_name from t2 where object_id between 100 and 105;
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  55bzx08aspfvg, child number 0
-------------------------------------
select object_id,object_name from t2 where object_id between 100 and 105
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   256 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     7 |   210 |   256   (1)| 00:00:04 |
--------------------------------------------------------------------------
很明显这是个错误的执行计划，没开启SPB自动捕捉情况下，没有数据
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%';
no rows selected

1根据sql_id、plan_hash_value导入目标sql至SPB中

使用目标SQL的执行计划（table full scan）生成SPB
SYS@fyl>var temp number
SYS@fyl>exec :temp :=dbms_spm.load_plans_from_cursor_cache( sql_id=>'55bzx08aspfvg',plan_hash_value=>'1513984157')
PL/SQL procedure successfully completed.
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%';
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC
------------------------------ ------------------------------ -------------- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
SYS_SQL_259d611fd3d979fd       SQL_PLAN_2b7b13z9xkygxb860bcf2 MANUAL-LOAD    YES YES
select object_id,object_name from t2 where object_id between 100 and 105

2加入hint改写sql，导入改写后的执行计划至SPB

SYS@fyl>select /*+ index(t2 idx_t2_id) */ object_id,object_name from t2 where object_id between 100 and 105;
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  7kns0j57f3fvt, child number 0
-------------------------------------
select /*+ index(t2 idx_t2_id) */ object_id,object_name from t2 where
object_id between 100 and 105
Plan hash value: 921640168
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |  2073 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2        |     7 |   210 |  2073   (1)| 00:00:25 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2_ID |     7 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

SYS@fyl>exec :temp :=dbms_spm.load_plans_from_cursor_cache( sql_id=>'7kns0j57f3fvt',plan_hash_value=>'921640168',sql_handle=>'SYS_SQL_259d611fd3d979fd');
PL/SQL procedure successfully completed.
SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%';
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC
------------------------------ ------------------------------ -------------- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
SYS_SQL_259d611fd3d979fd       SQL_PLAN_2b7b13z9xkygx6abe946f MANUAL-LOAD    YES YES
select object_id,object_name from t2 where object_id between 100 and 105

SYS_SQL_259d611fd3d979fd       SQL_PLAN_2b7b13z9xkygxb860bcf2 MANUAL-LOAD    YES YES
select object_id,object_name from t2 where object_id between 100 and 105

3删除步骤1中产生执行计划对于的SPB

SYS@fyl>exec :temp :=dbms_spm.drop_sql_plan_baseline( sql_handle=>'SYS_SQL_259d611fd3d979fd',plan_name=>'SQL_PLAN_2b7b13z9xkygxb860bcf2');
PL/SQL procedure successfully completed.

SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%';
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC
------------------------------ ------------------------------ -------------- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
SYS_SQL_259d611fd3d979fd       SQL_PLAN_2b7b13z9xkygx6abe946f MANUAL-LOAD    YES YES
select object_id,object_name from t2 where object_id between 100 and 105

验证：再次执行原sql

SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2_ID';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T2_ID                               20000000

SYS@fyl>select object_id,object_name from t2 where object_id between 100 and 105;
SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  55bzx08aspfvg, child number 1
-------------------------------------
select object_id,object_name from t2 where object_id between 100 and 105
Plan hash value: 921640168
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |  2073 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2        |     7 |   210 |  2073   (1)| 00:00:25 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2_ID |     7 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=105)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
   - SQL plan baseline SQL_PLAN_2b7b13z9xkygx6abe946f used for this statement