本文共 27552 字,大约阅读时间需要 91 分钟。
###再次执行sql时已经能用到了这条sql
plan baseline了
variable
v_objid number;
exec
:v_objid:=500;
select
count(*) from scott.t1 where object_id
set
autotrace traceonly;
select
count(*) from scott.t1 where object_id
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_93szh6uub7b2453067583'));--对应的执行计划是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_93szh6uub7b24dbd90e8e'));--plan_name= SQL_PLAN_93szh6uub7b24dbd90e8e执行计划如下
--------------------------------------------------------------------------------
SQL handle:
SQL_91e3f036b4b3ac44
SQL text:
select * from t1 where object_id<100000
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name:
SQL_PLAN_93szh6uub7b24dbd90e8e
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_93szh6uub7b2453067583置为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_93szh6uub7b2453067583重新变为REPRODUCED=YES了,而且通过sql语句的执行计划可以看到plan_name=SQL_PLAN_93szh6uub7b2453067583重新被使用上了
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_93szh6uub7b2453067583" 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<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 |
---修改后走的是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<100000;
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
##optimizer_use_sql_plan_baselines置为true,观察在启用sql
plan baseline的情况下,在IND_OBJID_T索引统
计信息改变之后,oracle是否还会继续去启用plan_name=SQL_PLAN_93szh6uub7b2453067583这条走索引
的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_93szh6uub7b24dbd90e8e');
dbms_output.put_line(result_int);
end;
/
--只剩一条走索引的plan= SQL_PLAN_93szh6uub7b2453067583
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_93szh6uub7b2453067583会被启用
alter
session set optimizer_use_sql_plan_baselines=TRUE;
set
autotrace traceonly
select *
from t1 where object_id<100000;
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
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"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh6uub7b2453067583" 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_93szh6uub7b2483309cfd,与此
同时还发现scott1用户下的plan_name=
SQL_PLAN_93szh6uub7b2453067583 reproduced属性变为NO,原
因是索引名称变了匹配不上了,即IND_OBJID_T !=IND_OBJID_T2
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_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_93szh6uub7b24dbd90e8e');
result_int2:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh6uub7b2483309cfd');
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_93szh6uub7b2453067583还是能够被利用
set
autotrace traceonly
select *
from t1 where object_id<100000;
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
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"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh6uub7b2453067583" used for this
statement
阶段结论:虽然这次索引变成了unique的,但执行计划中并没有使用index unique scan,用的依然是index range scan,这就和plan=SQL_PLAN_93szh6uub7b2453067583所指向的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<100000;
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
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"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh6uub7b2453067583" used for
this statement
阶段结论:只要执行计划能完全匹配上,就能利用到已生成的sql plan
baseline,对于表结构,
表内容等项目oracle不作检查,可见sql plan baseline对环境的适应能力是很强的,除了对象不可用之外(例如索引被删除),都能将预先生成的执行计划提供给优化器执行。
转载地址:https://blog.csdn.net/weixin_33137081/article/details/116409598 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!