参考文档:https://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95105
创建测试用表。并执行查询语句,并查看该语句的执行计划
SYS@test>create table test2 tablespace users as select * from dba_objects;
Table created.
SYS@test>
-- 进行查询
select * from test2 where object_id<10;
查看该语句的执行计划
SYS@test>select sql_id from v$sql where sql_text like 'select * from test2 where object_id%';
SQL_ID
-------------
982wxshw8rbfv
SYS@test>
-- explain
SYS@test>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2898 | 348 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST2 | 14 | 2898 | 348 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_bfxsuw31dur8x99963deb" used for this statement
18 rows selected.
SYS@test>
查看sqlplan视图,发现该PLAN是没有被固定的
SYS@test>select plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id%';
PLAN_NAME ENA ACC FIX
------------------------------ --- --- ---
SQL_PLAN_bfxsuw31dur8x99963deb YES YES NO
SYS@test>
--
SYS@test>SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES where plan_name='SQL_PLAN_bfxsuw31dur8x99963deb' 2 ;
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_b7771ae0c2dd5d1d SQL_PLAN_bfxsuw31dur8x99963deb YES YES NO
SYS@test>
对执行计划进行固定
DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SQL_b7771ae0c2dd5d1d',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
SYS@test>DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SQL_b7771ae0c2dd5d1d',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/ 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SYS@test>
查看固定后的结果
SYS@test>SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES where plan_name='SQL_PLAN_bfxsuw31dur8x99963deb' 2 ;
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_b7771ae0c2dd5d1d SQL_PLAN_bfxsuw31dur8x99963deb YES YES YES
SYS@test>
创建索引,并收集统计信息
create index idx_object_id1 on test2(object_id);
exec dbms_stats.gather_table_stats('SYS','TEST2');
SYS@test>create index idx_object_id1 on test2(object_id);
Index created.
SYS@test>exec dbms_stats.gather_table_stats('SYS','TEST2');
PL/SQL procedure successfully completed.
SYS@test>
查看执行计划,发现使用了SQLPLAN,而建立的索引并没有起作用
SYS@test>explain plan for select * from test2 where object_id<10;
Explained.
SYS@test>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 784 | 348 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST2 | 8 | 784 | 348 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"<10)
Note
-----
- SQL plan baseline "SQL_PLAN_bfxsuw31dur8x99963deb" used for this statement
17 rows selected.
SYS@test>
如果更改object_id<10 为object_id<20 . 可以看到使用了索引。没有使用之前固定的执行计划
SYS@test>explain plan for select * from test2 where object_id<20;
Explained.
SYS@test>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3768730550
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 1666 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 17 | 1666 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID1 | 17 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_ID"<20)
14 rows selected.
SYS@test>
上面的演示,通过一个简单的例子,说明了如何去固定一个SQL执行计划。
--- 补充内容
1 load sqlplan baseline from cursor
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'axfpyf7vzw5wq',
plan_hash_value=>3768730550,
fixed=>'NO',
enabled=>'NO');
end;
--
SYS@test>DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'axfpyf7vzw5wq',
plan_hash_value=>3768730550,
fixed=>'NO',
enabled=>'NO');
end; 2 3 4 5 6 7 8 9
10 /
PL/SQL procedure successfully completed.
SYS@test>
2 修改SQLPLAN baseline的属性
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_b7771ae0c2dd5d1d',
plan_name=>'SQL_PLAN_bfxsuw31dur8x99963deb',
--attribute_name=>'ENABLED',
--attribute_value=>'NO',
--attribute_name=>'FIXED',
--attribute_value=>'NO',
attribute_name=>'autopurge',
attribute_value=>'NO'
);
end;
-- 修改固定为NO
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_b7771ae0c2dd5d1d',
plan_name=>'SQL_PLAN_bfxsuw31dur8x99963deb',
--attribute_name=>'ENABLED',
--attribute_value=>'NO',
attribute_name=>'FIXED',
attribute_value=>'NO'
--attribute_name=>'autopurge',
--attribute_value=>'NO'
);
end;
-- 修改已接受为NO
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_b7771ae0c2dd5d1d',
plan_name=>'SQL_PLAN_bfxsuw31dur8x99963deb',
attribute_name=>'ENABLED',
attribute_value=>'NO'
--attribute_name=>'FIXED',
--attribute_value=>'NO'
--attribute_name=>'autopurge',
--attribute_value=>'NO'
);
end;
3 直接通过sqlid 进行load,然后再固定执行计划
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '6pum75s6s9060');
END;
/
SYS@test>DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '6pum75s6s9060');
END; 2 3 4 5
6 /
PL/SQL procedure successfully completed.
SYS@test>
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_72dffdbeb0c6d1ca',
plan_name=>'SQL_PLAN_75rzxruscdnfa5f3bf724',
attribute_name=>'FIXED',
attribute_value=>'YES'
);
end;
4 一个演进的测试,然后进行固定执行计划
SYS@test>drop index SYS.IDX_OBJECT_ID1;
Index dropped.
SYS@test>
-- 执行语句
SYS@test>select * from test2 where object_id<5;
-- 查找sqlid
SYS@test>select sql_id from v$sql where sql_text='select * from test2 where object_id<5';
SQL_ID
-------------
1b5utgagagrhx
SYS@test>
-- load到sql Plan baseline 中
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '1b5utgagagrhx');
END;
/
SYS@test>DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '1b5utgagagrhx');
END; 2 3 4 5
6 /
PL/SQL procedure successfully completed.
SYS@test>
-- 创建索引
create index idx_object_id2 on test2(object_id) tablespace users;
exec dbms_stats.gather_table_stats('SYS','test2');
-- 再次查询,
select * from test2 where object_id<5;
-- 进行演进
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_30f4ab054111c786');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
SYS@test>SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_30f4ab054111c786');
DBMS_OUTPUT.PUT_LINE(report);
END;SYS@test>SYS@test> 2 3 4 5 6 7
8 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_30f4ab054111c786
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan:
SQL_PLAN_31x5b0p0j3jw66f7568ca
------------------------------------
Plan was verified: Time used .06 seconds.
Plan passed performance
criterion: 416.83 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan
Test Plan Stats Ratio
------------- --------- -----------
Execution Status:
COMPLETE COMPLETE
Rows Processed: 3 3
Elapsed Time(ms): 5.21 .009
578.89
CPU Time(ms): 5.044 0
Buffer Gets: 1250 3 416.67
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes:
0 0
Physical Write Bytes: 0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans
accepted: 1
PL/SQL procedure successfully completed.
SYS@test>
-- 然后fix
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_30f4ab054111c786',
plan_name=>'SQL_PLAN_31x5b0p0j3jw66f7568ca',
attribute_name=>'FIXED',
attribute_value=>'YES'
);
end;
SYS@test>DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.alter_sql_plan_baseline (
sql_handle=>'SQL_30f4ab054111c786',
plan_name=>'SQL_PLAN_31x5b0p0j3jw66f7568ca',
attribute_name=>'FIXED',
attribute_value=>'YES'
);
end; 2 3 4 5 6 7 8 9 10
11 /
PL/SQL procedure successfully completed.
SYS@test>
-- 查询结果 。 ok 了
SYS@test>select enabled,accepted,fixed,reproduced from dba_sql_plan_baselines where sql_handle='SQL_30f4ab054111c786' and PLan_name='SQL_PLAN_31x5b0p0j3jw66f7568ca';
ENA ACC FIX REP
--- --- --- ---
YES YES YES YES
SYS@test>
5 查看SQLPLAN baseline
SYS@test>select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_15fcb21aeb77f6f7',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_15fcb21aeb77f6f7
SQL text: select * from test2 where object_id<25
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_1bz5k3bprgxrr5f3bf724 Plan id: 1597765412
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4066171248
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 |
| 2 | INDEX RANGE SCAN | IDX_OBJECT_ID2 |
------------------------------------------------------
20 rows selected.
SYS@test>
6 查看SQLPLAN baseline的参数。默认是保留53周,占用的sysaux的空间限制是10%
SYS@test>SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SYS@test>
-- 修改sql plan baseline的保留时间为105周
BEGIN
DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;
修改,及查看修改后的参数
BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
SYS@test>SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 30
PLAN_RETENTION_WEEKS 105
SYS@test>
END