上篇中我们简单介绍了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
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 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。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17203031/viewspace-710197/ ，如需转载，请注明出处，否则将追究法律责任。
