使用SPM来稳定执行计划
SQL Profile是一个稳定执行计划的的手段，但是这实际上只一个被动的技术手段，应用在那些执行计划发生了不好的变更的SQL上，即便在我们创建SQL Profile解决了目标SQL执行计划变更的问题，依然不能够保证系统后续执行得SQl的执行计划就不再发生不好的变更。这种不确定性会给oracle升级带来一系列的麻烦，因为不清楚升级之后原来系统之中哪些SQL的执行计划可能发生变化。因此有了SPM（SQL PLAN MANAGEMENT）这个工具，可以说SPM的推出彻底解决了执行计划的稳定性的问题，它既能够主动的稳定执行计划，又能保留继续使用新的执行效率可能更高的执行计划的机会。
下面我们来查两个参数
参数optimizer_capture_sql_plan_baselines 用于控制是否开启自动捕获SQL Plan Baseline,其默认的方式为false，表示在默认的情况下，Oracle并不会自动捕获SQL Plan Baseline.如果设置为true。oracle会在上面参数影响范围内所有重复执行的SQL自动捕获去SQL Plan Baseline。
参数optimizer_use_sql_plan_baselines boolean 用于控制是否启用SQL Plan Baseline 其默认值为true，表示在默认的情况下，oracle在生成执行计划的时候就会启用SPM，使用已有的SQL Plan Baseline。
在当前会话，禁掉SPM，并同时开启捕获SQL Plan Baseline；
创建测试表
SQL> create table t2 as select * from dba_objects;
Table created.
创建索引
SQL> create index idx_t2 on t2(object_id);
Index created.
收集统计信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
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 0
-------------------------------------
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.
我们可以发现，我们现在走的是索引，对索引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刚好发挥了完美的作用，既可以稳定执行计划，又可以为以后的更好的执行计划提供可能。
下面我们来查两个参数
- SQL> show parameter sql_plan
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- optimizer_capture_sql_plan_baselines boolean FALSE
- optimizer_use_sql_plan_baselines boolean TRUE
参数optimizer_use_sql_plan_baselines boolean 用于控制是否启用SQL Plan Baseline 其默认值为true，表示在默认的情况下，oracle在生成执行计划的时候就会启用SPM，使用已有的SQL Plan Baseline。
在当前会话，禁掉SPM，并同时开启捕获SQL Plan Baseline；
- SQL> alter session set optimizer_capture_sql_plan_baselines=true ;
-
- Session altered.
-
- SQL> alter session set optimizer_use_sql_plan_baselines =false;
-
- Session altered.
SQL> create table t2 as select * from dba_objects;
Table created.
创建索引
SQL> create index idx_t2 on t2(object_id);
Index created.
收集统计信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
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.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8vtdn0kgytfxr, child number 0
-------------------------------------
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.
我们可以发现，我们现在走的是索引，对索引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刚好发挥了完美的作用，既可以稳定执行计划，又可以为以后的更好的执行计划提供可能。
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
