oracle 11g及其以上得版本中，有如下两种方法可以产生目标sql的sql plan baseline:
。自动捕获。
。手工生成、批量导入（批量导入尤其适用于oracle数据库大版本的升级，它可以确保升级后原有系统所有sql得执行计划不会变更）。
我们来看一个自动捕获sql plan baseline并据此来稳定执行计划的实例。
oracle 11g中optimizer_capture_sql_plan_baselines的默认值为false，optimizer_use_sql_plan_baselines的默认值为true：
22:10:16 SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
在当前session中禁掉spm并同时开启自动捕获sql plan baseline：
22:16:51 SQL> alter session set optimizer_use_sql_plan_baselines=false;
Session altered.
Elapsed: 00:00:00.00
22:17:35 SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
Elapsed: 00:00:00.00
创建测试表cap_spm表：
22:17:53 SQL> create table cap_spm as select * from dba_objects;
Table created.
Elapsed: 00:00:00.92
22:19:21 SQL> create index idx_cs on cap_spm(object_id);
Index created.
Elapsed: 00:00:00.23
22:23:13 SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'CAP_SPM',estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.71
22:28:34 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
20 ICOL$
21 COL$
22 USER$
23 PROXY_DATA$
24 I_PROXY_DATA$
25 PROXY_ROLE_DATA$
26 I_PROXY_ROLE_DATA$_1
27 I_PROXY_ROLE_DATA$_2
28 CON$
9 rows selected.
Elapsed: 00:00:00.04
22:30:57 SQL>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 3v3s3h87rj717, child number 0
-------------------------------------
select object_id,object_name from cap_spm where object_id between 20
and 28
Plan hash value: 3149005651
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CAP_SPM | 8 | 240 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_CS | 8 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
从上述显示内容中可以看出，现在目标sql的执行计划走的是对索引IDX_CS的索引范围扫描。
因为目标sql只执行过一次，所以oracle现在不会自动捕获其sql plan baseline。从如下查询结果中可以看出，该sql限制确实没有对应的sql plan baseline：
22:37:33 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';
no rows selected
Elapsed: 00:00:00.03
22:37:46 SQL> 23:14:42 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
20 ICOL$
21 COL$
22 USER$
23 PROXY_DATA$
24 I_PROXY_DATA$
25 PROXY_ROLE_DATA$
26 I_PROXY_ROLE_DATA$_1
27 I_PROXY_ROLE_DATA$_2
28 CON$
9 rows selected.
Elapsed: 00:00:00.06
其执行计划并未发现任何改变，走的还是对索引IDX_CS的索引范围扫描：
22:38:14 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 3v3s3h87rj717, child number 0
-------------------------------------
select object_id,object_name from cap_spm where object_id between 20
and 28
Plan hash value: 3149005651
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CAP_SPM | 8 | 240 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_CS | 8 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
因为目标sql已经重复执行，所以现在oracle就回自动捕获sql plan baseline了。从如下查询结果中可以看出，oracle已经针对上述执行计划（即对索引IDX_CS的索引范围扫描）产生了一个sql plan baseline，其enable和accepted的值均为“yes”：
22:38:45 SQL>select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE YES YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
Elapsed: 00:00:00.02
这里我们将索引IDX_CS的聚族因子修改为2400万，目的为了能让目标sql的执行计划为对表cap_spm的全部扫描：
22:46:30 SQL> exec dbms_stats.set_index_stats(ownname=>'SYS',indname=>'IDX_CS',clstfct=>24000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
从如下查询结果里可以看出，索引idx_cs的聚族因子确实已修改为2400万：
22:48:31 SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_CS';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_CS 24000000
Elapsed: 00:00:00.09
重新执行目标sql：
22:48:39 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
20 ICOL$
28 CON$
25 PROXY_ROLE_DATA$
26 I_PROXY_ROLE_DATA$_1
21 COL$
23 PROXY_DATA$
24 I_PROXY_DATA$
22 USER$
27 I_PROXY_ROLE_DATA$_2
9 rows selected.
Elapsed: 00:00:00.08
其执行计划为如下所示：
23:25:46 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 3v3s3h87rj717, child number 0
-------------------------------------
select object_id,object_name from cap_spm where object_id between 20
and 28
Plan hash value: 619725477
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 297 (100)| |
|* 1 | TABLE ACCESS FULL| CAP_SPM | 8 | 240 | 297 (1)| 00:00:04 |
-----------------------------------------------------------------------------
从上述显示内容中可以看出，现在该sql的执行计划已经从对索引IDX_CS的索引范围扫描变为对表cap_spm的全表扫描，即执行计划已经发生了变更。
因为目标sql已经重复执行且同时又产生了一个新的执行计划，索引现在oracle就回自动捕获并创建这个新的执行计划对应的sql plan baseline了。
从如下查询如果中可以看出，oracle已经针对上述执行计划（即对表cap_spm得全表扫描）产生了一个新的sql plan baseline，其enabled的值依然为yes，但accepted的值变为了“no”:
23:25:48 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE YES YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE YES NO select object_id,object_name from cap_spm where object_id be
tween 20 and 28
Elapsed: 00:00:00.03
然后我们对当前session关闭自动捕获sql plan baseline并同时开启wpm，即相当于恢复了oracle11g中的默认设置：
23:35:40 SQL> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
Elapsed: 00:00:00.00
23:35:53 SQL> alter session set optimizer_use_sql_plan_baselines=true;
Session altered.
Elapsed: 00:00:00.00
现在索引idx_cs的聚族因子依然是2400万：
23:36:06 SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_CS';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_CS 24000000
Elapsed: 00:00:00.11
再次执行目标sql：
23:37:10 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
20 ICOL$
21 COL$
22 USER$
23 PROXY_DATA$
24 I_PROXY_DATA$
25 PROXY_ROLE_DATA$
26 I_PROXY_ROLE_DATA$_1
27 I_PROXY_ROLE_DATA$_2
28 CON$
9 rows selected.
Elapsed: 00:00:00.01
执行计划如下：
23:37:26 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
SQL_ID 3v3s3h87rj717, child number 2
-------------------------------------
select object_id,object_name from cap_spm where object_id between 20
and 28
Plan hash value: 3149005651
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2520 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CAP_SPM | 8 | 240 | 2520 (1)| 00:00:31 |
|* 2 | INDEX RANGE SCAN | IDX_CS | 8 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
，，，，，，，，，，，，
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
2 - "CAP_SPM".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_5kbt7kwjds1kad7d172c1 used for this statement
50 rows selected.
Elapsed: 00:00:00.12
从上述显示内容中可以看出，现在目标sql的执行计划已经从对表cap_spm的全表扫描恢复为对表索引idx_cs的索引范围扫描。注意到上述执行计划中的note部分有如下内容：”SQL plan baseline SQL_PLAN_5kbt7kwjds1kad7d172c1 used for this statement”，这表明在wpm以开启的情况下，即使目标sql产生了新的执行计划，oracle依然只会应用该sql的enabled和accepted的值均为”yes“的sql plan baseline。
从如下查询结果中可以看出，目标sql所应用的sql plan baseline名为SQL_PLAN_5kbt7kwjds1kad7d172c1，所对应的enabled和accepted的值确实是”yes“（它就是目标sql第一次执行时走索引范围扫描的执行计划所对应的sql plan baseline）:
23:37:34 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE YES YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE YES NO select object_id,object_name from cap_spm where object_id be
tween 20 and 28
Elapsed: 00:00:00.01
从上述测试结果中可以很清晰地看到，spm确实能够稳定目标sql的执行计划。当启用了spm后，它确实能够保证只有被验证过的执行计划（即enabled和accepted的值均为”yes“的sql plan baseline所对应的执行计划）才会被启用，当由于某种原因（比如统计信息的变更）而导致目标sql产生了新的执行计划后，这个新的执行计划并不会被oracle启用。
如果想启用目标sql新的执行计划（即对表cap_spm得全表扫描），应该如何做呢？
针对不同的oracle数据库版本，会有不同的处理办法。比如这里想启用目标sql新的执行计划（即对表cap_spm得全表扫描），如果是oracle11gr1的环境，则只需将目标sql所采用的名为SQL_PLAN_5kbt7kwjds1kad7d172c1的sql plan baseline（即对索引idx_cs的索引范围扫描所对应的sql plan baseline）的accepted值设为”no”就可以了。但遗憾的是，上述sql的执行环境是oracle 11gr2，所以以如下方式执行dbms_spm.alter_sql_plan_baseline时oracle会报错（因为在oracle11gr2中，所有已被accepted的sql plan baseline的accepted值将不再能够被设置为“no”）：
23:55:55 SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kad7d172c1',attribute_name=>'accepted',attribute_value=>'NO');
BEGIN :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kad7d172c1',attribute_name=>'accepted',attribute_value=>'NO'); END;
*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 1
Elapsed: 00:00:00.12
在oracle11gr2中，我们可以联合使用dbms_spm.evolve_sql_plan_baseline和dbms_spm.alter_sql_plan_baseline达到启用目标sql新的执行计划的目的。
先使用dbms_spm.evolve_sql_plan_baseline将目标sql新的执行计划（即对表cap_spm的全表扫描）所对应的名为SQL_PLAN_5kbt7kwjds1kaf4dd99e6的sql plan baseline的accepted值设为“yes”：
00:01:40 SQL> exec :temp:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kaf4dd99e6',verify=>'NO',commit=>'YES');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
从如下查询结果可以看到，名为SQL_PLAN_5kbt7kwjds1kaf4dd99e6的sql plan baseline的accepted值确实已经从之前的“no”变为了“yes”
00:03:47 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE YES YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE YES YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
Elapsed: 00:00:00.01
然后我们再使用dbms_spm.alter_sql_plan_baseline将原先的执行计划（即对索引idx_cs的索引范围扫描）所对应的sql plan baseline的enabled的值设为“no”：
00:03:50 SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_592f279722dc064a',plan_name=>'SQL_PLAN_5kbt7kwjds1kad7d172c1',attribute_name=>'ENABLED',attribute_value=>'NO');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
从如下查询结果中可以看出，原先的执行计划（即对索引idx_cs的索引范围扫描）所对应得sql plan baseline（即SQL_PLAN_5kbt7kwjds1kad7d172c1）的enabled值确实从之前的“yes“变为了”no“：
00:07:57 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id,object_name from cap_spm where object_id between 20 and 28';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kad7d172c1 AUTO-CAPTURE NO YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
SQL_592f279722dc064a SQL_PLAN_5kbt7kwjds1kaf4dd99e6 AUTO-CAPTURE YES YES select object_id,object_name from cap_spm where object_id be
tween 20 and 28
Elapsed: 00:00:00.01
再次执行目标sql：
00:11:05 SQL> select object_id,object_name from cap_spm where object_id between 20 and 28;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
20 ICOL$
28 CON$
25 PROXY_ROLE_DATA$
26 I_PROXY_ROLE_DATA$_1
21 COL$
23 PROXY_DATA$
24 I_PROXY_DATA$
22 USER$
27 I_PROXY_ROLE_DATA$_2
9 rows selected.
Elapsed: 00:00:00.02
执行计划如下：
00:11:09 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 3v3s3h87rj717, child number 1
-------------------------------------
select object_id,object_name from cap_spm where object_id between 20
and 28
Plan hash value: 619725477
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 297 (100)| |
|* 1 | TABLE ACCESS FULL| CAP_SPM | 8 | 240 | 297 (1)| 00:00:04 |
-----------------------------------------------------------------------------
。。。。。。。。。。
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
-----
- SQL plan baseline SQL_PLAN_5kbt7kwjds1kaf4dd99e6 used for this statement
47 rows selected.
Elapsed: 00:00:00.04
从上述显示内容中可以看出，现在sql的执行计划已经对索引idx_cs的索引范围扫描变成了对表cap_spm的全表扫描，即我们要启用新的执行计划（对表cap_spm的全表扫描）的目的已经实现。注意到执行计划中的note部分如下内容：”SQL plan baseline SQL_PLAN_5kbt7kwjds1kaf4dd99e6 used for this statement“，这表明目标sql新的执行计划所对应的sql plan baseline已经被成功启用。
从上述测试结果中可以看出，实际上我们可以轻易地在目标sql的多个执行计划中切换，索引spm确实是既能够主动地稳定执行计划，又保留了继续是欧诺个新的执行计划的计划，并且我们很容易能够启用新的执行计划。
参考文献：【基于oracle的sql优化】---崔华
