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
回退
把基线删除即可。
其他
更改一个或多个语句的SPM：
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_6ac48ec323b5b191') From dual;
扫一扫
1332
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
