上面我們介紹瞭如果從各種源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 as select * 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_28mhc5q8xbbxq94ecae5c YES 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_28mhc5q8xbbxq94ecae5c YES 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_28mhc5q8xbbxq94ecae5c used 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 Plan Management （三）

相關文章