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;