189 8069 5689
这篇文章主要介绍数据库中sql plan baseline怎么用，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！
成都创新互联秉承实现全网价值营销的理念，以专业定制企业官网，成都做网站、网站制作、成都外贸网站建设，小程序开发，网页设计制作，手机网站开发，全网整合营销推广帮助传统企业实现“互联网+”转型升级专业定制企业官网,公司注重人才、技术和管理，汇聚了一批优秀的互联网技术人才,对客户都以感恩的心态奉献自己的专业和所长。
测试内容：
1、dba_sql_plan_baselines表中和时间有关的四个字段CREATED，LAST_MODIFIED，LAST_EXECUTED，LAST_VERIFIED的变化规律
2、候选sql plan变为accepted sql plan baseline的几种方法
3、SQL语句对应的sql plan baseline均失效的情况下Optimizer将新生成的执行计划演进为sql plan baseline的过程
4、不同用户针对各自用户下的表，执行同一条sql语句， sql plan baseline的共享机制
建立测试用表：
grant connect,resource,unlimited tablespace to scott identified by sdfg_1234;
create table scott.t1 tablespace ts_pub as select * from dba_objects;
create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum<100;
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
1、dba_sql_plan_baselines表中和时间有关字段的变化规律，涉及到以下4个字段
CREATED
LAST_MODIFIED
LAST_EXECUTED
LAST_VERIFIED
###开启session级的sql capture，自动生成首条sql plan baseline
--session 1，设置Session级的capture
SQL> select * from dba_sql_plan_baselines;
no rows selected
alter system optimizer_capture_sql_plan_baselines=TRUE;
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
--session 2，dba_sql_plan_baselines中没有记录，因为上述sql只执行了一次
select sql_handle,sql_text,plan_name,creator,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
--session 1，再次执行一遍sql
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
--session 2，dba_sql_plan_baselines产生了首条sql plan baseline，首条初始状态就是accepted
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
###上述结果中的时间点字段值，last_verified值为空，因为其是这条sql生成的首条baseline所以没有经过验证；因为是新建的sql plan baseline其余三个时间字段值都一样
CREATED：02-JUL-14 02.37.20.000000 PM
LAST_MODIFIED：02-JUL-14 02.37.20.000000 PM
LAST_EXECUTED：02-JUL-14 02.37.20.000000 PM
LAST_VERIFIED：NULL
###上述结果中的时间点字段值，last_verified值为空，因为其是这条sql生成的首条baseline所以没有经过
--session 1，第三次执行sql，执行前关闭sql capture参数
alter session set optimizer_capture_sql_plan_baselines=FALSE;
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
--session 2，观察时间字段状态，CREATED、LAST MODIFIED两个字段值没有变化，这个可以理解，LAST_EXECUTED值应该变化为最近一次的执行时间，但事实却没有变化，即使alter system flush shared_pool以后重新执行语句，也没有变化
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
###通过DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE查看sql_plan_baseline对应的执行计划为FTS
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_d11d993788ae4828
SQL text: select count(*) from scott.t1 where object_id in (select object_id from
scott.t2)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d27ct6y4awk1822a9c5af Plan id: 581551535
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1240933221
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 462 (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT SEMI| | 3 | 27 | 462 (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 177K| 1042K| 455 (1)| 00:00:06 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")
28 rows selected.
###t1表的object_id字段上创建索引，再次执行sql
create index scott.ind_objid_t1 on scott.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
###dba_sql_plan_baselines里又生成了一条plan_name= SQL_PLAN_d27ct6y4awk18b1b38b11(sql_handle与前一条相同的sql)，但没有被accepted的baseline，这条记录的CREATED、LAST_MODIFIED字段表明了该条baseline的创建时间，LAST_EXECUTED、LAST_VERIFIED均为空值
col sql_handle format a20
col creator format a5
col sql_text format a50
col created format a30
col last_modified format a30
col last_executed format a30
col last_verified format a30
set linesize 190
set pagesize 200
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
###执行sql，虽然有索引，但因为baseline的存在，走的依然是FTS
set autotrace traceonly;
SQL> select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
Execution Plan
----------------------------------------------------------
Plan hash value: 1240933221
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 462 (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT SEMI| | 3 | 27 | 462 (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 177K| 1042K| 455 (1)| 00:00:06 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_d27ct6y4awk1822a9c5af" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2557 consistent gets
2556 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
###人工演进sql plan baseline，根据Buffer Get优化前后的对比2557/11=232.45，得出使用索引的sql plan baseline所获得的性能是FTS的232倍，oracle情况下根据隐含参数_plan_verify_improvement_margin(默认值为150，表示1.5倍)的值决定性能达到原先多少倍时accept新的sql plan baseline，此例中已经达到了232被，所以当让是verified and accepted
set serveroutput on
set long 10000
declare
result_clob clob;
begin
result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11',verify=>'YES',commit=>'YES');
dbms_output.put_line(result_clob);
end;
/
-------------------------------------------------------------------------------，
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SQL_d11d993788ae4828
PLAN_NAME =
SQL_PLAN_d27ct6y4awk18b1b38b11
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY =
YES
COMMIT = YES
Plan:
SQL_PLAN_d27ct6y4awk18b1b38b11
------------------------------------
Plan was
verified: Time used .901 seconds.
Plan passed performance criterion: 232.77
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): 59.641 .298 200.14
CPU Time(ms): 34.444 0
Buffer Gets:
2557 11 232.45
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.
###查看PLAN_NAME=SQL_PLAN_d27ct6y4awk18b1b38b11对应sql plan baseline，LAST_VERIFIED和
LAST_MODIFIED为同一个时间，LAST_VERIFIED表示在这个时间完成了Verify动作，LAST_MODIFIED表示在
Verify通过后将此baseline从not accepted变为accepted的时间。
CREATED: 02-JUL-14 03.22.41.000000 PM
LAST_MODIFIED: 02-JUL-14 03.44.10.000000 PM
LAST_VERIFIED:02-JUL-14 03.44.10.000000 PM
###执行该SQL后发现last_executed时间已经是最新的时间了
SQL> select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
LAST_EXECUTED:02-JUL-14 04.25.33.000000 PM
###用dbms_xplan.display_sql_plan_baseline显示Plan_name=SQL_PLAN_d27ct6y4awk18b1b38b11的执行计划，这次采用的是Nest Loop
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_d11d993788ae4828
SQL text: select count(*) from scott.t1 where object_id in (select object_id fro
m
scott.t2)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d27ct6y4awk18b1b38b11 Plan id: 2981333777
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2406492491
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 9 | 56 (2)| 00:00
:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | |
|
| 2 | NESTED LOOPS | | 99 | 891 | 56 (2)| 00:00
:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 3 | SORT UNIQUE | | 99 | 297 | 5 (0)| 00:00
:01 |
| 4 | TABLE ACCESS FULL| T2 | 99 | 297 | 5 (0)| 00:00
:01 |
|* 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"="OBJECT_ID")
阶段总结：
CREATED：sql plan生成到plan_history的时间(可以是accept或者not accept状态)
LAST_MODIFIED：sql plan上一次修改的时间，这个修改时间反映了sql plan演进过程中将not
accetped的sql plan更新为accepted动作发生的时间，也能反映使用alter_sql_plan_baseline
对于sql plan任何属性更改的时间
LAST_VERIFIED：sql plan最后一次被验证的时间，同一个plan被验证一遍之后如果再重复进
行验证，时间还是停留在首次验证的时间；第一条sql plan自动成为sql plan baseline时其
last_verified时间为空，说明其没有经过verify，即使后续对首条sql plan人工进行演进，其last_verified时间依然为空
LAST_EXECUTED：名义上为最后一次执行的时间，实际测下来定格在首次执行的时间，后续
的执行并不会更新
2、使sql plan变为accepted sql plan baseline的几种方法
(1) 调用Dbms_spm.evolve_sql_plan_baseline函数，需要人工调用(在12c版本里已经引入sql plan evolve advisor能实现自动演进sql plan baseline)，这个是最常用的方法，只做如下说明：
其中Verify=yes表示经过optimizer验证
verify=no表示不经过optimizer验证强制变为accepted状态
(2) 调用Dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE或者LOAD_PLANS_FROM_SQLSET函数，这里使用LOAD_PLANS_FROM_CURSOR_CACHE函数将shared pool中已经存在的执行计划load到baseline，且状态变为accepted；
###执行sql，使其cache到shared pool
variable v_objid number;
exec :v_objid:=1000;
select count(*) from scott.t1 where object_id<:v_objid;
SQL> select sql_text,sql_id,child_number,plan_hash_value from v$sql where sql_text like 'select count(*) from scott.t1%';
SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------------------------------------------------------------------------------------ ------------- ------------ ---------------
select count(*) from scott.t1 where object_id<:v_objid 9hup7n51za19u 0 4020739011
###显示执行计划
select * from table(dbms_xplan.display_cursor(sql_id=>'9hup7n51za19u',cursor_child_no=>0,format=>'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9hup7n51za19u, child number 0
-------------------------------------
select count(*) from scott.t1 where object_id<:v_objid
Plan hash value: 4020739011
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | | | 5 (100)|
|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|
|* 2 | INDEX RANGE SCAN| IND_OBJID_T1 | 8893 | 53358 | 5 (0)| 00:00:01
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
--
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<:V_OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
###从shared pool中将上述sql的执行计划load到sql plan baseline，load进来之后就变成了Accepted，没有verify的过程
set serveroutput on
declare
result_int pls_integer;
begin
result_int:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'9hup7n51za19u',plan_hash_value=>4020739011,fixed=>'NO',enabled=>'YES');
dbms_output.put_line(result_int);
end;
/
###在dba_sql_plan_baselines中找到了该条sql plan baseline，已经被accepted
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_text like '%v_objid';
###再次执行sql时已经能用到了这条sql plan baseline了
variable v_objid number;
exec :v_objid:=500;
select count(*) from scott.t1 where object_id<:v_objid;
set autotrace traceonly;
select count(*) from scott.t1 where object_id<:v_objid;
Execution Plan
----------------------------------------------------------
Plan hash value: 4020739011
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 6 | 5 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|
|* 2 | INDEX RANGE SCAN| IND_OBJID_T1 | 8893 | 53358 | 5 (0)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("OBJECT_ID" Note ----- - SQL plan baseline
"SQL_PLAN_gm8nknf6mhghn28a6f5d9" used for this statement Statistics ---------------------------------------------------------- 27
recursive calls 16
db block gets 15
consistent gets 13
physical reads 3136
redo size 527
bytes sent via SQL*Net to client 520
bytes received via SQL*Net from client 2
SQL*Net roundtrips to/from client 0
sorts (memory) 0
sorts (disk) 1
rows processed (3) 通过dbms_sqltune对SQL语句进行调优，并接受其调优建议 这里沿用本文第一部分对于select count(*) from scott.t1 where object_id in (select object_id
from scott.t2)语句生成的两条sql plan baseline，作如下处理：删除走索引的那条plan（只保留FTS）->使用sql tuning advisor对语句进行调优->接受advisor使用索引访问的建议 ###人工删除掉走索引的sql
plan set
numformat 9999999999999999999999999 col
sql_handle format a20 col
creator format a5 col
sql_text format a50 col
created format a30 col
last_modified format a30 col
last_executed format a30 col
last_verified format a30 set
linesize 180 select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'; --删除其中使用索引的那条 set
serveroutput on declare result_int
pls_integer; begin result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11'); dbms_output.put_line(result_int); end; / --删除成功只剩一条FTS的plan select
sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'; ###执行dbms_sqltune，生成并接受优化建议 --生成tuning任务 declare my_task_name
varchar2(30); my_sqltext clob; begin my_sqltext:='select
count(*) from scott.t1 where object_id in (select object_id from scott.t2)'; my_task_name:=dbms_sqltune.create_tuning_task(sql_text=>my_sqltext,user_name=>'SCOTT',scope=>'COMPREHENSIVE',time_limit=>60,task_name=>'scott_sql_tune_1',description=>'tune
1'); end; / --执行tuning任务 begin dbms_sqltune.execute_tuning_task(task_name=>'scott_sql_tune_1'); end; / ###查看sqltune报告，截取了相关内容 set
long 9000 set
longchunksize 1000 set
linesize 800 select
dbms_sqltune.report_tuning_task('scott_sql_tune_1') from dual; 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1240933221 DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT |
| 1 | 9 |
462 (2)| 00:00:06 | | 1 | SORT AGGREGATE |
| 1 | 9 | | | |* 2 | HASH JOIN RIGHT SEMI| |
3 | 27 | 462
(2)| 00:00:06 | | 3 | TABLE ACCESS FULL | T2
| 99 | 297 |
5 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | T1
| 177K| 1042K|
455 (1)| 00:00:06 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1') 2- Using SQL Profile -------------------- Plan hash value: 2406492491 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 |
9 | 56 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 |
9 | | | | 2 | NESTED LOOPS | | 99 |
891 | 56 (2)| 00:00:01 | | 3 | SORT UNIQUE | | 99 |
297 | 5 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| T2 |
99 | 297 | 5
(0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 |
6 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- ###接受Advisor推荐走索引的Profile，同时可以看到dba_sql_plan_baseline里又增加了一条accepted=yes的plan，这条正是我们刚才删除的，表明接受dbms_sqltune的调优结果也可以实现sql plan baseline的演进 execute dbms_sqltune.accept_sql_profile(task_name=>'scott_sql_tune_1',task_owner=>'SCOTT',replace=>TRUE); select
sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'; ###验证已经新的sql
plan baseline已经被使用 SQL>
set autotrace traceonly explain SQL>select
count(*) from scott.t1 where object_id in (select object_id from scott.t2); Execution Plan ---------------------------------------------------------- Plan hash
value: 2406492491 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 |
9 | 56 (2)| 00:00:01 | | 1 |
SORT AGGREGATE | | 1 |
9 | | | | 2 |
NESTED LOOPS | | 99 |
891 | 56 (2)| 00:00:01 | | 3 |
SORT UNIQUE | | 99 |
297 | 5
(0)| 00:00:01 | | 4 |
TABLE ACCESS FULL| T2
| 99 | 297 |
5 (0)| 00:00:01 | |* 5 |
INDEX RANGE SCAN | IND_OBJID_T1
| 1 | 6 |
1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate
Information (identified by operation id): --------------------------------------------------- 5 -
access("OBJECT_ID"="OBJECT_ID") Note ----- - SQL profile
"SYS_SQLPROF_0146fae6b2110000" used for this statement - SQL plan baseline
"SQL_PLAN_d27ct6y4awk18b1b38b11" used for this statement 阶段总结： 方法(1)适用于已经存在于sql plan history里但还未被accepted的sql plan，可以通过optimizer验证(verify=yes)后实现演进，或者不通过验证(verify=no)而直接演进为sql plan baseline 方法(2)在不开启session级或system级自动捕捉(optimizer_capture_sql_plan_baselines=FALSE)的情况下，人工将已经生成的执行计划装载为sql plan baseline，即绕过optimizer的评估，直接演进为accepted plan的情况。这种方法需要人工确认该执行计划是一定是最优的，否则会导致后续按照该baseline执行的SQL产生性能问题 方法(3)语句出现性能问题后，求助sql tuning advisor得到并应用优化建议，生成accepted的sql plan baseline，属于事后调优的范畴 3、SQL语句对应的sql plan baseline均失效的情况下，sql
plan演进会跳过verify步骤，直接变为accepted ###Drop掉原有的sql plan
baseline declare result_int
pls_integer; begin result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828'); end; / ###重新构建测试环境 create
table scott.t1 tablespace ts_pub as select * from dba_objects; create
table scott.t2 tablespace ts_pub as select * from dba_objects where
rownum<100; create
index scott.ind_objid_t1 on scott.t1(object_id) tablespace ts_pub; exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE); exec
dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE); alter session set optimizer_capture_sql_plan_baselines=TRUE; select count(*) from scott.t1 where object_id in (select
object_id from scott.t2); --执行至少两次 alter session set optimizer_capture_sql_plan_baselines=FALSE; ###drop掉索引，再次执行sql，观察到dba_sql_plan_baselines里，索引对应的plan REPRODUCED变成了NO，受索引被drop的影响此条plan baseline失效了；同时新增了一条FTS的plan，但状态为not accepted drop
index scott.ind_objid_t1; select count(*) from scott.t1 where object_id in (select
object_id from scott.t2); select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'; ###现在把FTS的plan演进为Accepted sql plan baseline，从EVOLVE_SQL_PLAN_BASELINE函数的输出可以看出，虽然指定了verify=YES，但因走索引的plan已经失效，oracle并没有进行verify就直接accept此plan了。 set
serveroutput on set long
10000 declare result_clob
clob; begin result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af',verify=>'YES',commit=>'YES'); dbms_output.put_line(result_clob); end; / ------------------------------------------------------------------------------- Evolve SQL Plan
Baseline Report ------------------------------------------------------------------------- ------ Inputs: ------- SQL_HANDLE = SQL_d11d993788ae4828 PLAN_NAME
= SQL_PLAN_d27ct6y4awk1822a9c5af TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY
= YES COMMIT
= YES Plan: SQL_PLAN_d27ct6y4awk1822a9c5af ------------------------------------ Plan was not verified. Using cost-based plan
as could not reproduce any accepted and enabled baseline plan. Plan was changed to
an accepted plan. ------------------------------------------------------------------------- ------ Report Summary ------------------------------------------------------------------------ ------- Number of plans verified: 0 Number of plans accepted: 1 ###演进的结果验证，FTS 对应的sql plan
baseline已经变成Accepted=yes了 select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828' ###对于走索引的这条sql plan baseline，若要使其重新生效，即reproduced从NO变为YES，必须重新建立索引并且执行一次sql才行 select count(*) from scott.t1 where object_id in (select
object_id from scott.t2); create index scott.ind_objid_t1 on scott.t1(object_id) tablespace
ts_pub; exec
dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE); exec
dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
###仅通过Verify并不能使其重新生效，提示已经是accepted sql plan baseline set serveroutput
on set long
10000 declare result_clob
clob; begin result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11',verify=>'YES',commit=>'YES'); dbms_output.put_line(result_clob); end; / ------------------------------------------------------------------------------- Evolve SQL Plan
Baseline Report ------------------------------------------------------------------------- ------ Inputs: ------- SQL_HANDLE = SQL_d11d993788ae4828 PLAN_NAME
= SQL_PLAN_d27ct6y4awk18b1b38b11 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY
= YES COMMIT
= YES Plan: SQL_PLAN_d27ct6y4awk18b1b38b11 ------------------------------------ It is already an accepted plan. ------------------------------------------------------------------------- ------ Report Summary ------------------------------------------------------------------------ ------- There
were no SQL plan baselines that required processing. select
sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828' ###只有重新执行sql，reproduced才会变为YES，此外还可以观察到这两条有效的sql plan baseline的last_verified字段均为空，表明这两条sql plan入驻的时候都没有经过verify，也间接说明了入驻的当时没有有效的sql plan baseline存在，是被直接”保送”进了sql plan baseline select count(*) from scott.t1 where object_id
in (select object_id from scott.t2); select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'; 1、不同用户针对各自用户下的表，执行同一条sql语句， sql plan
baseline的共享机制 测试场景描述：两个用户scott1、scott2下各有一张名为t1的表，scott1.t1(object_id)上建立名为ind_objid_t的non-unique索引，且在scott1用户下执行select *
from t1 where object_id<100000生成首条sql plan baseline；之后分别在以下几种场景下使用Scott2用户执行同样的语句：select * from t1 where object_id<100000，观察是否能用到scott1用户生成的首条sql
plan baseline，这几种场景包括： (1) Scott2.t1(object_id)字段没有索引 (2) Scott2.t1(object_id)字段创建non-unique索引，索引名称和Scott1保持一致 (3) Scott2.t1(object_id)字段创建non-unique索引， 索引名称和Scott1保持一致，人工增大clustering_factor，使Optimizer偏向选择FTS (4) Scott2.t1(object_id)字段创建non-unique索引，索引名称有别于Scott1 (5) Scott2.t1(object_id)字段创建unique索引，索引名称和Scott1保持一致 (6) 重建Scott2.t1表，同时更改scott2.t1表结构，除了object_id字段外，其余字段均和Scott2.t1中的字段不相同 数据环境准备： ###生成scott1用户下的表 grant
connect,resource,unlimited tablespace to scott1 identified by scott1_1234; grant
plustrace to scott1; create
table scott1.t1 tablespace ts_pub as select * from dba_objects; create
index scott1.ind_objid_t on scott1.t1(object_id) tablespace ts_pub; exec
dbms_stats.gather_table_stats(ownname=>'scott1',tabname=>'t1',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE); ###生成scott2用户下的表 grant
connect,resource,unlimited tablespace to scott2 identified by scott2_5678; grant
plustrace to scott2; create
table scott2.t1 tablespace ts_pub as select * from dba_objects; exec dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE); ##清理现有环境中的sql plan baseline，保持dba_sql_plan_baseline为空 set
serveroutput on declare result_int
pls_integer; cursor
t_cur is select distinct sql_handle from dba_sql_plan_baselines; begin for v_cur
in t_cur loop result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>v_cur.sql_handle); dbms_output.put_line(result_int); end loop; end; / alter system flush shared_pool; ##scott1用户生成首条sql plan baseline， sqlplus scott1/scott1_1234 alter session set optimizer_capture_sql_plan_baselines=true; select * from t1 where object_id<100000; --执行至少两遍 alter session set optimizer_capture_sql_plan_baselines=false; select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines; select * from
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b2453067583')); --对应的执行计划是index
range scan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |
3560 | 337K| 213
(0)| 00:00:03 | | 1
| TABLE ACCESS BY INDEX ROWID| T1 |
3560 | 337K| 213
(0)| 00:00:03 | |* 2 |
INDEX RANGE SCAN |
IND_OBJID_T | 3560 | |
10 (0)| 00:00:01 | 场景(1):Scott2.t1(object_id)字段没有索引，Scott2用户执行select * from t1 where object_id<100000; select
* from t1 where object_id<100000; ###t1.object_id字段没有索引，无法用上Scott1用户下的baseline，但会把Scott1用户创建的plan变成reproduced=NO同时在sql plan
history里生成了一条FTS的plan，Creator为scott2，状态为not accepted select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines; select *
from
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b24dbd90e8e')); --plan_name= SQL_PLAN_93szh7uub7b24dbd90e8e执行计划如下 -------------------------------------------------------------------------------- SQL handle:
SQL_91e3f036b4b3ac44 SQL text:
select * from t1 where object_id<100000 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name:
SQL_PLAN_93szh7uub7b24dbd90e8e
Plan id: 3688435342 Enabled:
YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash
value: 838529891 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT |
| 3560 | 337K|
456 (1)| 00:00:06 | |* 1 |
TABLE ACCESS FULL| T1 | 3560 |
337K| 456 (1)| 00:00:06 | -------------------------------------------------------------------------- 阶段结论：scott2用户的t1表上没有索引，优化器为sql生成的执行计划无法与scott1用户创建的sql plan baseline匹配，所以只能采用FTS的访问路径添加到sql plan history，同时将scott1用户plan_name=SQL_PLAN_93szh7uub7b2453067583置为reproduced=NO。可见优化器在匹配sql plan baseline时依据的是sql_handle，和这个plan的creator无关。 场景(2):Scott2.t1(object_id)字段创建non-unique索引，索引名称和Scott1保持一致 ##接着场景(1)，在scott2.t1(object_id)创建和scott1同名的索引 create
index scott2.ind_objid_t on scott2.t1(object_id) tablespace ts_pub; exec
dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for
all columns size 1',cascade=>TRUE,no_invalidate=>FALSE); ##scott2执行sql，看到plan_name=SQL_PLAN_93szh7uub7b2453067583重新变为REPRODUCED=YES了，而且通过sql语句的执行计划可以看到plan_name=SQL_PLAN_93szh7uub7b2453067583重新被使用上了 set
autotrace traceonly select *
from t1 where object_id<100000; | Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time
| -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | |
3560 | 337K| 213
(0)| 00:00:03 | | 1 |
TABLE ACCESS BY INDEX ROWID| T1
| 3560 | 337K|
213 (0)| 00:00:03 | |* 2 |
INDEX RANGE SCAN |
IND_OBJID_T | 3560 | |
10 (0)| 00:00:01 | -------------------------------------------------------------------------------- ----------- Predicate
Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<100000) Note ----- - SQL plan baseline "SQL_PLAN_93szh7uub7b2453067583" used for this
statement select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines 阶段结论：scott2. t1表与scott2.t1完全相同，这个相同包括表结构、索引名称、统计信息等都和scott1.t1保持一致，所以生成的执行计划能完全匹配scott1走索引的plan_name，REPRODUCED重新置为YES 场景(3): Scott2.t1(object_id)字段创建non-unique索引， 索引名称和Scott1保持一致，人工增大clustering_factor，使Optimizer偏向选择FTS ##创建Scott2.t1(object_id)索引 。。。步骤同上，此处省略 ##先把optimizer_use_sql_plan_baselines设成false，观察一下未启用sql
plan baseline的情况下，改大scott2.t1 表索引的clustering_factor值，对执行计划的影响 ---修改前走的是index
range scan alter
session set optimizer_use_sql_plan_baselines=FALSE; select
table_name,index_name,clustering_factor from user_indexes where
table_name='T1'; TABLE_NAME INDEX_NAME CLUSTERING_FACTOR ------------------------------
------------------------------ -----------------
网站栏目：数据库中sqlplanbaseline怎么用
本文地址：http://cdxtjz.cn/article/jhhsjd.html