測試內容：

1、 dba_sql_plan_baselines表中和時間有關的四個欄位CREATED，LAST_MODIFIED，LAST_EXECUTED，LAST_VERIFIED的變化規律

2、 候選sql plan變為accepted sql plan baseline的幾種方法

3、 SQL語句對應的sql plan baseline均失效的情況下Optimizer將新生成的執行計劃演進為sql plan baseline的過程

4、 不同使用者針對各自使用者下的表，執行同一條sql語句， sql plan baseline的共享機制

建立測試用表：

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

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

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

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

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

1、 dba_sql_plan_baselines表中和時間有關欄位的變化規律，涉及到以下4個欄位

CREATED

LAST_MODIFIED

LAST_EXECUTED

LAST_VERIFIED

###開啟session級的sql capture，自動生成首條sql plan baseline

--session 1，設定Session級的capture

SQL> select * from dba_sql_plan_baselines;

no rows selected

alter system optimizer_capture_sql_plan_baselines=TRUE;

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

--session 2，dba_sql_plan_baselines中沒有記錄，因為上述sql只執行了一次

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

--session 1，再次執行一遍sql

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

--session 2，dba_sql_plan_baselines產生了首條sql plan baseline，首條初始狀態就是accepted

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

###上述結果中的時間點欄位值，last_verified值為空，因為其是這條sql生成的首條baseline所以沒有經過驗證；因為是新建的sql plan baseline其餘三個時間欄位值都一樣

CREATED：02-JUL-14 02.37.20.000000 PM

LAST_MODIFIED：02-JUL-14 02.37.20.000000 PM

LAST_EXECUTED：02-JUL-14 02.37.20.000000 PM

LAST_VERIFIED：NULL

###上述結果中的時間點欄位值，last_verified值為空，因為其是這條sql生成的首條baseline所以沒有經過

--session 1，第三次執行sql，執行前關閉sql capture引數

alter session set optimizer_capture_sql_plan_baselines=FALSE;

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

--session 2，觀察時間欄位狀態，CREATED、LAST MODIFIED兩個欄位值沒有變化，這個可以理解，LAST_EXECUTED值應該變化為最近一次的執行時間，但事實卻沒有變化，即使alter system flush shared_pool以後重新執行語句，也沒有變化

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

###透過DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE檢視sql_plan_baseline對應的執行計劃為FTS

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

PLAN_TABLE_OUTPUT

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

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

SQL handle: SQL_d11d993788ae4828

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

scott.t2)

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

Plan name: SQL_PLAN_d27ct6y4awk1822a9c5af Plan id: 581551535

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

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1240933221

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

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

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

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

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

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

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

PLAN_TABLE_OUTPUT

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

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

Predicate Information (identified by operation id):

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

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

28 rows selected.

###t1表的object_id欄位上建立索引，再次執行sql

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

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

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

###dba_sql_plan_baselines裡又生成了一條plan_name= SQL_PLAN_d27ct6y4awk18b1b38b11(sql_handle與前一條相同的sql)，但沒有被accepted的baseline，這條記錄的CREATED、LAST_MODIFIED欄位表明了該條baseline的建立時間，LAST_EXECUTED、LAST_VERIFIED均為空值

col sql_handle format a20

col creator format a5

col sql_text format a50

col created format a30

col last_modified format a30

col last_executed format a30

col last_verified format a30

set linesize 190

set pagesize 200

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

###執行sql，雖然有索引，但因為baseline的存在，走的依然是FTS

set autotrace traceonly;

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

Execution Plan

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

Plan hash value: 1240933221

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

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

Note

-----

- SQL plan baseline "SQL_PLAN_d27ct6y4awk1822a9c5af" used for this statement

Statistics

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

0 recursive calls

0 db block gets

2557 consistent gets

2556 physical reads

0 redo size

526 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

###人工演進sql plan baseline，根據Buffer Get最佳化前後的對比2557/11=232.45，得出使用索引的sql plan baseline所獲得的效能是FTS的232倍，oracle情況下根據隱含引數_plan_verify_improvement_margin(預設值為150，表示1.5倍)的值決定效能達到原先多少倍時accept新的sql plan baseline，此例中已經達到了232被，所以當讓是verified and accepted

set serveroutput on

set long 10000

declare

result_clob clob;

begin

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

dbms_output.put_line(result_clob);

end;

/

-------------------------------------------------------------------------------，

Evolve SQL Plan Baseline

Report

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

------

Inputs:

-------

SQL_HANDLE = SQL_d11d993788ae4828

PLAN_NAME =

SQL_PLAN_d27ct6y4awk18b1b38b11

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY =

YES

COMMIT = YES

Plan:

SQL_PLAN_d27ct6y4awk18b1b38b11

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

Plan was

verified: Time used .901 seconds.

Plan passed performance criterion: 232.77

times better than baseline plan.

Plan was changed to an accepted plan.

Baseline Plan Test Plan Stats Ratio

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

Execution Status:

COMPLETE COMPLETE

Rows Processed: 1

1

Elapsed Time(ms): 59.641 .298 200.14

CPU Time(ms): 34.444 0

Buffer Gets:

2557 11 232.45

Physical Read Requests: 0

0

Physical Write Requests: 0 0

Physical Read

Bytes: 0 0

Physical Write Bytes:

0 0

Executions: 1

1

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

--

Report

Summary

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

-------

Number of plans verified: 1

Number of plans accepted: 1

PL/SQL procedure successfully completed.

###檢視PLAN_NAME=SQL_PLAN_d27ct6y4awk18b1b38b11對應sql plan baseline，LAST_VERIFIED和

LAST_MODIFIED為同一個時間，LAST_VERIFIED表示在這個時間完成了Verify動作，LAST_MODIFIED表示在

Verify透過後將此baseline從not accepted變為accepted的時間。

CREATED: 02-JUL-14 03.22.41.000000 PM

LAST_MODIFIED: 02-JUL-14 03.44.10.000000 PM

LAST_VERIFIED:02-JUL-14 03.44.10.000000 PM

###執行該SQL後發現last_executed時間已經是最新的時間了

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

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

LAST_EXECUTED:02-JUL-14 04.25.33.000000 PM

###用dbms_xplan.display_sql_plan_baseline顯示Plan_name=SQL_PLAN_d27ct6y4awk18b1b38b11的執行計劃，這次採用的是Nest Loop

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

PLAN_TABLE_OUTPUT

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

SQL handle: SQL_d11d993788ae4828

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

m

scott.t2)

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

Plan name: SQL_PLAN_d27ct6y4awk18b1b38b11 Plan id: 2981333777

PLAN_TABLE_OUTPUT

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

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

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

Plan hash value: 2406492491

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

-----

|

PLAN_TABLE_OUTPUT

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

-----

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

:01 |

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

|

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

:01 |

PLAN_TABLE_OUTPUT

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

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

:01 |

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

:01 |

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

:01 |

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

PLAN_TABLE_OUTPUT

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

-----

Predicate Information (identified by operation id):

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

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

階段總結：

CREATED：sql plan生成到plan_history的時間(可以是accept或者not accept狀態)

LAST_MODIFIED：sql plan上一次修改的時間，這個修改時間反映了sql plan演進過程中將not

accetped的sql plan更新為accepted動作發生的時間，也能反映使用alter_sql_plan_baseline

對於sql plan任何屬性更改的時間

LAST_VERIFIED：sql plan最後一次被驗證的時間，同一個plan被驗證一遍之後如果再重複進

行驗證，時間還是停留在首次驗證的時間；第一條sql plan自動成為sql plan baseline時其

last_verified時間為空，說明其沒有經過verify，即使後續對首條sql plan人工進行演進，其last_verified時間依然為空

LAST_EXECUTED：名義上為最後一次執行的時間，實際測下來定格在首次執行的時間，後續

的執行並不會更新

2、 使sql plan變為accepted sql plan baseline的幾種方法

(1) 呼叫Dbms_spm.evolve_sql_plan_baseline函式，需要人工呼叫(在12c版本里已經引入sql plan evolve advisor能實現自動演進sql plan baseline)，這個是最常用的方法，只做如下說明：

其中Verify=yes表示經過optimizer驗證

verify=no表示不經過optimizer驗證強制變為accepted狀態

(2) 呼叫Dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE或者LOAD_PLANS_FROM_SQLSET函式，這裡使用LOAD_PLANS_FROM_CURSOR_CACHE函式將shared pool中已經存在的執行計劃load到baseline，且狀態變為accepted；

###執行sql，使其cache到shared pool

variable v_objid number;

exec :v_objid:=1000;

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

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

SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE

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

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

###顯示執行計劃

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

PLAN_TABLE_OUTPUT

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

SQL_ID 9hup7n51za19u, child number 0

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

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

Plan hash value: 4020739011

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

--

|

PLAN_TABLE_OUTPUT

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

--

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

|

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

|

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

PLAN_TABLE_OUTPUT

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

|

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

--

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

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

1 - SEL$1

2 - SEL$1 / T1@SEL$1

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"<:V_OBJID)

Column Projection Information (identified by operation id):

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

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

###從shared pool中將上述sql的執行計劃load到sql plan baseline，load進來之後就變成了Accepted，沒有verify的過程

set serveroutput on

declare

result_int pls_integer;

begin

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

dbms_output.put_line(result_int);

end;

/

###在dba_sql_plan_baselines中找到了該條sql plan baseline，已經被accepted

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

###再次執行sql時已經能用到了這條sql plan baseline了

variable v_objid number;

exec :v_objid:=500;

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

set autotrace traceonly;

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

Execution Plan

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

Plan hash value: 4020739011

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

--

|

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

--

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

|

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

|

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

|

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

--

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"<TO_NUMBER(:V_OBJID))

Note

-----

- SQL plan baseline "SQL_PLAN_gm8nknf6mhghn28a6f5d9" used for this statement

Statistics

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

27 recursive calls

16 db block gets

15 consistent gets

13 physical reads

3136 redo size

527 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

(3) 透過dbms_sqltune對SQL語句進行調優，並接受其調優建議

這裡沿用本文第一部分對於select count(*) from scott.t1 where object_id in (select object_id from scott.t2)語句生成的兩條sql plan baseline，作如下處理：刪除走索引的那條plan（只保留FTS）->使用sql tuning advisor對語句進行調優->接受advisor使用索引訪問的建議

###人工刪除掉走索引的sql plan

set numformat 9999999999999999999999999

col sql_handle format a20

col creator format a5

col sql_text format a50

col created format a30

col last_modified format a30

col last_executed format a30

col last_verified format a30

set linesize 180

select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';

--刪除其中使用索引的那條

set serveroutput on

declare

result_int pls_integer;

begin

result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11');

dbms_output.put_line(result_int);

end;

/

--刪除成功只剩一條FTS的plan

select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';

###執行dbms_sqltune，生成並接受最佳化建議

--生成tuning任務

declare

my_task_name varchar2(30);

my_sqltext clob;

begin

my_sqltext:='select count(*) from scott.t1 where object_id in (select object_id from scott.t2)';

my_task_name:=dbms_sqltune.create_tuning_task(sql_text=>my_sqltext,user_name=>'SCOTT',scope=>'COMPREHENSIVE',time_limit=>60,task_name=>'scott_sql_tune_1',description=>'tune 1');

end;

/

--執行tuning任務

begin

dbms_sqltune.execute_tuning_task(task_name=>'scott_sql_tune_1');

end;

/

###檢視sqltune報告，擷取了相關內容

set long 9000

set longchunksize 1000

set linesize 800

select dbms_sqltune.report_tuning_task('scott_sql_tune_1') from dual;

1- Original With Adjusted Cost

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

Plan hash value: 1240933221

DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')

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

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

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')

2- Using SQL Profile

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

Plan hash value: 2406492491

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

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')

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

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

###接受Advisor推薦走索引的Profile，同時可以看到dba_sql_plan_baseline裡又增加了一條accepted=yes的plan，這條正是我們剛才刪除的，表明接受dbms_sqltune的調優結果也可以實現sql plan baseline的演進

execute dbms_sqltune.accept_sql_profile(task_name=>'scott_sql_tune_1',task_owner=>'SCOTT',replace=>TRUE);

select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';

###驗證已經新的sql plan baseline已經被使用

SQL> set autotrace traceonly explain

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

Execution Plan

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

Plan hash value: 2406492491

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

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

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

Note

-----

- SQL profile "SYS_SQLPROF_0146fae6b2110000" used for this statement

- SQL plan baseline "SQL_PLAN_d27ct6y4awk18b1b38b11" used for this statement

階段總結：

方法(1)適用於已經存在於sql plan history裡但還未被accepted的sql plan，可以透過optimizer驗證(verify=yes)後實現演進，或者不透過驗證(verify=no)而直接演進為sql plan baseline

方法(2)在不開啟session級或system級自動捕捉(optimizer_capture_sql_plan_baselines=FALSE)的情況下，人工將已經生成的執行計劃裝載為sql plan baseline，即繞過optimizer的評估，直接演進為accepted plan的情況。這種方法需要人工確認該執行計劃是一定是最優的，否則會導致後續按照該baseline執行的SQL產生效能問題

方法(3)語句出現效能問題後，求助sql tuning advisor得到並應用最佳化建議，生成accepted的sql plan baseline，屬於事後調優的範疇

3、 SQL語句對應的sql plan baseline均失效的情況下，sql plan演進會跳過verify步驟，直接變為accepted

###Drop掉原有的sql plan baseline

declare

result_int pls_integer;

begin

result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828');

end;

/

###重新構建測試環境

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

create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum<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

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

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

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

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;

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;

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

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

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

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;

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

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;

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對環境的適應能力是很強的，除了物件不可用之外(例如索引被刪除)，都能將預先生成的執行計劃提供給最佳化器執行。

sql plan baseline使用心得

相關文章