Oracle使用SPM固定指定SQL执行计划
环境构造
16:44:23 SYS@zkm(1)> drop table scott.zkm purge; Table dropped. Elapsed: 00:00:00.31 16:44:30 SYS@zkm(1)> create table scott.zkm as select * from dba_objects; Table created. Elapsed: 00:00:00.39 16:44:35 SYS@zkm(1)> create index scott.idx_object_id on scott.zkm(object_id) online; Index created. Elapsed: 00:00:00.21
目标SQL
select /*+ full(a) */ * from scott.zkm a where object_id=1000;
由于hint的强制关系，该SQL会执行全表扫描，如下：
16:44:42 SYS@zkm(1)> set autotrace traceonly 16:46:53 SYS@zkm(1)> set line 500 16:47:02 SYS@zkm(1)> select /*+ full(a) */ * from scott.zkm a where object_id=1000; Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 1571665327 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 366 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| ZKM | 1 | 207 | 366 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=1000) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 122 recursive calls 0 db block gets 1503 consistent gets 1603 physical reads 0 redo size 1628 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 13 sorts (memory) 0 sorts (disk) 1 rows processed
先查找sql_id，
col sql_id for a15 set line 500 col sql_text for a100 select sql_id,sql_text,PLAN_HASH_VALUE from v$sql where sql_text like 'select%from scott.zkm a where object_id=1000';
09:40:56 SYS@zkm(33)> col sql_id for a15 09:40:57 SYS@zkm(33)> set line 500 09:40:57 SYS@zkm(33)> col sql_text for a100 09:40:57 SYS@zkm(33)> select sql_id,sql_text,PLAN_HASH_VALUE from v$sql where sql_text like 'select%from scott.zkm a where object_id=1000'; SQL_ID SQL_TEXT PLAN_HASH_VALUE --------------- ---------------------------------------------------------------------------------------------------- --------------- 9ajk015s54vpv select /*+ full(a) */ * from scott.zkm a where object_id=1000 1571665327 Elapsed: 00:00:00.01
添加基线，
declare l_plans_loaded PLS_INTEGER; begin l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '9ajk015s54vpv'); DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); END; /
09:31:46 SYS@zkm(29)> declare 09:34:17 2 l_plans_loaded PLS_INTEGER; 09:34:17 3 begin 09:34:17 4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '9ajk015s54vpv'); 09:34:17 5 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); 09:34:17 6 END; 09:34:17 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.00
col SQL_HANDLE for a25 col plan_name for a35 select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
17:22:12 SYS@zkm(27)> col SQL_HANDLE for a25 17:22:21 SYS@zkm(27)> col plan_name for a35 17:22:26 SYS@zkm(27)> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE ---------- ------------------------- ----------------------------------- ------------------------------------------ --------- --------- --------- 8.8386E+18 SQL_7aa929320eda7c96 SQL_PLAN_7pa99687dnz4q45324a8c MANUAL-LOAD YES YES YES Elapsed: 00:00:00.00
可以查看SQL_HANDLE='SQL_7aa929320eda7c96'且PLAN_NAME='SQL_PLAN_7pa99687dnz4q45324a8c'对应的执行计划，
17:22:30 SYS@zkm(27)> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_7aa929320eda7c96',plan_name=>'SQL_PLAN_7pa99687dnz4q45324a8c')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_7aa929320eda7c96 SQL text: select /*+ full(a) */ * from scott.zkm a where object_id=1000 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_7pa99687dnz4q45324a8c Plan id: 1160923788 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 1571665327 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1097 | 221K| 366 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| ZKM | 1097 | 221K| 366 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=1000) 24 rows selected. Elapsed: 00:00:00.07
若是库缓存中存在优秀的优秀的执行计划，那么直接找出plan hash value添加到SQL作为基线之一，然后删除较差的sql的基线。
若是没有优秀的执行计划，可以手工使用hint产生你想要的执行计划后，添加为基线，下边演示这种。
添加hint强制走索引，
17:14:03 SYS@zkm(1)> select /*+ index(a idx_object_id) */ * from scott.zkm a where object_id=1000; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3532417104 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| ZKM | 1 | 207 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1000) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 27 recursive calls 0 db block gets 91 consistent gets 1 physical reads 0 redo size 1631 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed
先查找sql_id值，
col sql_id for a15 set line 500 col sql_text for a100 select sql_id,sql_text,PLAN_HASH_VALUE from v$sql where sql_text like 'select%from scott.zkm a where object_id=1000';
17:44:56 SYS@zkm(27)> col sql_id for a15 17:45:03 SYS@zkm(27)> set line 500 17:45:07 SYS@zkm(27)> col sql_text for a100 17:45:10 SYS@zkm(27)> select sql_id,sql_text,PLAN_HASH_VALUE from v$sql where sql_text like 'select%from scott.zkm a where object_id=1000'; SQL_ID SQL_TEXT PLAN_HASH_VALUE --------------- ---------------------------------------------------------------------------------------------------- --------------- 9ajk015s54vpv select /*+ full(a) */ * from scott.zkm a where object_id=1000 1571665327 7akf5qzu2f7wn select /*+ index(a idx_object_id) */ * from scott.zkm a where object_id=1000 3532417104 Elapsed: 00:00:00.00
将上边执行路径走了索引的执行计划添加至
select /*+ full(a) */ * from scott.zkm a where object_id=1000;对应基线
中。
DECLARE k1 pls_integer; begin k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id=>'7akf5qzu2f7wn', plan_hash_value=>3532417104,sql_handle=>'SQL_7aa929320eda7c96' ); end; /
17:38:42 SYS@zkm(27)> DECLARE 17:39:00 2 k1 pls_integer; 17:39:00 3 begin 17:39:00 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 17:39:00 5 sql_id=>'7akf5qzu2f7wn', 17:39:00 6 plan_hash_value=>3532417104,sql_handle=>'SQL_7aa929320eda7c96' 17:39:00 7 ); 17:39:00 8 end; 17:39:00 9 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
17:47:46 SYS@zkm(1)> col SQL_HANDLE for a25 17:47:47 SYS@zkm(1)> col plan_name for a35 17:47:47 SYS@zkm(1)> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE ---------- ------------------------- ----------------------------------- ------------------------------------------ --------- --------- --------- 8.8386E+18 SQL_7aa929320eda7c96 SQL_PLAN_7pa99687dnz4q45324a8c MANUAL-LOAD YES YES YES 8.8386E+18 SQL_7aa929320eda7c96 SQL_PLAN_7pa99687dnz4q54f02d62 MANUAL-LOAD YES YES YES
对应执行计划，
17:47:47 SYS@zkm(1)> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_7aa929320eda7c96',plan_name=>'SQL_PLAN_7pa99687dnz4q54f02d62')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_7aa929320eda7c96 SQL text: select /*+ full(a) */ * from scott.zkm a where object_id=1000 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_7pa99687dnz4q54f02d62 Plan id: 1425026402 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 3532417104 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1097 | 221K| 7 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| ZKM | 1097 | 221K| 7 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 439 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1000) 25 rows selected. Elapsed: 00:00:00.04
将原来的基线删除，
DECLARE k1 pls_integer; begin k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SQL_7aa929320eda7c96',plan_name=>'SQL_PLAN_7pa99687dnz4q45324a8c'); end; /
17:49:14 SYS@zkm(1)> DECLARE 17:49:14 2 k1 pls_integer; 17:49:14 3 begin 17:49:14 4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SQL_7aa929320eda7c96',plan_name=>'SQL_PLAN_7pa99687dnz4q45324a8c'); 17:49:14 5 end; 17:49:14 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 17:49:14 SYS@zkm(1)> col SQL_HANDLE for a25 17:49:19 SYS@zkm(1)> col plan_name for a35 17:49:19 SYS@zkm(1)> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE ---------- ------------------------- ----------------------------------- ------------------------------------------ --------- --------- --------- 8.8386E+18 SQL_7aa929320eda7c96 SQL_PLAN_7pa99687dnz4q54f02d62 MANUAL-LOAD YES YES YES Elapsed: 00:00:00.00
再次运行sql，已经走索引了。
17:50:01 SYS@zkm(1)> select /*+ full(a) */ * from scott.zkm a where object_id=1000; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3532417104 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1097 | 221K| 7 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| ZKM | 1097 | 221K| 7 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 439 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1000) Note ----- - SQL plan baseline "SQL_PLAN_7pa99687dnz4q54f02d62" used for this statement Statistics ---------------------------------------------------------- 12 recursive calls 10 db block gets 78 consistent gets 0 physical reads 0 redo size 1631 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
回退
把基线删除即可。