SHOUG成员 – ORACLE ACS高级顾问罗敏
我们经常会遇到应用软件在开发测试环境和生产环境性能不一致的问题，以及升级之后应用性能衰减等问题。除去管理、实施、沟通、协调等因素，一个重要原因就在于不同环境绝对隔离，相互之间缺乏有效的关联技术。例如开发测试环境和生产环境是相互独立的，升级前后的系统也是相互独立的。
11g新的性能管理工具SPM就是这种不同环境之间的关联技术。通过SPM，我们可以将开发测试环境和生产环境，以及升级前后的系统有效地关联起来，使得不同环境之间的应用软件性能不会出现衰减。
本章先从一些典型场景和问题着手，然后全面介绍SPM原理和运用过程，以及SPM的管理等技术点，最后将介绍有关SPM的更多参考资料。
一些典型场景
- 典型场景１
“系统性能这么差，肯定是应用软件问题，这帮开发人员可能就没有好好测试，就直接投到生产系统了。”—- 负责运维的DBA经常这么抱怨道。
“我们的应用在开发和测试环境都跑得好好的，肯定是这帮DBA瞎改什么配置，搞得应用出了问题，特别是把语句执行计划搞得变了。”—- 应用开发团队一方面觉得委屈，另一方面又觉得问题可能是出在生产系统环境。
在很多大型企业，特别是国企，应用软件设计开发和系统运行维护分属两个相对独立的部门或团队，管理上的过于职责分明和缺乏有效沟通更加剧了这种分歧和对立。
- 典型场景2
日益发展的IT技术既给现有IT系统提供了更先进的平台和更广泛的技术，但系统升级、变更可能带来的风险，又让决策者们彷徨犹豫。终于升级到11g了，但是新系统却出现了性能衰减。于是，各方抱怨又纷至沓来：
“我们原来在10g跑得好好的，怎么一跑到11g就出现这么多问题，你们11g到底行不行啊？” —- 应用开发、运维等各方客户齐声抱怨道。
“我们不敢奢望11g比10g跑得快，但你们Oracle能不能保证我们的应用在11g下起码别比10g跑得慢啊？” —- 客户几乎是在哀求了。
“… …”
针对上述一些典型场景和客户需求，除了需要在项目管理、沟通协调等方面加强工作之外，Oracle在技术方面有什么招数吗？有！Oracle 11g的SPM（SQL Plan Management）就是解决上述问题的典型技术。
SPM原理
传统技术手段
众所周知，SQL语句执行性能好坏主要取决于语句执行计划，而Oracle优化器特别是基于成本优化器（CBO），主要依靠所访问表和其它对象的统计信息、优化器版本、优化器参数、系统硬件配置和参数设置、SQL Profile等信息，来综合分析并确定语句最佳执行计划。保证语句执行计划最优和稳定的一种重要手段就是统计信息的准确性和实时性，大部分DBA和开发人员也深知及时采集和更新统计信息的重要性。但确定执行计划的因素的确太多，例如上述的优化器版本、参数等信息，因此仅仅依靠统计信息还不能确保语句执行计划的最优化和稳定性。
Oracle传统上有哪些保证语句执行计划稳定性的技术呢？第一种就是在语句中增加提示（HINT），强制Oracle优化器采用某种固定的执行计划。另一种就是使用存储大纲（Stored Outline）技术，即将优化的执行计划提示信息存储在Oracle内部一组表格中，强制相关SQL语句使用这些存储大纲。这两种技术一个共同特点就是将相关SQL语句的执行计划固定下来，而不考虑未来环境变化，例如数据库版本升级之后是否会带来新的更优化的执行路径。
SQL Profile则是Oracle从10g开始提供的另一种确保语句执行计划最优化的技术。相比表和索引有统计信息，SQL Profile就是一条SQL语句的统计信息。例如，当我们遇到一个复杂且资源消耗非常大SQL语句时，Oracle可采用一些取样数据，或者可以执行该语句一个片段，以及分析该语句的历史执行情况，这些信息就是语句的SQL Profile。Oracle通过预先采集SQL Profile信息，来评估整体执行计划是否最优化。即当Oracle正式执行该SQL语句时，优化器不仅利用该语句所访问对象的统计信息，而且利用已产生的该语句SQL Profile信息，来产生整体上更优的执行计划。
11g SQL执行计划管理技术：SPM
在上述传统性能管理技术基础上，Oracle 11g推出了更先进的SQL执行计划管理技术：SPM，全称为SQL Plan Management技术。
SPM在原理上通过维护一个SQL执行计划的基线（Baseline），来自动控制SQL语句执行计划的演化过程。当启用SPM技术之后，优化器新产生的执行计划只有证明不会导致性能衰减时，才能加入到执行计划基线中。也就是说，只有基线中的执行计划才能被真正执行。如后所述，Oracle可以自动产生SQL语句的执行计划基线，也可以通过SQL Tuning Sets等手工方式产生。
SPM的最大好处就在于维持SQL语句的性能稳定性，避免性能衰减，同时也能有效减轻DBA工作负担，使得DBA们不用花费大量精力去识别、分析性能衰减问题并加以解决。
以下就是SPM原理图：
在启用SMP技术之后，当一个SQL语句被重复执行多遍，Oracle会将这些语句记录在语句日志（Statement log）中，优化器也会将这些语句的执行计划保存于执行计划历史（Plan History）记录中，包括SQL语句文本、Outline、绑定变量、编译环境信息等。当优化器新产生一个语句执行计划时，只有当Oracle通过验证该执行计划不会导致性能衰减之后，才会加入到Plan Baseline中并得到使用。而这种验证工作则是11g新的自动SQL优化任务（Automatic SQL Tuning Task）中的一部分，该任务将在每天晚上的维护窗口自动运行，并只针对资源消耗过大的SQL语句进行分析。该任务将自动分析这些新执行计划，并验证是否为可接受的（Accepted），同时加入Plan Baseline中。这样，Plan Baseline中的执行计划都是Accepted的。显然，一个语句的第一个执行计划构成最初的的Plan Baseline，但后续新产生的执行计划则只是保存在Plan History中，只有当验证性能不衰减之后，才能加入Plan Baseline。
如上图所示，Statement log、Plan history、Plan baseline和SQL Profile等都存储在SQL Management Base（简称SMB）中，SMB其实是数据字典一部分并存储在SYSAUX表空间中。针对SMB，Oracle提供了自动空间管理功能，例如保存期限、空间限额等，后面将详细介绍之。
SPM使用过程
SPM使用主要分为如下两种方式：
快速逐次捕获（On the fly capture）
通过设置初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为TRUE，将开启SPM的快速逐次捕获（On the fly capture）功能，该参数缺省值为FALSE。同时参数OPTIMIZER_USE_SQL_PLAN_BASELINES保持为缺省值TRUE。
如下图所示，当该参数设置为TRUE之后，Oracle将自动识别重复执行的SQL语句，并将记录在Plan History中。其中第一次执行的SQL语句执行计划将自动成为Plan Baseline。
批量加载（Bulk loading）
如下图所示，通过dbms_spm包，DBA可手工批量加载（Bulk loading）SQL语句的Plan Baseline。
具体而言，包括如下三种方式：
- 通过LOAD_PLANS_FROM_CURSOR_CACHE过程从cursor cache中加载。例如：
SQL> exec DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(<sql-id>);
- 通过LOAD_PLANS_FROM_SQLSET过程从STS（SQL tuning set）中加载。例如：
SQL> exec DBMS_SPM.LOAD_PLANS_FROM_SQLSET(‘mySQLTuningSet1’)
- 通过*_STGTAB_BASELINE过程创建一个中间表（Stage表），并通过Data Pump将这些表迁移到目标数据库中。
SPM适用场景
升级场景
以下是10g到11g中SPM的使用示意图:
以下是详细介绍使用过程:
首先在现有10g环境将捕获的SQL语句执行计划存储在STS 中，例如：
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET('STS102WKLD');
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'STS102WKLD',
time_limit => 120,
repeat_interval => 5);
END;
/
上述脚本首先创建一个名称为STS102WKLD的STS，其次采集时长为120秒，每隔5秒采集一次当前SQL语句执行计划于该 STS中。
然后执行如下脚本：
BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'STGTAB102'); DBMS_SQLTUNE.PACK_STGTAB_SQLSET( sqlset_name => 'STS102WKLD', staging_table_name => 'STGTAB102'); END; /
即先创建一个名为STGTAB102的中间表，并将STS102WKLD中的内容包装到STGTAB102中。
- 执行如下脚本:
$ exp system/manager file=STGTAB102.dmp tables=STGTAB102
$ imp system/manager file=STGTAB102.dmp full=y
即通过export/import将STGTAB102表内容导入到11g目标数据库中。
- 执行如下脚本:
DECLARE my_plans pls_integer; BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => 'STS102WKLD', sqlset_owner => '%', replace => TRUE, staging_table_name => 'STGTAB102'); my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'STS102WKLD', sqlset_owner => 'SYS', basic_filter => 'sql_text like ''%''', fixed => 'YES', enabled => 'YES', commit_rows => 1); END; /
上述脚本先将STGTAB102表打开并存储到名为STS102WKLD的STS中，然后再通过LOAD_PLANS_FROM_SQLSET过程将这些语句的执行计划导入到Plan Baseline中。
升级中SPM的进一步使用场景
以下是在升级过程中，SPM的进一步使用场景示意图：
以下是详细使用过程：
- 在升级到11g之后的初始阶段，例如一个季度内，将优化器参数optimizer_features_enable先保留为升级前的版本，如2.0.2，并运行相关应用，同时通过设置optimizer_capture_sql_plan_baselines=true，自动捕获相关语句在10.2.0.2优化器下生成的最佳执行计划，并加入到baseline中。
- 当升级到11g之后的初始阶段结束后，将optimizer_features_enable参数设置到新版本值，如2.0.1。这样，如果新版本优化器产生了衰减的执行计划，Oracle也会使用升级前优化器产生的执行计划，确保性能不衰减。而且，新版本优化器可能产生性能更好的SQL语句执行计划。
新软件开发场景
在11g环境下假设已经使用了SPM技术，可将SQL Plan Baselines数据在开发、测试和生产数据库之间进行迁移，确保性能的稳定。以下就是示意图:
以下就是详细使用过程：
- 在开发测试环境执行如下脚本：
DECLARE spm number; BEGIN DBMS_SPM.CREATE_STGTAB_BASELINE( table_name=>'SPM_SAVED', table_owner=>'SYSTEM'); spm := DBMS_SPM.PACK_STGTAB_BASELINE( table_name=>'SPM_SAVED', table_owner=>'SYSTEM'); END; /
即先创建一个名为SPM_SAVED的stage表，再将SQL Plan Baselines数据导入SPM_SAVED表。
- 在开发测试和生产环境分别执行如下脚本：
$ exp system/manager file=SPM_SAVED.dmp tables=SPM_SAVED
$ imp system/manager file=SPM_SAVED.dmp full=y
即通过export/import将SPM_SAVED表内容导入到11g生产数据库中。
- 在生产环境执行如下脚本：
DECLARE spm number; BEGIN spm := DBMS_SPM.UNPACK_STGTAB_BASELINE( table_name=>'SPM_SAVED', table_owner=>'SYSTEM'); END; /
即将SPM_SAVED表内容导入到11g生产数据库的SQL Plan Baselines中。
SPM相关技术点
SQL Plan Baseline的演化过程
当优化器产生一个语句的新执行计划，Oracle将该执行计划作为一个非接受（nonaccepted）执行计划保存在执行计划历史记录（Plan history）中。只有当该执行计划被验证不会导致性能衰减，才会加入到SQL Plan Baseline中。所谓执行计划验证过程也叫执行计划演化过程，就是将新的、非接受的执行计划与SQL Plan Baseline中某个执行计划进行比较，并确保该执行计划性能更佳的过程。通常，可通过如下两种方式进行验证：
- 通过EVOLVE_SQL_PLAN_BASELINE函数
例如，如下语句：
variable report clob exec :report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(- sql_handle=>'SYS_SQL_593bc74fca8e6738'); Print report
该语句将返回一个报告，告知执行计划历史记录中的指定执行计划是否被通过验证，并加入到Plan Baseline中。
- 通过运行SQL Tuning Advisor
通过手工和自动运行SQL Tuning Advisor，将对执行计划历史记录中的指定执行计划进行验证和演化。当SQL Tuning Advisor发现新执行计划性能好于SQL Plan Baseline中某个执行计划，则新执行计划加入到SQL Plan Baseline中。
SPM相关视图
与SPM相关的主要视图是dba_sql_plan_baselines，例如：
select signature, sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines; SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT --------- ------------ -------- ---------------- ------------ --- --- --- --- 8.062E+18 SYS_SQL_6fe2 select.. SQL_PLAN_6zsn… AUTO-CAPTURE YES NO NO YES 8.062E+18 SYS_SQL_e23f select.. SQL_PLAN_f4gy… AUTO-CAPTURE YES YES NO YES …
其中：
- SIGNATURE, SQL_HANDLE, SQL_TEXT和 PLAN_NAME等字段主要用于查询条件之用。
- ORIGIN字段则表示该Plan Baseline是自动捕获（AUTO-CAPTURE），还是手工加载（AUTO-CAPTURE）,以及通过SQL Tuning Advisor手工（MANUAL-SQLTUNE）或自动演化（AUTO-SQLTUNE）而来。
- ENABLED和 ACCEPTED字段。只有当ENABLED和 ACCEPTED字段都为Yes，优化器才考虑使用该执行计划。ENABLED字段表示该执行计划为可使用状态，而ACCEPTED字段表示该执行计划是否为Baseline。通过ALTER_SQL_PLAN_BASELINE()函数，可以将一个Baseline执行计划临时设置为不可用的，例如：
exec :cnt := dbms_spm.alter_sql_plan_baseline(- sql_handle => 'SYS_SQL_6fe28d438dfc352f', - plan_name => 'SQL_PLAN_6zsnd8f6zsd9g54bc8843',- attribute_name => 'ENABLED', attribute_value => 'NO');
- FIXED字段值为Yes，则表示优化器只考虑该执行计划。例如，假设一个SQL语句有10个Baseline执行计划，其中3个为FIXED，则优化器只考虑3个FIXED的执行计划。一个Plan Baseline如果包含至少一个FIXED执行计划，则该Plan Baseline称之为FIXED Plan Baseline。如果一个新执行计划加入到FIXED Plan Baseline中，只有当新执行计划为FIXED的，该执行计划才可使用。
同样地，通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE()函数，可将一个Plan Baseline执行计划设置为FIXED的，或反之。
DBMS_SPM包的更详细内容，请参考《Oracle® Database PL/SQL Packages and Types Reference11g Release 2 (11.2)》相关章节。
SPM数据的自动管理
以下是SPM数据的自动管理示意图：
SPM数据SMB（SQL Management Base）存储在SYSAUX表空间中，SMB空间上限缺省值为SYSAUX表空间的10%，Oracle自动每周对SMB空间进行检测。通过如下命令，可调整SMB空间上限值SPACE_BUDGET_PERCENT，例如：
SQL> exec dbms_spm.configure(‘SPACE_BUDGET_PERCENT’,20);
SPACE_BUDGET_PERCENT取值范围为1到50。
如果SMB空间超过了上限，Oracle将警告信息记录在alert.log文件中，直至SMB空间上限值扩大，或者SYSAUX空间扩大，或者通过删除SQL Plan Baseline、SQL Profile之后，使得SMB空间下降。
Oracle每周定期主动对53周（1年）没有使用到的SQL Plan Baseline数据进行清理，该任务每周在维护窗口自动运行。通过如下命令，可调整保存期限值PLAN_RETENTION_WEEKS，例如：
SQL> exec dbms_spm.configure(‘PLAN_RETENTION_WEEKS’,105);
PLAN_RETENTION_WEEKS取值范围为5到523，即5周到10年。
通过如下视图，可查询SMB的当前配置：
SQL> select * from DBA_SQL_MANAGEMENT_CONFIG;
通过如下命令，可手工删除SMB相关数据：
SQL> exec :cnt := dbms_spm.drop_sql_plan_baseline(‘SYS_SQL_37e0168b04e73efe’);
本章参考资料及进一步读物
本章参考资料及进一步读物：
| 序号 | 资料类别 | 资料名称 | 资料概述 |
| 1. | Oracle 11g R2联机文档 | 《Oracle® Database Performance Tuning Guide》 | 请大家重点阅读第15章 “Using SQL Plan Management”和第20章“Using Plan Stability” |
| 2. | My Oracle Support | 《Master Note: Plan Stability Features (Including SQL Plan Management (SPM)) (Doc ID 1359841.1)》 | 了解SPM技术的主目录文档。 |
| 3. | My Oracle Support | 《FAQ: SQL Plan Management (SPM) Frequently Asked Questions (Doc ID 1524658.1)》 | 针对SPM，大家一定有形形色色的各类问题，估计大家的绝大部分问题都能在这篇文档中找到答案。 |
| 4. | My Oracle Support | 《How to Use SQL Plan Management (SPM) – Example Usage (Doc ID 456518.1)》 | 使用SPM技术的一个详细例子。 |
| 5. | My Oracle Support | 《Loading Hinted Execution Plans into SQL Plan Baseline. (Doc ID 787692.1)》 | 原来的SQL语句通过Hint方式确保了执行计划的最优化，如何将这个执行计划变成SQL Plan Baseline？这篇文档给出了详细的过程。 |
| 6. | My Oracle Support | 《How To Configure Automatic Purge(Drop) Of SQL Plan Baseline(SPM). (Doc ID 1438701.1)》 | SPM的Baseline数据缺省保存一年，如何调整保存期限？这篇文档给出了具体方法。 |
| 7. | My Oracle Support | 《How to Load SQL Execution Plans in to a STS so that they can be Transferred (to 11g) and Loaded into a SQL Plan Baseline. (Doc ID 801033.1)》 | 该文档介绍了如何将10g的执行计划导入STS，并最终导入到11g的SPM的详细过程。 |
| 8. | My Oracle Support | 《HOW TO LOAD SQL PLANS INTO SPM FROM AWR (Doc ID 789888.1)》 | 如何将AWR中的SQL执行计划导入到SPM？这篇文档给出了详细过程。 |
Leave a Reply