数据库中sql plan baseline怎么用

这篇文章主要介绍数据库中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
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个字段CREATEDLAST_MODIFIEDLAST_EXECUTEDLAST_VERIFIED ###开启session级的sql capture，自动生成首条sql plan baseline–session 1，设置Session级的captureSQL>
select * from dba_sql_plan_baselines;no rows selectedalter 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，再次执行一遍sqlselect count(*) from scott.t1 where object_id in (select
object_id from scott.t2);–session 2，dba_sql_plan_baselines产生了首条sql plan baseline，首条初始状态就是acceptedselect 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 PMLAST_MODIFIED：02-JUL-14 02.37.20.000000 PMLAST_EXECUTED：02-JUL-14 02.37.20.000000 PMLAST_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对应的执行计划为FTSselect * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>’SQL_d11d993788ae4828′,plan_name=>’SQL_PLAN_d27ct6y4awk1822a9c5af’));PLAN_TABLE_OUTPUT——————————————————————————————————————————————————————————————————————————————————————————SQL
handle: SQL_d11d993788ae4828SQL text:
select count(*) from scott.t1 where object_id in (select object_id from scott.t2)—————————————————————————————————————————————————————-Plan
name: SQL_PLAN_d27ct6y4awk1822a9c5af
Plan id: 581551535Enabled:
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字段上创建索引，再次执行sqlcreate
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 a20col
creator format a5col
sql_text format a50col
created format a30col
last_modified format a30col
last_executed format a30col
last_verified format a30set
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的存在，走的依然是FTSset
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
statementStatistics———————————————————- 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 acceptedset
serveroutput onset
long 10000declareresult_clob
clob;beginresult_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
BaselineReport——————————————————————————-Inputs:——- SQL_HANDLE = SQL_d11d993788ae4828 PLAN_NAME
=SQL_PLAN_d27ct6y4awk18b1b38b11 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY
=YES COMMIT
= YESPlan:SQL_PLAN_d27ct6y4awk18b1b38b11———————————— Plan wasverified:
Time used .901 seconds. Plan passed
performance criterion: 232.77times
better than baseline plan. Plan was changed to an accepted plan.Baseline
Plan Test Plan Stats Ratio————- ——— ———– Execution Status:COMPLETE COMPLETE Rows Processed: 11 Elapsed Time(ms): 59.641 .298 200.14CPU
Time(ms): 34.444 0 Buffer Gets:2557 11 232.45 Physical Read Requests: 00 Physical Write Requests: 0 0 Physical ReadBytes: 0 0 Physical Write Bytes:0 0 Executions: 11——————————————————————————- ReportSummary——————————————————————————-Number of plans verified: 1Number of plans accepted: 1PL/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 PMLAST_MODIFIED: 02-JUL-14 03.44.10.000000 PMLAST_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
LoopSQL>
select * from
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>’SQL_d11d993788ae4828′,plan_name=>’SQL_PLAN_d27ct6y4awk18b1b38b11′));PLAN_TABLE_OUTPUT—————————————————————————————————————————————————————-SQL
handle: SQL_d11d993788ae4828SQL text:
select count(*) from scott.t1 where object_id in (select object_id from scott.t2)—————————————————————————————————————————————————————-Plan
name: SQL_PLAN_d27ct6y4awk18b1b38b11
Plan id: 2981333777PLAN_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演进过程中将notaccetped的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 poolvariable
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>

###显示执行计划

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

——————————————————————————–

—

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

——————————————————————————–

—

——————————————————————————–

—

| 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’)

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–

——————————————————————————

——————————————————————————

| 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

————————————————————————————-

————————————————————————————-

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

————————————————————————————-

————————————————————————————-

| 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

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

(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

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

select
* from t1 where object_id

###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

——————————————————————————–

Plan name:
SQL_PLAN_93szh7uub7b24dbd90e8e
Plan id: 3688435342

Enabled:
YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE

——————————————————————————–

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash
value: 838529891

————————————————————————–

————————————————————————–

| 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

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”

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

——————————
—————————— —————–

T1 IND_OBJID_T 10126

set
autotrace traceonly

select *
from t1 where object_id

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 |

—修改后走的是fts

exec
dbms_stats.set_index_stats(ownname=>’SCOTT2′,indname=>’IND_OBJID_T’,clstfct=>2000000);

select
table_name,index_name,clustering_factor from user_indexes where
table_name=’T1′;

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR

——————————
—————————— —————–

T1 IND_OBJID_T 2000000

set
autotrace traceonly

select *
from t1 where object_id

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT |
| 3560 | 337K|
456 (1)| 00:00:06 |

|* 1 |
TABLE ACCESS FULL| T1 | 3560 |
337K| 456 (1)| 00:00:06 |

————————————————————————–

##optimizer_use_sql_plan_baselines置为true，观察在启用sql
plan baseline的情况下，在IND_OBJID_T索引统

计信息改变之后，oracle是否还会继续去启用plan_name=SQL_PLAN_93szh7uub7b2453067583这条走索引

的plan

–为使结果更为明朗，这里先删除掉scott2用户在场景(1)里创建出的走FTS的plan

set
serveroutput on

declare

result_int
pls_integer;

begin

result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SQL_91e3f036b4b3ac44′,plan_name=>’SQL_PLAN_93szh7uub7b24dbd90e8e’);

dbms_output.put_line(result_int);

end;

–只剩一条走索引的plan= SQL_PLAN_93szh7uub7b2453067583

Select

sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRO

DUCED from
dba_sql_plan_baselines;

–scott2用户执行sql，plan= SQL_PLAN_93szh7uub7b2453067583会被启用

alter
session set optimizer_use_sql_plan_baselines=TRUE;

set
autotrace traceonly

select *
from t1 where object_id

Time
|

——————————————————————————–

———–

| 0 | SELECT STATEMENT | |
3560 | 337K| 40066 (1)|

00:08:01 |

| 1 |
TABLE ACCESS BY INDEX ROWID| T1 |
3560 | 337K| 40066 (1)|

00:08:01 |

|* 2 |
INDEX RANGE SCAN |
IND_OBJID_T | 3560 | |
10 (0)|

00:00:01 |

——————————————————————————–

———–

Predicate
Information (identified by operation id):

—————————————————

2 – access(“OBJECT_ID”

Note

—–

– SQL plan baseline “SQL_PLAN_93szh7uub7b2453067583” used for this
statement

—但同时也会生成一个FTS的plan，clustering_factor值远大于table所占用的blocks的情况下，、优化器认为

FTS才是合适的选择

select

sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRO

DUCED from
dba_sql_plan_baselines

阶段结论：只要sql plan baseline的reproduced!=NO，就一定会被优化器选中，哪怕这条baseline

对应的执行计划效率再差。与此同时优化器执行sql时还是要去收集所执行对象的统计信息，

并且把它计算出的执行计划添加到sql plan history作为演进时的候选对象。

场景(4): Scott2.t1(object_id)字段创建non-unique索引，索引名称有别于Scott1

##修改Scott2.ind_objid_t索引名称

alter
index scott2.IND_OBJID_T rename to IND_OBJID_T2;

exec
dbms_stats.gather_table_stats(ownname=>’scott2′,tabname=>’t1′,method_opt=>’for
all columns size

1′,cascade=>TRUE,no_invalidate=>FALSE);

##重新执行sql，得到了不同的执行计划(这里的不同主要是指索引名称的改变，访问的路径还是index range

scan)，结果是在dba_sql_plan_baseline里新增了1条plan_name=SQL_PLAN_93szh7uub7b2483309cfd，与此

同时还发现scott1用户下的plan_name=
SQL_PLAN_93szh7uub7b2453067583 reproduced属性变为NO，原

因是索引名称变了匹配不上了，即IND_OBJID_T !=IND_OBJID_T2

set
autotrace traceonly

select
* from t1 where object_id

| 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_T2 | 3560 |
| 10 (0)

| 00:00:01 |

阶段结论：虽然我们平时关注的主要是执行计划中的access-path部分，但其实索引名称也是执行计划的重要组成部分也是决定sql plan baseline能否被重用的一个重要因素

场景(5): Scott2.t1(object_id)字段创建unique索引，索引名称和Scott1保持一致

##scott2重建索引，名称和scott1名称等同，但索引类型变为unique

–先Drop掉creator=scott2的两条sql plan

set
serveroutput on

declare

result_int1
pls_integer;

result_int2
pls_integer;

begin

result_int1:=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SQL_91e3f036b4b3ac44′,plan_name=>’SQL_PLAN_93szh7uub7b24dbd90e8e’);

result_int2:=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SQL_91e3f036b4b3ac44′,plan_name=>’SQL_PLAN_93szh7uub7b2483309cfd’);

dbms_output.put_line(result_int1);

dbms_output.put_line(result_int2);

end;

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上的索引

drop index
scott2.ind_objid_t2;

create
unique 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观察到scott1用户的plan_name=SQL_PLAN_93szh7uub7b2453067583还是能够被利用

set
autotrace traceonly

select *
from t1 where object_id

Time
|

——————————————————————————–

———–

| 0 | SELECT STATEMENT | |
3560 | 337K| 212
(0)|

00:00:03 |

| 1 |
TABLE ACCESS BY INDEX ROWID| T1
| 3560 | 337K| 212
(0)|

00:00:03 |

|* 2 |
INDEX RANGE SCAN |
IND_OBJID_T | 3560 | |
9 (0)|

00:00:01 |

——————————————————————————–

———–

Predicate
Information (identified by operation id):

—————————————————

2 – access(“OBJECT_ID”

Note

—–

– SQL plan baseline “SQL_PLAN_93szh7uub7b2453067583” used for this
statement

阶段结论：虽然这次索引变成了unique的，但执行计划中并没有使用index unique scan，用的依然是index range scan，这就和plan=SQL_PLAN_93szh7uub7b2453067583所指向的access-path保持一致，说明只要在access-path，索引名称相同的情况下，oracle不会对索引是否为unique有强制的要求

场景(6): 重建Scott2.t1表，同时更改scott2.t1表结构，除了object_id字段外，其余字段均和Scott2.t1中的字段不相同

##重构Scott2.t1表

drop table scott2.t1;

create table scott2.t1 (col1
varchar2(2),object_id number,col3 varchar2(100)) tablespace ts_pub;

declare

begin

for i in 1..170000 loop

insert into scott2.t1
values(‘AA’,i,’scott2.t1′);

end loop;

commit;

end;

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);

##scott用户执行sql，sql plan
baseline能够被重用

set autotrace traceonly

select * from t1 where
object_id

Time
|

——————————————————————————–

———–

|
0 | SELECT STATEMENT
| | 100K|
1757K| 545 (1)|

00:00:07 |

|
1 | TABLE ACCESS BY INDEX ROWID|
T1 | 100K|
1757K| 545 (1)|

00:00:07 |

|*
2 | INDEX RANGE SCAN | IND_OBJID_T | 100K|
| 225 (1)|

00:00:03 |

——————————————————————————–

———–

Predicate Information (identified
by operation id):

—————————————————

2 – access(“OBJECT_ID”

Note

—–

– SQL plan baseline “SQL_PLAN_93szh7uub7b2453067583” used for
this statement

阶段结论：只要执行计划能完全匹配上，就能利用到已生成的sql plan
baseline，对于表结构，

表内容等项目oracle不作检查，可见sql plan baseline对环境的适应能力是很强的，除了对象不可用之外(例如索引被删除)，都能将预先生成的执行计划提供给优化器执行。

以上是“数据库中sql plan baseline怎么用”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注百云行业资讯频道！

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>