手动生成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