Oracle 使用SPM来稳定执行计划示例–手工生成SPM
使用手工生成sql plan baseline，在不改变目标sql文本的情况下调整其执行计划，其思路也是“偷梁换柱”，具体步骤为：
（1）针对目标sql使用dbms_spm.load_plans_from_cursor_cache手工生成其初始执行计划所对应的sql plan baseline。此时，使用dbms_spm.load_plans_from_cursor_cache传入的参数为如下所示：
dbms_spm.load_plans_from_cursor_cache
(
sql_id=>’原目标sql的sql_id’,
plan_hash_value=>原目标sql的plan hash value
）
（2）使用dbms_spm.drop_plan_baseline删除在步骤（1）中手工生成的原目标sql的初始执行计划所对应的sql plan baseline。此时，使用dbms_spm.drop_plan_baseline传入的参数为如下所示：
dbms_spm.drop_plan_baseline
（
sql_handle=>’原目标sql在步骤（1）中所产生的sql plan baseline的sql_handle’，
plan_name=>’原目标sql在步骤（1）中所产生的sql plan baseline的plan_name'
）
我们来看一个利用手工生成的sql plan baseline，在不改变目标sql的sql文本的情况下更改其执行计划的实例。先执行如下sql（这里使用了hint强制不使用索引idx_cs，以模拟哪些执行计划错误的sql）：
23:51:15 SQL> select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4;
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
TAB$ 4
Elapsed: 00:00:00.03
其执行计划如下：
23:52:16 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL_ID 1h04bpbyrqxwb, child number 0
-------------------------------------
select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from
cap_spm where object_id=4
Plan hash value: 619725477
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 297 (100)| |
|* 1 | TABLE ACCESS FULL| CAP_SPM | 1 | 30 | 297 (1)| 00:00:04 |
-----------------------------------------------------------------------------
从上述显示内容中可以看到，现在目标sql的执行计划走的是对表cap_spm的全部扫描，其sql_id为1h04bpbyrqxwb，plan hash value为619725477。
现在没有开启sql plan baseline的自动捕获，从如下查询结果中可以看出，目标sql确实没有对应的sql plan baseline：
23:52:25 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4';
no rows selected
Elapsed: 00:00:00.02
使用目标sql的初始执行计划（即对表cap_spm的全部扫描）所对应的sql_id和plan hash value来手工生成对应的sql plan baseline：
23:56:40 SQL> var temp number
23:59:53 SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1h04bpbyrqxwb',plan_hash_value=>619725477);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
从如下查询结果中可以看出，目标sql的初始执行计划所对应的sql plan baseline 已经成功生成，其对应的sql_handle 为SQL_0f62435374ae89c3，plan_name为SQL_PLAN_0ysk3aduax2f3f4dd99e6：
00:00:29 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_0f62435374ae89c3 SQL_PLAN_0ysk3aduax2f3f4dd99e6 MANUAL-LOAD YES YES select /*+no_index(cap_spm,idx_cs) */ object_name,object_id
from cap_spm where o
Elapsed: 00:00:00.00
改写原目标sql，加入强制走索引idx_cs的hint后重新执行：
00:02:17 SQL> select /*+index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4;
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
TAB$ 4
Elapsed: 00:00:00.00
其执行计划如下：
00:04:04 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 4y6khahh4k16x, child number 0
-------------------------------------
select /*+index(cap_spm,idx_cs) */ object_name,object_id from cap_spm
where object_id=4
Plan hash value: 3149005651
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 317 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CAP_SPM | 1 | 30 | 317 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_CS | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
从上述显示内容中可以看到，现在该sql的执行计划已经如我们所愿：从对表CAP_SPM的全表扫描变成了对索引IDX_CS的索引范围扫描，改写后的sql其sql_id为4y6khahh4k16x，Plan hash value:为3149005651。
从如下查询结果可以看出，原目标sql现在依然只有其原执行计划（即对表CAP_SPM的全部扫描）所对应的sql plan baseline：
00:04:13 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_0f62435374ae89c3 SQL_PLAN_0ysk3aduax2f3f4dd99e6 MANUAL-LOAD YES YES select /*+no_index(cap_spm,idx_cs) */ object_name,object_id
from cap_spm where o
Elapsed: 00:00:00.00
用上述改写后的sql的新执行计划（即对索引idx_cs的索引范围扫描）所对应的sql id和plan hash value 以及原目标sql的sql plan baseline的sql_handle来手工生成新的sql plan baseline：
00:08:18 SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'4y6khahh4k16x',plan_hash_value=>3149005651,sql_handle=>'SQL_0f62435374ae89c3');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
从如下查询结果中可以看出，改写过的sql的新执行计划所对应的sql plan baseline 已经成功生成。注意到所有手工生成的sql plan baseline的enabled和accepted的值均为“yes”，这是和自动捕获的sql plan baseline不一样的地方：
00:11:28 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_0f62435374ae89c3 SQL_PLAN_0ysk3aduax2f3d7d172c1 MANUAL-LOAD YES YES select /*+no_index(cap_spm,idx_cs) */ object_name,object_id
from cap_spm where o
SQL_0f62435374ae89c3 SQL_PLAN_0ysk3aduax2f3f4dd99e6 MANUAL-LOAD YES YES select /*+no_index(cap_spm,idx_cs) */ object_name,object_id
from cap_spm where o
Elapsed: 00:00:00.01
drop掉原执行计划（即对表cap_spm的全表扫描）所对应的sql plan baseline：
00:16:24 SQL> exec :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_0f62435374ae89c3',plan_name=>'SQL_PLAN_0ysk3aduax2f3f4dd99e6');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
从如下查询结果中可以看出，现在只剩下了改写过的sql所对应的新执行计划所对应的sql plan baseline：
00:16:43 SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_0f62435374ae89c3 SQL_PLAN_0ysk3aduax2f3d7d172c1 MANUAL-LOAD YES YES select /*+no_index(cap_spm,idx_cs) */ object_name,object_id
from cap_spm where o
Elapsed: 00:00:00.01
再次执行原目标sql：
00:18:54 SQL> select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from cap_spm where object_id=4;
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
TAB$ 4
Elapsed: 00:00:00.01
其执行计划如下：
00:19:00 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID 1h04bpbyrqxwb, child number 2
-------------------------------------
select /*+no_index(cap_spm,idx_cs) */ object_name,object_id from
cap_spm where object_id=4
Plan hash value: 3149005651
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 317 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| CAP_SPM | 1 | 30 | 317 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IDX_CS | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
。。。。。。。。。
Note
-----
- SQL plan baseline SQL_PLAN_0ysk3aduax2f3d7d172c1 used for this statement
50 rows selected.
Elapsed: 00:00:00.05
从上述显示内容中可以看出，现在原目标sql的执行计划已经从对表cap_spm的全部扫描变为了对索引idx_cs的索引范围扫描。注意到上述执行计划中的note部分有如下内容：“SQL plan baseline SQL_PLAN_0ysk3aduax2f3d7d172c1 used for this statement”，这表明改写后的sql的新执行计划（即对索引idx_cs的索引范围扫描）所对应的sql plan baseline已经被oracle成功应用到了原目标sql上，“偷梁换柱”的过程已经成功完成。这里我们已经用手工生成的sql plan baseline成功实现了在不改变目标sql的sql文本的情况下更改其执行计划的目的。
参考文献：【基于oracle的sql优化】---崔华
