(1)启用执行计划基线
SQL> alter system set optimizer_capture_sql_plan_baselines=true;
System altered.
--declare
-- rs pls_integer;
-- begin
-- rs:=dbms_spm.load_plans_from_cursor_cache('4s1dw29xzc2rs');
-- end;
-- /
--PL/SQL procedure successfully completed.
(2)执行旧SQL和新SQL,让执行计划加载到计划基线中。
select id from test01 where id>800000; select /*+full(test01)*/id from test01 where id>800000; --找执行过的SQL对应的SQL_ID; SQL> col SQL_TEXT for a100 SQL> set lin 200 SQL> select SQL_TEXT,SQL_ID from v$sqlarea where SQL_TEXT like '%test01%' and upper(SQL_TEXT) not like 'EXPLAIN%' and SQL_TEXT like '%where id>800000'; SQL_TEXT SQL_ID ---------------------------------------------------------------------------------------------------- ------------- select id from test01 where id>800000 a05acgcuwzfwm select /*+full(test01)*/id from test01 where id>800000 0wg3z9uhv7umc SQL> col SQL_TEXT for a50 SQL> col CREATED for a40 SQL> set lin 200 SQL> select sql_handle, plan_name,enabled accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ACC FIX CREATED SQL_TEXT ------------------------------ ------------------------------ --- --- ---------------------------------------- -------------------------------------------------- SQL> select sql_handle, plan_name,enabled, accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX CREATED SQL_TEXT ----------------------------------------------------- --- --- ---------------------------------------- -------------------------------------------------- SQL_2112181b20c8066e SQL_PLAN_224hs3chch1mff98b55bb YES YES NO 23-JAN-24 06.10.57.000000 PM select id from test01 where id>800000 SQL_344ebb56d048d47e SQL_PLAN_38mpvav84jp3y04c70b35 YES YES NO 23-JAN-24 05.58.47.000000 PM select /*+full(test01)*/id from test01 where id>800000 SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h0f98b55bb YES YES NO 23-JAN-24 05.56.04.000000 PM select id from test01 where id>800000
(3)如果要删除固定的执行计划。
--我们暂时不做删除。 --declare --xx PLS_INTEGER; --BEGIN --xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_344ebb56d048d47e',plan_name=>'SQL_PLAN_38mpvav84jp3y04c70b35'); --END; --/
(4)停用原基线
--select id from test01 where id>800000 --停用旧SQL的执行计划; declare rs pls_integer; begin rs:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( plan_name=>'SQL_PLAN_c2ab4r79z28h0f98b55bb', attribute_name=>'ENABLED', attribute_value=>'NO'); end; / PL/SQL procedure successfully completed. --这里虽然停用了,ACCEPT=YES,没有变。 --ENABLED=NO; 停用了。 --可以看到,ENABLED=NO,停用了。 select sql_handle, plan_name,enabled,accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX CREATED SQL_TEXT --------------------------------------------------- --- --- --- ---------------------------------------- -------------------------------------------------- SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h004c70b35 YES YES YES 23-JAN-24 06.23.47.000000 PM select id from test01 where id>800000 SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h0f98b55bb NO YES NO 23-JAN-24 05.56.04.000000 PM select id from test01 where id>800000
--(5)变更前执行计划。
SQL> select * from table(dbms_xplan.display_cursor('a05acgcuwzfwm',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a05acgcuwzfwm, child number 0
-------------------------------------
select id from test01 where id>800000
Plan hash value: 578627003
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 490 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_ID | 191K| 2432K| 490 (1)| 00:00:06 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID">800000)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
(6)变更执行计划
--变更执行计划。
--连接新基线
declare
ln_ps pls_integer;
begin
ln_ps:=dbms_spm.load_plans_from_cursor_cache
(sql_id=>'0wg3z9uhv7umc', --新SQL的sql_id
plan_hash_value=>262542483, --新SQL的plan_hash_value
sql_handle=>'SQL_c12964b9d3f12200' --旧sql_handle
);
end;
/
--查看执行计划,执行计划已经变更。
SQL> select * from table(dbms_xplan.display_cursor('a05acgcuwzfwm',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a05acgcuwzfwm, child number 0
-------------------------------------
select id from test01 where id>800000
Plan hash value: 578627003
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 490 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_ID | 191K| 2432K| 490 (1)| 00:00:06 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID">800000)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a05acgcuwzfwm, child number 2
-------------------------------------
select id from test01 where id>800000
Plan hash value: 262542483
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TEST01 | 4 | 52 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">800000)
Note
-----
- SQL plan baseline SQL_PLAN_c2ab4r79z28h004c70b35 used for this statement
44 rows selected.
--虽然有两个执行计划,但是这里显然使用了新的执行计划:SQL_PLAN_c2ab4r79z28h004c70b35。
(7)ACCEPTED字段说明
--一个SQL语句对应的基线，我将它们归纳为三种状态 1.accepted（可接受），只有这种状态的基线，优化器才会考虑此基线中的执行计划 2.no-accepted（不可接受），这种状态的基线，优化器在SQL语句解析期间不会考虑。这种状态的基线必须通过演化和验证通过后，转变为accepted状态后，才会被优化器考虑使用 3.fixed为yes（固定），这种状态的基线固有最高优先级！比其他两类基线都要优先考
(8)固定执行计划
--1)固定前检查。
col SQL_TEXT for a50
col CREATED for a40
set lin 200
select sql_handle, plan_name, accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ACC FIX CREATED SQL_TEXT
----------------------- ------------------------------ --- --- ---------------------------------------- --------------------------------------------------
SQL_344ebb56d048d47e SQL_PLAN_38mpvav84jp3y04c70b35 YES NO 23-JAN-24 05.58.47.000000 PM select /*+full(test01)*/id from test01 where id>800000
SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h004c70b35 YES NO 23-JAN-24 06.23.47.000000 PM select id from test01 where id>800000
SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h0f98b55bb YES NO 23-JAN-24 05.56.04.000000 PM select id from test01 where id>800000
--2)查看执行计划,执行计划已经变更。
--由原来的索引范围扫描变更为,全表扫描。
select id from test01 where id>800000;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a05acgcuwzfwm, child number 2
-------------------------------------
select id from test01 where id>800000
Plan hash value: 262542483
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TEST01 | 4 | 52 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">800000)
Note
-----
- SQL plan baseline SQL_PLAN_c2ab4r79z28h004c70b35 used for this statement
22 rows selected.
--SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h004c70b35
--3)固定这个执行计划。
DECLARE
i NATURAL;
BEGIN
i := dbms_spm.alter_sql_plan_baseline(
'SQL_c12964b9d3f12200',
'SQL_PLAN_c2ab4r79z28h004c70b35',
attribute_name => 'FIXED',
attribute_value => 'YES');
dbms_output.put_line(i);
END;
/
--(9)固定完执行计划检查
col SQL_TEXT for a50 col CREATED for a40 set lin 200 select sql_handle, plan_name, accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ACC FIX CREATED SQL_TEXT ----------------------- ------------------------------ --- --- ---------------------------------------- -------------------------------------------------- SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h004c70b35 YES YES 23-JAN-24 06.23.47.000000 PM select id from test01 where id>800000 SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h0f98b55bb YES NO 23-JAN-24 05.56.04.000000 PM select id from test01 where id>800000 --可以看到:SQL_PLAN_c2ab4r79z28h004c70b35,已经固定到SQL上。
(10)再次查看执行计划
select id from test01 where id>800000;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a05acgcuwzfwm, child number 2
-------------------------------------
select id from test01 where id>800000
Plan hash value: 262542483
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TEST01 | 4 | 52 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">800000)
Note
-----
- SQL plan baseline SQL_PLAN_c2ab4r79z28h004c70b35 used for this statement
22 rows selected.
我们的执行计划绑定成功。
(11)总结
执行计划绑定使用SQL_PROFILE绑定时,总是绑定失败,还是使用原来的执行计划。 SPM可以绑定执行计划: 1)optimizer_capture_sql_plan_baselines=true; 2)执行替换前SQL,替换后SQL,此时会缓存到计划基线中; 3)停用SQL的旧的执行计划,绑定新的执行计划到旧SQL; 4)验证SQL的执行计划已经变更。 5)optimizer_capture_sql_plan_baselines参数的取值有以下有三种： 1、FALSE：此参数将关闭sql计划基线功能，不捕获计划基线； 2、TRUE：此参数将启用sql计划基线功能，捕获计划基线； 3、FORCE：此参数将强制oracle数据库总是将当前执行的sql语句的计划捕获为基线， 而不管sql语句是否已经捕获过计划基线。 6)正确设置optmizer_capture_sql_plan_baselines的参数，需要考虑以上三种取值中的特性， 并根据oracle数据库的实际性能需求进行选择： --TRUE; 如果oracle数据库的服务质量要求较高，但数据库的性能不允许增加消耗， 我们可以将优化器参数optimizer_capture_sql_plan_baselines设置为TRUE， 以利用oracle数据库自动优化sql计划基线的功能。 --FORCE; 如果对于数据库性能有一定要求，可以将优化器参数optimizer_capture_sql_plan_baselines设置为FORCE， 使oracle数据库系统总是将当前执行的sql语句的计划捕获为基线，以确保语句持续获得最佳性能。 --FALSE; 如果不需要oracle数据库自动优化sql计划基线的功能，我们可以将优化器参数 optimizer_capture_sql_plan_baselines设置为FALSE，此时oracle数据库系统会忽略sql计划基线功能， 从而提高性能。