SQL Plan Baseline與Shared Cursor機制研究（一）

執行計劃不穩定，出現偶發性的惡化現象，一直是困擾CBO應用和運維人員的一個重要問題。一直以來，Oracle一直試圖採用外部固定的方法來固化應用程式SQL語句。從最早的Hint，到11g的SPM（SQL Plan Management）。這些方法都在不同的層面上幫助我們解決Oracle效能問題。

SPM是Oracle在11g推出的執行計劃固定工具。相對於較早版本中的SQL Profile和Hint（outline），SPM無論是從功能上還是使用上，有很多優勢。在筆者之前的系列中，專門對SPM進行過介紹。

SPM實際上是從CBO的層面上影響最終的執行計劃決策。在SPM的工作迴圈中，CBO優化器、記憶體shared cursor機制和SPM管理模組相互協作，實現執行計劃的固定。本文重點在於研究SPM機制啟動的時候，三個模組的工作協調方式。

1、環境準備

我們選擇Oracle 11gR2作為實驗環境。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

SPM相關引數保持在預設狀態下。

SQL> show parameter baseline;

NAME TYPE VALUE

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

optimizer_capture_sql_plan_baselines boolean FALSE

optimizer_use_sql_plan_baselines boolean TRUE

建立實驗資料表T，構建索引。

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 /*+ SPM DEMO*/* from t where wner='SCOTT';

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 12 | 936 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 12 | 936 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

注意，此時因為資料表中資料都是owner=’scott’，所以走FTS是可以接受的。

2、自動捕獲執行計劃作為Baseline

SPM建立Baseline兩種方式，手工新增Baseline或者自動收集捕獲。我們實現選擇自動捕獲方法。

--Session Level啟動自動捕獲

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

會話已更改。

--執行兩次

SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';

（結果集合略）

注意，當啟動自動SPM收集的時候，相同的SQL只有重複執行兩次以上，才會生成Baseline。

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

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS

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

SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0

我們看到，Oracle已經生成了一個初始Baseline，是通過自動捕獲方法生成的。Enable和Accepted的狀態都是Yes，說明是一個生效的Baseline。

此時，我們檢查下shared pool中的狀態資訊。

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT

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

3kn82wdfqh9j3 2 1

SQL> select sql_id, child_number, sql_plan_baseline from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';

SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE

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

3kn82wdfqh9j3 0

注意，Oracle要求執行兩次之後才會生成Baseline。所以，此時Library Cache中包括一個父遊標和對應的一個子遊標。並且這個遊標不是對應執行計劃的。

我們從shared pool中抽取這個語句的執行計劃，檢視資訊。

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '3kn82wdfqh9j3',format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 3kn82wdfqh9j3, 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 | 12 | 936 | 2 (0)| 00:00:01 |

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

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

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

1 - SEL$1 / T@SEL$1

Outline Data

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

（篇幅原因，有省略……）

49 rows selected

注意，我們抽取的執行計劃中，並沒有標註baseline的說明。這就說明，當自動生成baseline的時候，原始的執行計劃沒有被變化。

3、重複執行SQL實驗

此時，如果我們重複執行這個SQL語句，會如何呢？Oracle是否會使用生成的Baseline執行計劃，還是執行相同的shared cursor。

--重複執行，結果省略

SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';

--SQL PLAN Baseline情況

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

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS

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

SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0

---shared pool情況

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT

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

3kn82wdfqh9j3 3 2

SQL> select sql_id, child_number, sql_plan_baseline,executions from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';

SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS

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

3kn82wdfqh9j3 0 2

3kn82wdfqh9j3 1 SQL_PLAN_6datc7mtkmzvc94ecae5c 1

從結果看，當我們再次執行這個SQL的時候，Oracle發現已經構建了Baseline，就會按照Plan Baseline去工作。此時如果shared cursor中存在非Baseline的執行計劃子游標，Oracle會去建立一個新的子游標。

此時，我們檢視這個共享遊標的執行計劃。

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '3kn82wdfqh9j3',format => 'advanced',cursor_child_no => 1));

PLAN_TABLE_OUTPUT

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

SQL_ID 3kn82wdfqh9j3, child number 1

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

select /*+ SPM DEMO*/* from t where wner='SCOTT'

Plan hash value: 1601196873

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

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

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

|* 1 | TABLE ACCESS FULL| T | 12 | 936 | 2 (0)| 00:00:01 |

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

（篇幅原因，有省略……）

Note

-----

- SQL plan baseline SQL_PLAN_6datc7mtkmzvc94ecae5c used for this statement

53 rows selected

注意，標識說明，Baseline執行計劃正在被處理。此時，可以先關閉收集引數。

SQL> alter session set optimizer_capture_sql_plan_baselines=false;

會話已更改。

4、全新清空shared pool下的實驗狀態

如果我們此時清空shared pool，全新進行baseline的處理。

SQL> alter system flush shared_pool;

System altered

SQL> alter system flush buffer_cache;

System altered

重新執行SQL，理論是可以和現有的SQL Baseline相匹配。

--執行一次

SQL> select /*+ SPM DEMO*/* from t where wner='SCOTT';

--SPM過程

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

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED AUTOPURGE EXECUTIONS

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

SYS_SQL_66ab2c3cf329ff6c SQL_PLAN_6datc7mtkmzvc94ecae5c YES YES NO YES 0

--Shared Pool中情況

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+ SPM DEMO*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT

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

3kn82wdfqh9j3 1 1

SQL> select sql_id, child_number, sql_plan_baseline,executions from v$sql where sql_text like 'select /*+ SPM DEMO*/* from t%';

SQL_ID CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS

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

3kn82wdfqh9j3 0 SQL_PLAN_6datc7mtkmzvc94ecae5c 1

但我們重新生成shared pool物件的時候，Oracle選擇出了和baseline一致的結果。所以，我們可以得知：當最優執行計劃和SPM中的baseline相吻合的時候，會在shared pool中形成對應的父子游標。子游標中標註著SQL Plan Baseline資訊。

Shared Cursor情況如下：

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '3kn82wdfqh9j3',format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 3kn82wdfqh9j3, 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 | 12 | 936 | 2 (0)| 00:00:01 |

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

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

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

1 - SEL$1 / T@SEL$1

Outline Data

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

PLAN_TABLE_OUTPUT

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T"@"SEL$1")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

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

Column Projection Information (identified by operation id):

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

1 - "OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128],

PLAN_TABLE_OUTPUT

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

"T"."SUBOBJECT_NAME"[VARCHAR2,30], "T"."OBJECT_ID"[NUMBER,22],

"T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19],

"T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7],

"T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],

"T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1],

"T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22],

"T"."EDITION_NAME"[VARCHAR2,30]

Note

-----

- SQL plan baseline SQL_PLAN_6datc7mtkmzvc94ecae5c used for this statement

53 rows selected

那麼，這個決策過程是怎麼樣的呢？下篇我們計劃使用10053進行CBO決策跟蹤過程。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-755198/，如需轉載，請註明出處，否則將追究法律責任。

SQL Plan Baseline與Shared Cursor機制研究（一）

相關文章