上篇中我們簡單介紹了Oracle 11g中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

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

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_bas TRUE 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_94z03q1wdq2ucc0c79166 AUTO-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中進行除錯。

在Oracle 11g中，支援我們從一個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。主要分為自動和手動兩種。預設情況下，Oracle 11g中關閉自動開關的，這個也是有相應的考慮。因為開啟SPM是在一些特殊的情況下才有優勢作用。

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

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

Oracle11g中SQL Plan Management （二）

相關文章