###再次执行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对环境的适应能力是很强的，除了对象不可用之外(例如索引被删除)，都能将预先生成的执行计划提供给优化器执行。