ITPub博客

首页 > 数据库 > Oracle > sql plan baseline使用心得

sql plan baseline使用心得

原创 Oracle 作者:oliseh 时间:2014-10-20 17:06:43 0 删除 编辑

测试内容:

1、 dba_sql_plan_baselines表中和时间有关的四个字段CREATEDLAST_MODIFIEDLAST_EXECUTEDLAST_VERIFIED的变化规律

2、 候选sql plan变为accepted sql plan baseline的几种方法

3、 SQL语句对应的sql plan baseline均失效的情况下Optimizer将新生成的执行计划演进为sql plan baseline的过程

4、 不同用户针对各自用户下的表,执行同一条sql语句, sql plan baseline的共享机制

 

建立测试用表:

grant connect,resource,unlimited tablespace to scott identified by sdfg_1234;

 

create table scott.t1 tablespace ts_pub as select * from dba_objects;

 

create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum<100;

 

exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);

 

exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);

 

 

1、 dba_sql_plan_baselines表中和时间有关字段的变化规律,涉及到以下4个字段

CREATED

LAST_MODIFIED

LAST_EXECUTED

LAST_VERIFIED

 

###开启session级的sql capture,自动生成首条sql plan baseline

--session 1,设置Session级的capture

SQL> select * from dba_sql_plan_baselines;

  

no rows selected

 

alter system optimizer_capture_sql_plan_baselines=TRUE;

 

select count(*) from scott.t1 where object_id in (select object_id from scott.t2);

 

--session 2dba_sql_plan_baselines中没有记录,因为上述sql只执行了一次

select sql_handle,sql_text,plan_name,creator,last_modified,last_executed,last_verified from dba_sql_plan_baselines;

 

--session 1,再次执行一遍sql

select count(*) from scott.t1 where object_id in (select object_id from scott.t2);

 

--session 2dba_sql_plan_baselines产生了首条sql plan baseline,首条初始状态就是accepted

select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;



###上述结果中的时间点字段值,last_verified值为空,因为其是这条sql生成的首条baseline所以没有经过验证;因为是新建的sql plan baseline其余三个时间字段值都一样

CREATED02-JUL-14 02.37.20.000000 PM

LAST_MODIFIED02-JUL-14 02.37.20.000000 PM

LAST_EXECUTED02-JUL-14 02.37.20.000000 PM

LAST_VERIFIEDNULL

 

###上述结果中的时间点字段值,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,观察时间字段状态,CREATEDLAST MODIFIED两个字段值没有变化,这个可以理解,LAST_EXECUTED值应该变化为最近一次的执行时间,但事实却没有变化,即使alter system flush shared_pool以后重新执行语句,也没有变化


select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;


###通过DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE查看sql_plan_baseline对应的执行计划为FTS

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

SQL handle: SQL_d11d993788ae4828

SQL text: select count(*) from scott.t1 where object_id in (select object_id from

          scott.t2)

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_d27ct6y4awk1822a9c5af         Plan id: 581551535

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

--------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Plan hash value: 1240933221

 

------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |     1 |     9 |   462   (2)| 00:00:06 |

|   1 |  SORT AGGREGATE       |      |     1 |     9 |            |          |

|*  2 |   HASH JOIN RIGHT SEMI|      |     3 |    27 |   462   (2)| 00:00:06 |

|   3 |    TABLE ACCESS FULL  | T2   |    99 |   297 |     5   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | T1   |   177K|  1042K|   455   (1)| 00:00:06 |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"="OBJECT_ID")

 

28 rows selected.

 

###t1表的object_id字段上创建索引,再次执行sql

create index scott.ind_objid_t1 on scott.t1(object_id) tablespace ts_pub;

 

exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);

 

exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);

 

###dba_sql_plan_baselines里又生成了一条plan_name= SQL_PLAN_d27ct6y4awk18b1b38b11(sql_handle与前一条相同的sql),但没有被acceptedbaseline,这条记录的CREATEDLAST_MODIFIED字段表明了该条baseline的创建时间,LAST_EXECUTEDLAST_VERIFIED均为空值

   col sql_handle format a20

col creator format a5

col sql_text format a50

col created        format a30

col last_modified  format a30

col last_executed  format a30

col last_verified  format a30

set linesize 190

   set pagesize 200

 


   select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;


 

###执行sql,虽然有索引,但因为baseline的存在,走的依然是FTS

set autotrace traceonly;

 

SQL> select count(*) from scott.t1 where object_id in (select object_id from scott.t2);

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1240933221

 

------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |     1 |     9 |   462   (2)| 00:00:06 |

|   1 |  SORT AGGREGATE       |      |     1 |     9 |            |          |

|*  2 |   HASH JOIN RIGHT SEMI|      |     3 |    27 |   462   (2)| 00:00:06 |

|   3 |    TABLE ACCESS FULL  | T2   |    99 |   297 |     5   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | T1   |   177K|  1042K|   455   (1)| 00:00:06 |

------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"="OBJECT_ID")

 

Note

-----

   - SQL plan baseline "SQL_PLAN_d27ct6y4awk1822a9c5af" used for this statement

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       2557  consistent gets

       2556  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

###人工演进sql plan baseline,根据Buffer Get优化前后的对比2557/11=232.45,得出使用索引的sql plan baseline所获得的性能是FTS232倍,oracle情况下根据隐含参数_plan_verify_improvement_margin(默认值为150,表示1.5)的值决定性能达到原先多少倍时accept新的sql plan baseline,此例中已经达到了232被,所以当让是verified and accepted

set serveroutput on

set long 10000

declare

result_clob clob;

begin

result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11',verify=>'YES',commit=>'YES');

dbms_output.put_line(result_clob);

end;

/

 

 

-------------------------------------------------------------------------------

 

                        Evolve SQL Plan Baseline

Report

-------------------------------------------------------------------------

------

 

Inputs:

-------

  SQL_HANDLE = SQL_d11d993788ae4828

  PLAN_NAME  =

SQL_PLAN_d27ct6y4awk18b1b38b11

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY     =

YES

  COMMIT     = YES

 

Plan:

SQL_PLAN_d27ct6y4awk18b1b38b11

------------------------------------

  Plan was

verified: Time used .901 seconds.

  Plan passed performance criterion: 232.77

times better than baseline plan.

  Plan was changed to an accepted plan.

 

 

 

 

Baseline Plan      Test Plan       Stats Ratio

 

 

-------------      ---------       -----------

  Execution Status:

COMPLETE       COMPLETE

  Rows Processed:                       1

1

  Elapsed Time(ms):                59.641           .298            200.14

 

 

CPU Time(ms):                    34.444              0

  Buffer Gets:

2557             11            232.45

  Physical Read Requests:               0

0

  Physical Write Requests:              0              0

  Physical Read

Bytes:                  0              0

  Physical Write Bytes:

0              0

  Executions:                           1

1

 

-----------------------------------------------------------------------------

--

                                 Report

Summary

------------------------------------------------------------------------

-------

Number of plans verified: 1

Number of plans accepted: 1

 

 

 

PL/SQL procedure successfully completed.

 

###查看PLAN_NAME=SQL_PLAN_d27ct6y4awk18b1b38b11对应sql plan baselineLAST_VERIFIED

LAST_MODIFIED为同一个时间,LAST_VERIFIED表示在这个时间完成了Verify动作,LAST_MODIFIED表示在

Verify通过后将此baselinenot accepted变为accepted的时间。

CREATED: 02-JUL-14 03.22.41.000000 PM

LAST_MODIFIED: 02-JUL-14 03.44.10.000000 PM

LAST_VERIFIED:02-JUL-14 03.44.10.000000 PM

 

###执行该SQL后发现last_executed时间已经是最新的时间了

SQL> select count(*) from scott.t1 where object_id in (select object_id from scott.t2);

 


select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;

LAST_EXECUTED:02-JUL-14 04.25.33.000000 PM

 

###dbms_xplan.display_sql_plan_baseline显示Plan_name=SQL_PLAN_d27ct6y4awk18b1b38b11的执行计划,这次采用的是Nest Loop

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

SQL handle: SQL_d11d993788ae4828

SQL text: select count(*) from scott.t1 where object_id in (select object_id fro

m

 

          scott.t2)

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_d27ct6y4awk18b1b38b11         Plan id: 2981333777

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

 

Plan hash value: 2406492491

 

--------------------------------------------------------------------------------

-----

 

| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time

    |

 

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-----

 

|   0 | SELECT STATEMENT     |              |     1 |     9 |    56   (2)| 00:00

:01 |

 

|   1 |  SORT AGGREGATE      |              |     1 |     9 |            |

    |

 

|   2 |   NESTED LOOPS       |              |    99 |   891 |    56   (2)| 00:00

:01 |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

|   3 |    SORT UNIQUE       |              |    99 |   297 |     5   (0)| 00:00

:01 |

 

|   4 |     TABLE ACCESS FULL| T2           |    99 |   297 |     5   (0)| 00:00

:01 |

 

|*  5 |    INDEX RANGE SCAN  | IND_OBJID_T1 |     1 |     6 |     1   (0)| 00:00

:01 |

 

--------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   5 - access("OBJECT_ID"="OBJECT_ID")

  

阶段总结:

CREATEDsql plan生成到plan_history的时间(可以是accept或者not accept状态)

LAST_MODIFIEDsql plan上一次修改的时间,这个修改时间反映了sql plan演进过程中将not

accetpedsql plan更新为accepted动作发生的时间,也能反映使用alter_sql_plan_baseline

对于sql plan任何属性更改的时间

LAST_VERIFIEDsql 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中已经存在的执行计划loadbaseline,且状态变为accepted

###执行sql,使其cacheshared pool

variable v_objid number;

exec :v_objid:=1000;

select count(*) from scott.t1 where object_id<:v_objid;

SQL> select sql_text,sql_id,child_number,plan_hash_value from v$sql where sql_text like 'select count(*) from scott.t1%';

 

SQL_TEXT                                                                                   SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE

------------------------------------------------------------------------------------------ ------------- ------------ ---------------

select count(*) from scott.t1 where object_id<:v_objid                                     9hup7n51za19u            0      4020739011

 

###显示执行计划

select * from table(dbms_xplan.display_cursor(sql_id=>'9hup7n51za19u',cursor_child_no=>0,format=>'ALL'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  9hup7n51za19u, child number 0

-------------------------------------

select count(*) from scott.t1 where object_id<:v_objid

 

Plan hash value: 4020739011

 

--------------------------------------------------------------------------------

--

 

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time

 |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

--

 

|   0 | SELECT STATEMENT  |              |       |       |     5 (100)|

 |

 

|   1 |  SORT AGGREGATE   |              |     1 |     6 |            |

 |

 

|*  2 |   INDEX RANGE SCAN| IND_OBJID_T1 |  8893 | 53358 |     5   (0)| 00:00:01

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 |

 

--------------------------------------------------------------------------------

--

 

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   2 - SEL$1 / T1@SEL$1

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"<:V_OBJID)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) COUNT(*)[22]

 

###shared pool中将上述sql的执行计划loadsql plan baselineload进来之后就变成了Accepted,没有verify的过程

set serveroutput on

declare

result_int pls_integer;

begin

result_int:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'9hup7n51za19u',plan_hash_value=>4020739011,fixed=>'NO',enabled=>'YES');

dbms_output.put_line(result_int);

end;

/

 

###dba_sql_plan_baselines中找到了该条sql plan baseline,已经被accepted

select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_text like '%v_objid';

###再次执行sql时已经能用到了这条sql plan baseline

variable v_objid number;

exec :v_objid:=500;

 

select count(*) from scott.t1 where object_id<:v_objid;

set autotrace traceonly;

select count(*) from scott.t1 where object_id<:v_objid;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4020739011

 

--------------------------------------------------------------------------------

--

 

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time

 |

 

--------------------------------------------------------------------------------

--

 

|   0 | SELECT STATEMENT  |              |     1 |     6 |     5   (0)| 00:00:01

 |

 

|   1 |  SORT AGGREGATE   |              |     1 |     6 |            |

 |

 

|*  2 |   INDEX RANGE SCAN| IND_OBJID_T1 |  8893 | 53358 |     5   (0)| 00:00:01

 |

 

--------------------------------------------------------------------------------

--

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"

 

Note

-----

   - SQL plan baseline "SQL_PLAN_gm8nknf6mhghn28a6f5d9" used for this statement

 

 

Statistics

----------------------------------------------------------

         27  recursive calls

         16  db block gets

         15  consistent gets

         13  physical reads

       3136  redo size

        527  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

(3)     通过dbms_sqltuneSQL语句进行调优,并接受其调优建议

这里沿用本文第一部分对于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;

/

--删除成功只剩一条FTSplan


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=yesplan,这条正是我们刚才删除的,表明接受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里但还未被acceptedsql 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得到并应用优化建议,生成acceptedsql 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失效了;同时新增了一条FTSplan,但状态为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';

###现在把FTSplan演进为Accepted sql plan baseline,从EVOLVE_SQL_PLAN_BASELINE函数的输出可以看出,虽然指定了verify=YES,但因走索引的plan已经失效,oracle并没有进行verify就直接acceptplan了。

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,若要使其重新生效,即reproducedNO变为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'

###只有重新执行sqlreproduced才会变为YES,此外还可以观察到这两条有效的sql plan baselinelast_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的共享机制

测试场景描述:两个用户scott1scott2下各有一张名为t1的表,scott1.t1(object_id)上建立名为ind_objid_tnon-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里生成了一条FTSplanCreatorscott2,状态为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,和这个plancreator无关。

场景(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_nameREPRODUCED重新置为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)里创建出的走FTSplan

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用户执行sqlplan= 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

 

---但同时也会生成一个FTSplanclustering_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 baselinereproduced!=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里新增了1plan_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

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



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1304933/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617085