對資料庫系統而言，SQL語句的效能是每一個開發和運維DBA格外關注的細節點。前端應用效能中有很大部分是依賴資料庫SQL，特別是Query的執行效率。所以，對於資料庫調優而言，很大的功夫就在於進行SQL的調優。

SQL語句的執行是依賴最佳化器生成的執行計劃。執行計劃SQL Plan是真正獲取到結果集合的步驟方法。為SQL生成一個更“好”的執行計劃，是所有DBMS最佳化器的重要職責。

1、Oracle中的CBO和統計量困局

Oracle最佳化器Optimizer經過RBO到CBO時代的轉化，應該說是向著智慧化和靈活化實現了飛躍。從簡單的規則序列生成執行計劃，轉而參考資料物件的統計資訊。CBO透過統計資訊來進行執行計劃生成，依據成本cost對比來確定最優的執行計劃。

伴隨週期性的統計量收集，CBO的工作似乎無懈可擊。但是，很多時候，即使在CBO時代，SQL的執行計劃還是存在效能問題。注意，我們這裡並不涉及CBO本身演算法的缺陷。

最直接的症狀，我們經常可以聽到這樣的抱怨：原本一條SQL或者SP一直或者大部分時間執行良好，但是“時不時”速度變慢。聯絡DBA進行Tuning後，常常是重新收集一次統計量（增加直方圖或者提高取樣率），SQL執行恢復正常。之後，在某一個時間點，問題照舊。

出現這種問題的根本原因在於：後設資料中統計量資訊同實際資料表的差異導致了錯誤執行計劃的生成。導致不一致的原因很多，比如統計量收集（自動）時間點不合適、大批次資料操作後沒有及時收集或者統計量異常。

簡單的說，就是CBO對於統計量的變化過於“敏感”，而統計量收集又是和資料表資料不能做到完全同步。

對於週期性收集的統計量資訊，SQL執行計劃的偶然性蛻變似乎是不能解決的困局。因為我們不能做到每次執行SQL前都收集統計量。所以，我們需要做的就是避免異常SQL執行計劃的生成，對執行計劃生成進行控制。

從一些文獻看，Oracle對於統計量收集頻度問題是比較矛盾的。一種推薦的做法是設定假設，認為在一個時間段內，正常資料表的資料列分佈是穩定的，不會有大的變化。在一次穩定的統計量收集之後，就將統計量固定“Freeze”住。這樣就不會有一場SQL執行計劃的生成。

另一種做法，是藉助SQL BaseLine，儲存一份“健康”時刻的關鍵SQL執行計劃，作為一個留存。當出現緊急的SQL效能問題時，現將儲存的執行計劃baseline替換上去，支援系統執行。之後再分析出現問題的原因。

SQL執行計劃異常的常見場景還包括系統升級。不同版本的Oracle最佳化器，特別是CBO的變化很大，一個在舊版本中工作良好的SQL可能新版本中生成一個很有問題的執行計劃。所以，一些系統的移植升級過程中，現有執行計劃的保留移植也是重要的考慮因素之一。

在11g中，Oracle開始嘗試解決這種問題。11g推出了專門針對執行計劃管理的元件內容SPM（SQL Plan Management）。在Baseline技術基礎上提供了執行計劃自動、手動最佳化進化的功能。

SQL> select * from v$version;

BANNER

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

Oracle Database11gEnterpriseEdition Release11.2.0.1.0 - Production

PL/SQL Release11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 - Production

SQL> select parameter ,value from v$option where parameter like '%SQL%';

PARAMETER VALUE

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

SQL Plan Management TRUE

2、SPM執行計劃“三部曲”

我們說，SQL執行計劃是和統計量緊密相關，偶然性的統計量錯誤或者不完全是造成執行計劃Degrade的本質原因。SPM（SQL Plan Mangement）期望實現的在執行執行時能夠保證SQL效能不會存在退化的情況，而且可以保證不斷的推進執行計劃效能。

簡單的說：SPM保證只有那些被接受“Accepted”的可信執行計劃才會被使用到，其他生成的執行計劃都需要進行確認驗證“Verified”。只有那些之後驗證效能最佳化好的執行計劃才會最終被接受為執行。

SPM主要包括三個關鍵元件，分別扮演不同的角色：

ü SQL執行Baseline捕獲

對所有相關的SQL，都生成專門的SQL Baseline，儲存在資料庫中。注意，Baseline是佔用SYSAUX表空間的。

ü SQL Baseline選擇

在系統的執行時，Oracle要保證每次執行SQL的執行計劃都是使用SQL Baseline中的確定執行計劃。同時，跟蹤所有該statement執行中使用的新執行計劃，作為Plan Histroy資訊儲存下來。

Plan Histroy是有不同的狀態的。執行計劃中包括的狀態有“Accepted”和“Unaccepted”兩種。非接受的狀態SQL可能是由於收集捕獲之後沒有進行驗證“Unvertified”或者被拒絕“Rejected”。

ü SQL執行計劃Baseline進化

將Plan History中的未驗證“Unvertified”狀態的執行計劃進行判斷，判斷為“Accepted”或者“Rejected”。

從上面的介紹看，SPM本質上就是進行執行計劃不斷進化upgrade的工具元件。從Oracle10g開始，自動化和智慧化就開始滲透到Oracle各個體系環節。自適應元件的頻頻推出，將過去一些難以確定、經驗密集型的問題轉為到Oracle自己解決。SPM就是Oracle在執行計劃管理上的一個推進階段。

下面，我們分別介紹三個元件的使用和功能。

上篇中我們簡單介紹了Oracle11g中SPM（SQL Plan Management）。按照Oracle的意圖，使用SPM可以有效的對執行計劃進行管理，防止出現效能的Degrade現象。透過進化演進的過程，實現執行計劃逐步最佳化的目標。

3、SQL plan baseline capture

SPM（SQL Plan Management）的第一步，就是確定好進行控制管理的SQL語句，生成執行計劃Baseline。注意，生成的SQL Baseline，是基於當前版本的CBO最佳化器。由於各種原因，不同版本的CBO在執行計劃生成方面差異很大。所以，我們通常將執行計劃與特定的Oracle CBO版本繫結。

在收集執行計劃方面，有兩種方法選擇：執行計劃自動收集（Automatic Capture of Execution Plan）和執行計劃批次載入（Bulk load execution plan）。

ü Automatic Capture of Execution Plan

自動收集執行計劃的功能是Oracle11g的一個可控制元件。

SQL> select parameter ,value from v$option where parameter like '%SQL%';

PARAMETER VALUE

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

SQL Plan Management TRUE

預設情況下，該元件是不工作的。我們可以透過初始化引數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE設定為True，開啟自動SQL Plan Baseline收集功能。該引數的預設值為false。

SQL> show parameter capture_sql_plan

NAME TYPE VALUE

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

optimizer_capture_sql_plan_baselines boolean FALSE

該引數能夠在會話和例項級別進行改寫。

SQL> select name,ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from v$parameter where name like 'optimizer_capture_sql%';

NAME ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE

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

optimizer_capture_sql_plan_basTRUE IMMEDIATE TRUE

elines

SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

Session altered

SQL> select name,value from v$parameter where name like 'optimizer_capture_sql%';

NAME VALUE

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

optimizer_capture_sql_plan_baselines TRUE

當啟動了自動執行計劃Baseline捕獲之後，Oracle會啟用SPM儲存庫（SPM Repository），將會話範圍（Session Level）或者例項範圍（System level）發出的所有SQL語句記錄在SQL History裡面。

在Baseline中，包括了執行SQL的語句、執行計劃、CBO版本等資訊。當一個新的SQL語句出現時，Oracle會將其記錄在Baseline中，作為第一個“Accepted”的執行計劃。

例如，剛剛在會話層面開啟了自動收集，之後該會話所有發出的SQL，都會進入執行記錄收集階段。

SQL> select signature, sql_handle, sql_text,PLAN_NAME,ORIGIN,version, ACCEPTED from dba_sql_plan_baselines;

SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION ACCEPTED

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

1.05553156 SYS_SQL_927c03b078db0b4c select name,value from v$para SQL_PLAN_94z03q1wdq2ucc0c79166AUTO-CAPTURE 11.2.0.1.0 YES

meter where name like 'optimiz

er_capture_sql%'

如果要檢視Baseline對應的執行計劃，可以使用dbms_xplan的對應工具包檢視。

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

PLAN_TABLE_OUTPUT

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

SQL handle: SYS_SQL_927c03b078db0b4c

SQL text: select name,value from v$parameter where name like

'optimizer_capture_sql%'

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

Plan name: SQL_PLAN_94z03q1wdq2ucc0c79166 Plan id: 3234304358

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

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

Plan hash value: 1128103955

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

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

| 0 | SELECT STATEMENT | | 1 | 2115 | 1 (100)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 2115 | 1 (100)| 00:00:01 |

|* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |

| 3 | FIXED TABLE FULL| X$KSPPCV | 100 | 198K| 0 (0)| 00:00:01 |

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

32 rows selected

當一個SQL的Baseline已經存在，再次執行這個SQL時，如果有新的執行計劃被生成，SQL History中會記錄上這個執行計劃並且被判定為等待驗證的Plan。

ü Bulk Load批次載入

SQL效能出現抖動問題一個主要場景就是在資料庫升級Upgrade後，由於CBO版本的變化，引起執行計劃的變化。此時可以針對一部分SQL語句的執行計劃進行控制管理，建立SQL Baseline。

建立批次Load的方法目前有三種：SQL Tuning Set、從當前Cursor Cache中獲取還有就是從外部資料表中進行載入。

SQL Tuning Set（STS）

SQL Tuning Set是Oracle提供的一種進行自動化SQL調優的工具。當我們發現或者Oracle的自動Tunning Advice發現一個SQL執行有效能問題時，是可以放置在一個Tunning Set中進行除錯。

在Oracle11g中，支援我們從一個SQL Tunning Set中將一個SQL的執行計劃載入為SQL Plan Baseline。我們可以透過Oracle EM或者PL/SQL指令碼實現Tunning Set到Baseline的轉換。

FUNCTION load_plans_from_sqlset( sqlset_name IN VARCHAR2,

sqlset_owner IN VARCHAR2 := NULL,

basic_filter IN VARCHAR2 := NULL,

fixed IN VARCHAR2 := 'NO',

enabled IN VARCHAR2 := 'YES',

commit_rows IN NUMBER := 1000

)

RETURN PLS_INTEGER;

輸入SQL Tunning Set的名稱和其他資訊，就可以實現將其轉化為Baseline。從實際操作上看，使用SQL Tunning Set轉化為Baseline的場景還是很多的。當我們的資料庫一部分SQL出現問題的時候，可以專門對其進行監管。

從Cursor Cache中載入

每條生成的SQL語句執行計劃，都會以shared cursor組成部分的形式儲存在library cache中。如果有一個SQL執行計劃存在於Cursor Cache中，就可以將它直接轉化為baseline。

輸入shared cursor的model_name、schema和SQL_ID，呼叫DBMS_SPM包方法，就可以將指定SQL的執行計劃載入為Baseline。

SQL> select count(*) from dba_sql_plan_baselines;

COUNT(*)

----------

0

SQL> select sql_text, sql_id, plan_hash_value from v$sql where sql_text like 'select /* demo_test */count(*)%';

SQL_TEXT SQL_ID PLAN_HASH_VALUE

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

select /* demo_test */count(*) cd4g0jbf2m5wj 2966233522

from t

SQL> var i number;

SQL> exec :i := dbms_spm.load_plans_from_cursor_cache (sql_id => 'cd4g0jbf2m5wj' , plan_hash_value =>'2966233522');

PL/SQL procedure successfully completed

i

---------

1

SQL> select sql_handle, sql_text, plan_name, origin from dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN

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

SYS_SQL_4bfcd2ee36110fce select /* demo_test */count(*)SQL_PLAN_4rz6kxsv123yf3fdbb376 MANUAL-LOAD

from t

上面程式碼演示瞭如何從cursor cache中獲取到指定的一個SQL執行計劃作為Baseline。

從外部資料表中載入執行計劃作為Baseline

對很多應用系統，特別是執行復雜SQL的應用程式，在資料庫層面經常有很多除錯好的執行計劃。當系統部署或者升級的時候，這些執行計劃是保證系統高效能執行的重要保證。

SPM允許執行計劃Baseline從一個系統中打包為一個table，在轉移到另一個系統中進行釋放。這樣實現了從一個Table載入Baseline計劃。具體執行過程如下：

首先，在原有的系統中，我們使用dbms_spm.create_stgtab_baseline建立Baseline容納的資料表。

SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT');

PL/SQL procedure successfully completed

SQL> desc scott.baseline_test;

Name Type Nullable Default Comments

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

VERSION NUMBER Y

SIGNATURE NUMBER Y

SQL_HANDLE VARCHAR2(30) Y

OBJ_NAME VARCHAR2(30) Y

OBJ_TYPE VARCHAR2(30) Y

PLAN_ID NUMBER Y

（篇幅原因，有省略…）

注意，在SYS下建立stage table，通常會報錯。

SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST');

begin dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST'); end;

ORA-19381:無法在SYS方案中建立登臺表

ORA-06512:在"SYS.DBMS_SMB", line 313

ORA-06512:在"SYS.DBMS_SPM", line 2940

ORA-06512:在line 1

之後，可以使用dbms_spm.pack_stgtab_baseline方法將指定（或者全部）Baseline儲存在Stage Table中。

SQL> select count(*) from scott.baseline_test;

COUNT(*)

----------

0

SQL> var i number;

SQL> exec :i := dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TEST', table_owner => 'SCOTT');

PL/SQL procedure successfully completed

i

---------

1

SQL> select count(*) from scott.baseline_test;

COUNT(*)

----------

1

在資料表中，儲存了匯出的baseline資訊，之後可以藉助資料匯出工具將資料錶轉移到一個新系統中（這裡我們將原有Baseline刪除，用於模擬環境）。

--刪除Baseline

SQL> var i number;

SQL> exec :i := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_4bfcd2ee36110fce', plan_name => 'SQL_PLAN_4rz6kxsv123yf3fdbb376');

PL/SQL procedure successfully completed

i

---------

1

SQL> select sql_handle, sql_text, plan_name from dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT PLAN_NAME

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

當轉移到一個新系統之後，使用unpack_stgtab_baseline方法，將資料載入到系統Baseline中。

--重新載入Baseline

SQL> var i number;

SQL> exec :i := dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT');

PL/SQL procedure successfully completed

i

---------

1

SQL> select sql_handle, sql_text, plan_name from dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT PLAN_NAME

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

SYS_SQL_4bfcd2ee36110fce select /* demo_test */count(*) SQL_PLAN_4rz6kxsv123yf3fdbb376

from t

實現了將Baseline移植的操作過程，這個在升級等場景中意義重大。

上面我們詳細介紹了SPM的第一個元件內容——SQL Plan Baseline Capture。主要分為自動和手動兩種。預設情況下，Oracle11g中關閉自動開關的，這個也是有相應的考慮。因為開啟SPM是在一些特殊的情況下才有優勢作用。

所以，在實際使用時，還是推薦用Bulk Load的方式，針對一些特定的SQL進行SPM管理。

下面我們將繼續介紹SPM的第二個操作Plan Baseline Selection。

上面我們介紹瞭如果從各種源source中，將SQL執行計劃轉換為SQL Plan Baseline。本篇中我們繼續介紹Baseline的選擇和進化。

4、SQL Plan Management Selection

在CBO環境下，一個SQL的執行計劃過程如下：每次SQL語句被編譯後，最佳化器首先是根據CBO原則生成一個CBO執行計劃。接下來要檢查Baseline儲存庫（在sysaux表空間裡面），如果初始化引數optimizer_use_sql_plan_baseline設定為true（預設選項），在CBO執行計劃執行之前，Oracle會檢查一下SQL Plan Baseline儲存中有沒有匹配的執行計劃語句。

SQL> show parameter use_sql_plan;

NAME TYPE VALUE

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

optimizer_use_sql_plan_baselines boolean TRUE

如果找到相同的執行計劃，就直接執行該計劃。如果沒有與Baseline相同的執行計劃，新生成的執行計劃就會直接的加入到SQL History中，等待進一步確認為“Accepted”的Baseline。

如果產生的新的執行計劃，Oracle SPM的作用開始體現出來：首先，Oracle不會選擇新生成的執行計劃作為真實執行計劃，而且從Baseline中選擇出一個已經“Accepted”的執行計劃作為實際的執行計劃進行替換。

注意：同樣一條SQL語句，在Baseline中可能存在多條“Accepted”的執行計劃。此時，Oracle會選擇一條成本較低的執行計劃替換掉本次的執行計劃。但是，如果此前有資料字典內容的變化，如索引增加修改和刪除操作，那麼新生成的執行計劃就會被執行。

從多條相同語句Baseline中，選擇一條合適執行Baseline Plan的過程，我們稱之為：“costing plan”。這種選擇的過程，是消耗資源的。

下面我們進行簡單的實驗。

SQL> create table t asselect * from dba_objects where wner='SCOTT';

Table created

SQL> create index idx_t_owner on t(owner);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> select count(*) from t;

COUNT(*)

----------

15

我們構建了資料表T，此時雖然存在索引，但是以owner=’SCOTT’為條件時，還是會走索引路徑。

--正式執行該語句；

SQL> select /* spm_demo */* from t where wner='SCOTT';

（篇幅原因，結果集合略……）

15 rows selected

SQL> select sql_id, sql_text, PLAN_HASH_VALUE, executions, child_number from v$sql where sql_text like 'select /* spm_demo */* from%';

SQL_ID SQL_TEXT PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER

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

fmtuuwubyf8dq select /* spm_demo */* from t 1601196873 1 0

where wner='SCOTT'

SQL> select * from table(dbms_xplan.display_cursor('fmtuuwubyf8dq',0,'ALL'));

PLAN_TABLE_OUTPUT

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

SQL_ID fmtuuwubyf8dq, child number 0

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

select /* spm_demo */* from t where wner='SCOTT'

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | TABLE ACCESS FULL| T | 15 | 1230 | 2 (0)| 00:00:01 |

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

（篇幅原因，有省略）

35 rows selected

用SQL Baseline Capture的方法，將其Load如Baseline中。

SQL> var i number;

SQL> exec :i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'fmtuuwubyf8dq' , plan_hash_value =>'1601196873');

PL/SQL procedure successfully completed

i

---------

1

SQL> select sql_handle, sql_text, plan_name, ENABLED, ACCEPTED, FIXED, EXECUTIONS from dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS

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

SYS_SQL_244e0c2d91d5afb6 select /* spm_demo */* from t SQL_PLAN_28mhc5q8xbbxq94ecae5cYES YES NO 1

where wner='SCOTT'

注意：作為第一次載入到Baseline中的計劃，被設定為“Accepted”狀態，執行次數為1。

那麼，我們接下來簡單處理一下資料內容。

SQL> insert into t select * from dba_objects where owner != 'SCOTT';

72556 rows inserted

SQL> commit;

Commit complete

那麼此時，資料執行計劃應該是什麼？

--重新收集統計量，反映分佈變化；

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> explain plan for select * from t where wner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

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

| 0 | SELECT STATEMENT | | 26 | 2522 | 2 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 26 | 2522 | 2 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 26 | | 1 (0)|

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT')

14 rows selected

下面，執行該SQL。

SQL> select /* spm_demo */* from t where wner='SCOTT';

（結果集合省略……）

15 rows selected

SQL> select sql_handle,plan_name, ENABLED, ACCEPTED, FIXED, EXECUTIONS from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS

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

SYS_SQL_244e0c2d91d5afb6 SQL_PLAN_28mhc5q8xbbxq7f85a438 YES NO NO 0

SYS_SQL_244e0c2d91d5afb6 SQL_PLAN_28mhc5q8xbbxq94ecae5cYES YES NO 1

注意，此時生成了一個新的SQL Plan History，但是對應的狀態是非“ACCEPTED”，表明雖然新執行計劃生成，但是尚未透過驗證。執行的SQL執行計劃應該是已經被確認的全表掃描。

我們首先看一下新生成的Baseline對應的是何種執行計劃。

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

PLAN_TABLE_OUTPUT

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

SQL handle: SYS_SQL_244e0c2d91d5afb6

SQL text: select /* spm_demo */* from t where wner='SCOTT'

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

Plan name: SQL_PLAN_28mhc5q8xbbxq7f85a438 Plan id: 2139464760

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

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

Plan hash value: 1516787156

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

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

| 0 | SELECT STATEMENT | | 26 | 2522 | 2 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 26 | 2522 | 2 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 26 | | 1 (0)|

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT')

25 rows selected

說明：當前SQL語句執行，在CBO下，的確是生成了新的執行計劃。但是由於是非“Accepted”計劃，所以作為Plan History儲存下來，等待確認。

Liberary Cache中情況如下，：

--父遊標情況

SQL> select sql_id, sql_text, PLAN_HASH_VALUE, executions, version_count from v$sqlarea where sql_text like 'select /* spm_demo */* from%';

SQL_ID SQL_TEXT PLAN_HASH_VALUE EXECUTIONS VERSION_COUNT

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

fmtuuwubyf8dq select /* spm_demo */* from t 1601196873 3 3

where wner='SCOTT'

--子游標情況

SQL> select sql_id, sql_text, PLAN_HASH_VALUE, executions, child_number from v$sql where sql_text like 'select /* spm_demo */* from%';

SQL_ID SQL_TEXT PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER

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

fmtuuwubyf8dq select /* spm_demo */* from t 1601196873 1 0

where wner='SCOTT'

fmtuuwubyf8dq select /* spm_demo */* from t 1601196873 2 2

where wner='SCOTT'

兩個執行計劃分別為：

--獲取到child_number=0的執行計劃；

SQL> select * from table(dbms_xplan.display_cursor('fmtuuwubyf8dq'));

PLAN_TABLE_OUTPUT

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

SQL_ID fmtuuwubyf8dq, child number 0

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

select /* spm_demo */* from t where wner='SCOTT'

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | TABLE ACCESS FULL| T | 15 | 1230 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SCOTT')

18 rows selected

--獲取到child_number=2的執行計劃

SQL> select * from table(dbms_xplan.display_cursor('fmtuuwubyf8dq',2));

PLAN_TABLE_OUTPUT

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

SQL_ID fmtuuwubyf8dq, child number 2

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

select /* spm_demo */* from t where wner='SCOTT'

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 283 (100)| |

|* 1 | TABLE ACCESS FULL| T | 26 | 2522 | 283 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SCOTT')

Note

-----

- SQL plan baseline SQL_PLAN_28mhc5q8xbbxq94ecae5cused for this statement

22 rows selected

上面的結果可以告訴我們幾點內容：

首先，child_number=0的那個執行計劃，是我們第一次執行SQL快取在其中的。只有一次執行次數。這個是正常的。而child_number=2的執行計劃，雖然都是走的FTS全表掃描，但是依據的是Baseline生成的執行計劃，而且那個Baseline就是我們“Accepted”的那個Baseline對應的。

其次，我們也可以知道Baseline工作的一個小細節，雖然使用Baseline是共用執行計劃，但是共用的是Baseline之間的執行計劃。第一次用Baseline生成的執行計劃，還是走硬解析過程。

最後，Baseline的作用下，新的執行計劃不會貿然的被採用，但是會被生成儲存下來。進而等待確認。這樣，起碼保證SQL的執行計劃不會一個不可控的狀態，效能抖動degrade的機率有所降低。

此外，還有其他的因素影響到selection的過程，就是Baseline中的fixed列。當多個相同語句的Baseline存在時，Oracle要進行“costing”過程，這個取值會影響到costing過程。被標記為Fixed的執行計劃在進行Baseline選擇的時候，要受到最高的優先順序。如果多個Baseline中只有一個Baseline是Fixed狀態，且該計劃可以執行，那麼Oracle最終會選擇這個計劃。否則就會進行“costing”過程，找出一個相對成本較小的一個作為執行計劃。

在實踐中，我們通常要避免出現“costing”過程，這個過程對系統的負載壓力很大。所以我們通常希望有一條可控的執行路徑針對特定的SQL。

上面講述了SQL Baseline Selection的過程，下面是最後的執行計劃進化和其他一些相關問題。

ORACLE11G中SQL計劃管理

相關文章