探索SPM和SQL PROFILES
背景：
生产中偶尔会碰到一些sql，有多种执行计划，其中部分情况是统计信息过旧造成的，重新收集下统计信息就行了。
但是有些时候重新收集统计信息也解决不了问题，Oracle提供了几种方式来绑定执行计划，帮我我们解决执行计划错误的问题。
Oracle优化器辅助手段发展史：
Oracle 8：hint Oracle 8i&9: stored outline Oracle 10: sql profile Oracle 11: sql plan manangement
Oracle 11g之后推出SPM（SQL Plan Management），SPM是一种主动稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。SPM默认开启，但是自动捕获默认是关闭的。
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
Oracle SPM 主要组件：
-
Plan Capture 捕获
存储SQL对应Plan的相关信息。这些信息可以唯一确定一个plan。 -
Plan selection 选择
在系统的运行时，Oracle要保证每次执行SQL的执行计划都是使用SQL Baseline中的确定执行计划。同时，跟踪所有该statement执行中生成的新执行计划，作为Plan Histroy信息保存下来。 -
Plan evolution 进化
添加新的plan到SQL baselines中，自动或手动。
通过使用baseline，减少性能回退。可以为新应用生成baselines，逐步演化更好的plan。
一、探索SPM
1. 构造环境
SQL> create table t (id int,name varchar2(20));
Table created.
SQL> insert into t values (1,'a1');
1 row created.
SQL> insert into t values (2,'a2');
1 row created.
SQL> insert into t values (3,'a3');
1 row created.
SQL> insert into t values (4,'a4');
1 row created.
SQL> insert into t values (5,'a5');
1 row created.
SQL> commit;
Commit complete.
2. 执行SQL语句，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 0
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
结论：全表扫描。
3. 创建sql plan baseline
-- 通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线
SQL> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '85yjka0hqww9x',plan_hash_value=>null);
5 END;
6 /
PL/SQL procedure successfully completed.
4. 查看基准线信息
SQL> col SQL_TEXT for a50
SQL> col SQL_HANDLE for a25
SQL> col PLAN_NAME for a30
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ -------------------------------------------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un94ecae5c SELECT * FROM t WHERE id =1 YES
5. 通过函数来查看基线的具体执行计划信息
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_57aaf60b37d40f54',plan_name=>'SQL_PLAN_5garq1cvx83un94ecae5c'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL handle: SQL_57aaf60b37d40f54
SQL text: SELECT * FROM t WHERE id =1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5garq1cvx83un94ecae5c Plan id: 2498539100
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 25 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
24 rows selected.
6. 创建索引
SQL> create index t_idx1 on t(id);
Index created.
7. 再次执行SQL，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 2
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- SQL plan baseline SQL_PLAN_5garq1cvx83un94ecae5c used for this statement
22 rows selected.
结论：执行计划仍然为全表扫描，并没有走索引，而且提示SQL plan baseline已经被应用。
8. 再次查看基准线信息
SQL> col SQL_TEXT for a50
SQL> col SQL_HANDLE for a25
SQL> col PLAN_NAME for a30
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ --------------------------------- ------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un94ecae5c SELECT * FROM t WHERE id =1 YES
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un90007b4c SELECT * FROM t WHERE id =1 NO
结论：由于新创建了索引，优化器再次评估这个sql发现执行计划相比sql计划基线的效率更高，此时会新生成一个不可接受的sql计划基线，ACCEPT状态为NO。
9. 查看新生产的sql计划基线的信息
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_57aaf60b37d40f54',plan_name=>'SQL_PLAN_5garq1cvx83un90007b4c'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_57aaf60b37d40f54
SQL text: SELECT * FROM t WHERE id =1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5garq1cvx83un90007b4c Plan id: 2415950668
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 4055207394
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("ID"=1)
25 rows selected.
结论：新产生的执行计划基线走的是我们想要的索引方式。
10. 演化基线
为了验证基线中一个处于不可接受状态的执行计划是否比一个处于可接受状态的执行计划具有更高的效率，必须通过演化来验证，需要让优化器以不同的执行计划来执行这条SQL语句，观察不可接受状态的执行计划基线是否会带来更好的性能，如果性能确实更高，这个不可接受状态的基线将会转换为可接受状态。
简单演示，手工执行：
SQL> set serveroutput on
SQL> set long 10000
SQL> declare
2 result_clob clob;
3 begin
4 result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_57aaf60b37d40f54',plan_name=>null,verify=>'YES',commit=>'YES');
5 dbms_output.put_line(result_clob);
6 end;
7 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_57aaf60b37d40f54
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT
= YES
Plan: SQL_PLAN_5garq1cvx83un90007b4c
------------------------------------
Plan was verified: Time used .01 seconds.
Plan passed performance criterion: 3.5 times better than baseline plan.
Plan was changed to
an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): .013 .011 1.18
CPU Time(ms): .014 .011 1.27
Buffer Gets: 7 2 3.5
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.
-- 关于verify和commit参数：
Verify如果为yes（默认值）将执行sql语句演化，如果设置为false将不需要演化；
Commit如果为yes（默认值）数据字典将按照演化的结果进行修改，如果设置为no，参数verify设置为yes，只进行演化但是不会修改数据字典。
11. 再次查看基准线信息
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ ----------------------------------- ------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un94ecae5c SELECT * FROM t WHERE id =1 YES
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un90007b4c SELECT * FROM t WHERE id =1 YES
结论：新产生的执行计划基线ACCEPT状态也变为YES。
12. 再次执行SQL，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 2
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 4055207394
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | 1 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
- SQL plan baseline SQL_PLAN_5garq1cvx83un90007b4c used for this statement
24 rows selected
结论：执行计划变为全表扫描，SQL plan baseline变为新产生的基线。
二、对比SQL profile
思考： 如果一个SQL中，即存在sql plan baseline，也同时存在sql profile，那么执行计划是怎么样的呢？
看到有文档是说：profile优先级大于baseline，如果有profile存在，优先使用profile。那么这个说法准确吗？
查阅官方文档描述：SQL profile 和 SQL plan baseline 之间，没有严格的关系。
通过实验验证：
实验1：
基于上面的实验继续做，此时SQL语句走的是索引扫描，SQL plan baseline为：SQL_PLAN_5garq1cvx83un90007b4c
1. 使用sql_profile绑定
-- 通过coe_load_sql_profile.sql 绑定了全表扫描的执行计划。过程省略.....
SQL> select NAME,SQL_TEXT from dba_sql_profiles;
NAME SQL_TEXT
------------------------------------------------------------ -----------------------------------
85YJKA0HQWW9X_1601196873 SELECT * FROM t WHERE id =1
2. 再次执行SQL，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 1
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 4055207394
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | 1 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- SQL profile 85YJKA0HQWW9X_1601196873 used for this statement
- SQL plan baseline SQL_PLAN_5garq1cvx83un90007b4c used for this statement
24 rows selected.
结论：我们可以看到SQL profile和SQL plan baseline两者都应用到了执行计划中，但是走的是INDEX RANGE SCAN扫描方式，并没有按照sql profile中走全表扫描的方式。
3. 10053 trace分析
-- 10053 trace文件用来查看Oracle如何根据成本选择执行计划
SQL> alter session set events '10053 trace name context forever, level 12';
SQL> SELECT * FROM t WHERE id =1;
SQL> alter session set events '10053 trace name context off';
– 查看trace 内容：
-- 1） full的hint先被应用，sql profile生效了
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("T") */ "T"."ID" "ID","T"."NAME" "NAME" FROM "SXC"."T" "T" WHERE "T"."ID"=1
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1280, alloc=16344), compile(in-use=64584, alloc=66928), execution(in-use=3712, alloc=4032)
kkoqbc-subheap (create addr=0x7fdca254fa10)
****************
QUERY BLOCK TEXT
****************
SELECT * FROM t WHERE id =1
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=93952 hint_alias="T"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 3074 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T (NOT ANALYZED)
#Rows: 409 #Blks: 5 AvgRowLen: 100.00 ChainCnt: 0.00
Index Stats::
Index: T_IDX1 Col#: 1
LVLS: 0 #LB: 1 #DK: 5 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1): ID( NO STATISTICS (using defaults)
AvgLen: 13 NDV: 13 Nulls: 0 Density: 0.078240
Table: T Alias: T
Card: Original: 409.000000 Rounded: 4 Computed: 4.09 Non Adjusted: 4.09
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 117487
Resp_io: 3.00 Resp_cpu: 117487
Best:: AccessPath: TableScan -- 全表扫描方式
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.09 Bytes: 0
..........
.........
-- 2）index的hint也被应用，baseline也生效了
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("T") INDEX_RS_ASC ("T" "T_IDX1") */ "T"."ID" "ID","T"."NAME" "NAME" FROM "SXC"."T" "T" WHERE "T"."ID"=1
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1312, alloc=16344), compile(in-use=71104, alloc=72240), execution(in-use=4816, alloc=8088)
kkoqbc-subheap (create addr=0x7fdca24eea58)
****************
QUERY BLOCK TEXT
****************
SELECT * FROM t WHERE id =1
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=93952 hint_alias="T"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 3074 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T (NOT ANALYZED)
#Rows: 409 #Blks: 5 AvgRowLen: 100.00 ChainCnt: 0.00
Index Stats::
Index: T_IDX1 Col#: 1
LVLS: 0 #LB: 1 #DK: 5 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1): ID( NO STATISTICS (using defaults)
AvgLen: 13 NDV: 13 Nulls: 0 Density: 0.078240
Table: T Alias: T
Card: Original: 409.000000 Rounded: 4 Computed: 4.09 Non Adjusted: 4.09
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 117487
Resp_io: 3.00 Resp_cpu: 117487
Access Path: index (AllEqGuess)
Index: T_IDX1
resc_io: 2.00 resc_cpu: 14613
ix_sel: 0.004000 ix_sel_with_filters: 0.004000
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange --索引扫描方式
Index: T_IDX1
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 4.09 Bytes: 0
结论：
1. sql profile和SPM baseline是一起作用，hint中会被合并，本案例中，被合并为 /*+ FULL (“T”) INDEX_RS_ASC (“T” “T_IDX1”) */
2. 当2个hint是冲突的时候，Oracle 会忽略互相冲突的提示HINT的组合。
4. 删除baseline
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_text PLS_INTEGER;
3 BEGIN
4 v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_57aaf60b37d40f54',plan_name => NULL);
5 DBMS_OUTPUT.put_line(v_text);
6 END;
7 /
2
PL/SQL procedure successfully completed.
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
no rows selected
5. 再次执行SQL，查看执行计划
SQL> set autotrace off
SQL> set line222
alter session set statistics_level=all ;
Session altered.
SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 0
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- SQL profile 85YJKA0HQWW9X_1601196873 used for this statement
22 rows selected.
结论：只剩下sql profile 被应用，且执行计划为全表扫描。
实验2：
清空所有数据，重新开始实验。
1. 清空之前测试数据
-- 1.删除测试表
SQL> drop table t;
Table dropped.
-- 2.删除sql_profile
SQL> exec dbms_sqltune.drop_sql_profile(name => '85YJKA0HQWW9X_1601196873');
PL/SQL procedure successfully completed.
-- 3.删除sql plan baselines
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_text PLS_INTEGER;
3 BEGIN
4 v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_57aaf60b37d40f54',plan_name => NULL);
5 DBMS_OUTPUT.put_line(v_text);
6 END;
7 /
2
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_sql_profiles;
COUNT(*)
----------
0
SQL> select count(*) from dba_sql_plan_baselines;
COUNT(*)
----------
0
2. 重新构造环境
SQL> create table t (id int,name varchar2(20));
Table created.
SQL> insert into t values (1,'a1');
1 row created.
SQL> insert into t values (2,'a2');
1 row created.
SQL> insert into t values (3,'a3');
1 row created.
SQL> insert into t values (4,'a4');
1 row created.
SQL> insert into t values (5,'a5');
1 row created.
SQL> commit;
Commit complete.
3. 执行SQL语句，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 1
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
结论：全表扫描。
4. 创建sql plan baseline
-- 通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线
SQL> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '85yjka0hqww9x',plan_hash_value=>null);
5 END;
6 /
PL/SQL procedure successfully completed.
5. 查看基准线信息
SQL> col SQL_TEXT for a50
SQL> col SQL_HANDLE for a25
SQL> col PLAN_NAME for a30
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ ---------------------------------- ------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un94ecae5c SELECT * FROM t WHERE id =1 YES
6. 创建索引
SQL> create index t_idx1 on t(id);
Index created
7. 再次执行SQL，查看执行计划
SQL>set autotrace off
SQL>set line222
SQL>alter session set statistics_level=all ;
Session altered.
SQL>SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 1
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- SQL plan baseline SQL_PLAN_5garq1cvx83un94ecae5c used for this statement
22 rows selected.
结论：仍然全表扫描，并没有走索引，和前面的测试结果一样，符合预期。
8. 查看基准线信息
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ --------------------------------- ------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un94ecae5c SELECT * FROM t WHERE id =1 YES
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un90007b4c SELECT * FROM t WHERE id =1 NO
结论：也生产了新的基线，ACCEPT状态为NO。
9. 使用SQL profile绑定
-- 通过coe_load_sql_profile.sql 绑定了全表扫描的执行计划。过程省略.....
SQL> @coe_load_sql_profile
...
coe_load_sql_profile completed.
10. 演化基线
SQL> set serveroutput on
SQL> set long 10000
SQL> declare
2 result_clob clob;
3 begin
4 result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_57aaf60b37d40f54',plan_name=>null,verify=>'YES',commit=>'YES');
5 dbms_output.put_line(result_clob);
6 end;
7 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_57aaf60b37d40f54
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_5garq1cvx83un90007b4c
------------------------------------
Plan was verified: Time used .01 seconds.
Plan passed performance criterion: 3.5 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): .023 .01 2.3
CPU Time(ms): .023 .01 2.3
Buffer Gets: 7 2 3.5
Physical Read Requests: 0 0
Physical WriteRequests: 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.
11. 查看基准线信息
SQL> select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ ---------------------------------- ------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un94ecae5c SELECT * FROM t WHERE id =1 YES
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un90007b4c SELECT * FROM t WHERE id =1 YES
结论：演化后，新产生的执行计划基线ACCEPT状态也变为YES。
12 .再次执行SQL，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 0
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- SQL profile 85YJKA0HQWW9X_1601196873 used for this statement
- SQL plan baseline SQL_PLAN_5garq1cvx83un94ecae5c used for this statement
23 rows selected.
结论：SQL profil 和 SQL plan baseline 都被应用，但是执行计划变为仍然是全表扫描，SQL plan baseline仍然是老的基线SQL_PLAN_5garq1cvx83un94ecae5c，并没有采用新的基线。
13. 删除sql profile
SQL> exec dbms_sqltune.drop_sql_profile(name => '85YJKA0HQWW9X_1601196873'); PL/SQL procedure successfully completed.
14 .查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 0
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 4055207394
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | 1 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
- SQL plan baseline SQL_PLAN_5garq1cvx83un90007b4c used for this statement
24 rows selected.
结论：删除sql profile后，只有SQL plan baseline被应用，而且执行计划也变为index扫描，SQL plan baseline变成了新的基线SQL_PLAN_5garq1cvx83un90007b4c。
15. 再次使用SQL profile绑定
-- 再次通过coe_load_sql_profile.sql 绑定了全表扫描的执行计划。过程省略.....
SQL> @coe_load_sql_profile
...
coe_load_sql_profile completed.
16. 查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 0
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 8 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- SQL profile 85YJKA0HQWW9X_1601196873 used for this statement
- SQL plan baseline SQL_PLAN_5garq1cvx83un94ecae5c used for this statement
23 rows selected.
结论：SQL profil 和 SQL plan baseline 又都被应用，但是执行计划又变为全表扫描，SQL plan baseline又变成老的基线SQL_PLAN_5garq1cvx83un94ecae5c。
17. 删除全表扫描的sql plan baseline
SQL>DECLARE
2 v_text PLS_INTEGER;
3 BEGIN
4 v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_57aaf60b37d40f54',plan_name => 'SQL_PLAN_5garq1cvx83un94ecae5c');
5 DBMS_OUTPUT.put_line(v_text);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>select sql_handle,PLAN_NAME,sql_text,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACCEPT
------------------------- ------------------------------ -----------------------------------------
SQL_57aaf60b37d40f54 SQL_PLAN_5garq1cvx83un90007b4c SELECT * FROM t WHERE id =1 YES
结论：只剩下一个新的基线，对应的执行计划为index扫描。
18. 再次执行SQL，查看执行计划
SQL> set autotrace off
SQL> set line222
SQL> alter session set statistics_level=all ;
Session altered.
SQL> SELECT * FROM t WHERE id =1;
ID NAME
---------- ----------------------------------------
1 a1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 85yjka0hqww9x, child number 1
-------------------------------------
SELECT * FROM t WHERE id =1
Plan hash value: 4055207394
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 1 |00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 | 1 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- SQL profile 85YJKA0HQWW9X_1601196873 used for this statement
- SQL plan baseline SQL_PLAN_5garq1cvx83un90007b4c used for this statement
24 rows selected.
结论：删除全表扫描的基线后，SQL profil 和 SQL plan baseline 仍然都被应用，但是执行计划变为index扫描，SQL plan baseline变为新的基线SQL_PLAN_5garq1cvx83un90007b4c。
最后修改时间：2022-07-22 10:43:52
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
