Oracle数据库11g：SQL计划管理程序（一）

核心提示：另外一篇关于Oracle数据库11g新的SQL性能分析器（SPA）工具的文章探讨了用于SQL语句捕获和性能分析统计的技术，这样，Oracle数据库11g：SQL计划管理程序（一），在环境改变之前，能更正确地预报环境改变对SQL语句性能的影响，在你尝试在一个生产环境中部署前，我强烈建议你密切关注对应的Oracle文档，虽

另外一篇关于Oracle数据库11g新的SQL性能分析器（SPA）工具的文章探讨了用于SQL语句捕获和性能分析统计的技术，这样，在环境改变之前，能更正确地预报环境改变对SQL语句性能的影响，虽然这对每个Oracle DBA而言都是一套有价值的工具包，但任何资深的DBA都会告诉你这只成功了一半。

当一个有效的（和极好的）SQL语句执行计划突然变得无效时会出现一个更隐蔽的问题：SQL语句会被重新解析，但实际上新执行计划的结果比原来的计划更糟糕，有下面几个原因会引发这一事件的发生：

◆游标老化最后从库缓存中被清除出去，因为这需要它的SQL语句被硬解析，游标可能碰巧使用了效率更低的执行计划。

◆当优化器统计重新计算游标的依赖对象时游标也可能是无效的，或当游标依赖的某个对象被修改了。

◆还有其他一些原因，不外乎改变SQL语句计划的稳定性：优化器版本的改变，初始化参数的修改，甚至为SQL语句创建新的SQL配置文件（Oracle10g后）。

SQL计划管理：简短的历史介绍

每个DBA面对的最大挑战是不但要能识别哪条SQL语句正糟糕地在执行，而且要为SQL语句找到最佳的执行计划，并使这些计划对所有语句都是可用的并能从中受益，这些解决方案的发展成为了一个有趣的历史。

第一阶段：存储概要（stored outlines）

Oracle 8i提供了一个捕获SQL语句执行计划并保存为存储概要的机制，一个存储概要实际上是一条由DBA或开发者优化过的存储在OUTLN方案中的SQL语句（有时是通过工具进行优化的，但大多数时候是靠不断的试验和对比得出的），当一条SQL语句被执行且它的执行计划与存储概要完全匹配，Oracle就使用存储概要替换使用的SQL语句，存储概要也可以分阶段执行，或限制到仅那些匹配的会话才能执行。

这提出了Oracle 8i术语：计划稳定性，它可能是有用的，也有一些确定，最严重的是存储概要计划随时间的推移变得越来越过时和陈旧了，因为数据分配或优化统计在不停改变，实际上存储概要倾向于强制一条语句使用一个效率可能相当低的计划，而一个新的效率更好的计划可能只需要简单的重新解析该语句即可。

第二阶段：SQL配置文件（SQL profiles）

Oracle数据库10g使用了一个叫做SQL配置文件的新方法弥补了存储概要的缺点，DBA可以使用SQL调整顾问（STA）或SQL访问顾问（SAA）来识别可以得到更好性能的SQL语句，这些语句可以保存在SQL调整集、一个AWR快照或保存在当前的库缓存中，一旦识别出调整候选者，这些顾问程序就开始分析捕获到的语句以期获得更好的性能，然后生成专用的语句扩展（就叫做SQL配置文件）并重写SQL语句以在执行期间获取更佳的性能。

与存储概要类似，一个SQL配置文件提供了使用更好的执行计划的能力（如果这个执行计划是可用的），SQL配置文件也可以象存储概要一样分阶段执行，或限制到对特定会话才能执行该SQL配置文件，但是大多数重要的改进超过了存储概要，至少有两方面：（a）自我调整的能力保障了SQL配置文件能提供最好的执行计划，（b）检查SQL配置文件不再有效的能力（因此ADDM建议需要生成一个新的SQL配置文件）。

第三阶段：SQL计划管理

Oracle11g实现了一个新特色，叫做SQL计划管理（SPM），它通过一个简单而优雅的方法克服了异常的SQL执行计划回退，一旦一个用户会话启用了自动捕获SQL计划基线，基于成本的优化器（CBO）记录该会话执行的每条SQL语句到SQL管理库（SMB），它存储了SQL语句文本，存储概要，绑定变量和它的兼容环境，它将这些作为一个SQL计划的基线。

因为这是该语句第一次执行，Oracle11g会评估存储的执行计划哪个是最佳的，在下次执行同样的SQL语句过程中，SQL计划管理程序程序就很清楚了，在该语句的第二次执行时，基于成本的优化器（CBO）会与当前存储在SMB中的执行计划进行比较，如果CBO发现了一个新的计划，首先会将该新计划添加到计划历史中，然后评估该新计划看其效率是否比当前SMB中的执行计划更好。

如果新的执行计划可以提升SQL语句的性能，SQL计划管理程序（SPM）就将该计划标记为该语句执行的最佳选择，当DBA没有手动修改初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES的默认值TRUE时，现在该语句执行过程中CBO会使用新的执行计划，换句话说，因为使用了新的计划而语句的实际性能倒退了的话，CBO将会从SMB中选择一个认可的成本更低的计划，SPM将在SMB中存储新的执行计划，因为它可能在进来成为最好的执行计划选择。

捕获SQL计划基线

好消息是在Oracle11g中捕获SQL计划基线存储到SMB中一件相当容易的事情，首先，初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES控制自动捕获SQL计划基线是否激活，这个参数的默认值是FALSE，意味着SQL计划基线不是自动捕获的，但是，一经DBA设置为TRUE（不管是在会话层还是在系统层），SPM就开始记录SQL语句的执行，当一条SQL语句不止被执行过一次时，该语句将被考虑作为SQL计划基线捕获候选者。

其次，一个新的Oracle11g包（DBMS_SPM）提供捕获和预先从几个源通过手动发送计划引入高性能SQL语句的能力：

◆在数据库当前缓存中发现了一个或多个SQL语句可以用于创建SQL计划基线，存储过程LOAD_PLANS_FROM_CURSOR_CACHE可以用于捕获任何库缓存（作为SMB的潜在候选者）中语句的子集。

◆存储在SQL调整集或一个AWR快照中的SQL语句可以被捕获并通过LOAD_PLANS_FROM_SQLSET存储过程翻译成SQL计划基线。

◆最后，SQL计划基线由外部输入转换表中的SQL语句衍生而来，这就意味着从一个完全不同的数据库捕获语句是可能的，也就是说，在一个QA环境中，预先在当前存储过程环境中种下它们。（我将在本系列文章的后面花费大量的时间讨论如何使其成为一个优秀的技术，以预防在数据库或应用程序升级过程中出现计划回退。）

查看SQL计划基线信息

在SMB中捕获的SQL计划基线元数据包括SPM属性和CBO计划控制，当一个新的而没有使用过的计划进入SMB后，它被标记为ENABLED，但是它不能标记为ACCEPTED，除非：（A）CBO已经评估了这个计划并将其视为最佳计划，或（B）这个计划已经演变成ACCEPTED模式。在CBO考虑使用某个执行计划前，它必须同时将其标记为ENABLED和ACCEPTED。

查看该元数据最简单的方法是查询DBA_SQL_PLAN_BASELINES数据字典视图，下面是该视图中大部分有价值的计划控制信息的摘要：　　

表1.1 SQL计划基线计划控制元数据
属性	描述
SQL_HANDLE	用字符串表示的SQL唯一性标识，它可以作为搜索关键字
PLAN_NAME	用字符串表示的唯一的SQL计划，它可以作为搜索关键字
SQL_TEXT	非正常SQL语句，实际上是文本
ORIGIN	识别下面的SQL计划：
	l　 AUTO-CAPTURE：自动捕获
	l　　MANUAL-LOAD：手动演变
	l　　MANUAL-SQLTUNE：通过SQL调整顾问程序自动演变
	l　　AUTO-SQLTUNE：通过自动SQL调整程序自动演变
ENABLED	因为Oracle11g有下面任何一个原因，就表明该SQL计划是有效且很优秀的计划：
	l　自动接受（accepted）它，或
	l　DBA通过存储过程DBMS_SPM.alter_SQL_PLAN_BASELINE()手动将它的状态强制设置为ACCEPTED
FIXED	SQL计划的FIXED属性被设置为YES将被视为使用CBO优化器，如果有多个计划被标记为FIXED，CBO将从这些标记了的计划中选择最佳的执行计划
OPTIMIZER_COST	通过CBO使用这个执行计划执行SQL语句估量总体成本

另外一个查看执行一条SQL语句现存的SQL计划基线可能的影响的方法是通过新的DBMS_XPLAN包中的DISPLAY_SQL_PLAN_BASELINE存储过程实现的，例如：我可以用这个存储过程查看所有匹配SMB中的SQL语句的SQL计划基线，如果我也提供了该SQL语句计划的名字，那么它的执行计划也将同时显示出来。

自动SQL计划捕获：一个简单的例子

为了精确论证SQL计划管理如何为一条SQL语句选择最有效的执行计划，我将从一个相当简单的例子开始：一条SQL语句（查看列表1.1）从销售历史（SH）方案中的几个表中检索一个相对小的数据子集。注意已经用一个恰当的注释（SPM_1.1）标记了该SQL语句，以便在它朝一个可接受的计划基线发展过程中，能很容易地跟踪它的状态。

现在我已经准备好自动捕获SQL计划基线，在列表1.2中显示，我将为这个会话设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数为TRUE，以激活自动捕获SQL计划基线，并且我将设置OPTIMIZER_MODE为ALL_ROWS，以便CBO优化器优化SQL语句对应的执行计划，返回尽可能是最有效的所有结果集。为了保证该会话会应用任何被捕获的SQL计划基线，我还设置了初始化参数OPTIMIZER_USE_SQL_PLAN_BASELINES为TRUE（它的默认值），这个参数控制CBO是否会检查如果SQL语句再次重复执行时应该被评估以尽可能得到最佳的执行计划。

然后我会执行两次SPM_1.1语句，第一次执行是保证语句的执行被记录到了SMB，第二次执行该语句引起它的执行计划自动接受作为一个SQL计划基线。为了显示我实验中此时SMB的状态，我将执行列表1.3中的查询，它依靠DBA_SQL_PLAN_BASELINES视图，从输出的结果中可以看出，当前的语句实际上已经通过自动捕获被捕获到SMB中了。（注意我已经缩写了SQL语句的hash值和它的计划基线，保留了它们最后的八个字符以便更容易处理）

最后，我将修改OPTIMIZER_MODE参数的值为FIRST_ROWS，以便CBO优化SQL语句相对应的执行计划，尽可能返回前面的几行结果集—明显与ALL_ROWS设置相对，ALL_ROWS力求返回最大的结果集。列表1.4展示了我发布的实现这个的命令，与查询DBA_SQL_PLAN_BASELINES视图的结果一样，这个查询输出清晰地显示了两个执行计划都被存储在SMB中，但是计划07e0351f与计划ddc1fcd0的优化成本2388相比实际上效率更好—它的优化成本只有757，因此，SPM只标记了第一个计划为ENABLED和ACCEPTED。

最后，列表1.5显示了两个执行计划的详细情况，我已经利用DBMS_XPLAN的新存储过程DISPLAY_SQL_BASELINE显示了它们的正确的执行方法，注意SPM已经捕获了相应的SQL计划基线以及它所有的优化器统计。

下一步

我已经介绍了SQL计划管理原理和一些最基本的如何有效使用它的例子，但是还有太多的性能调整情景，在本系列的下一篇文章中，我将深入研究如何：

◆通过手工方法捕获SQL计划基线

◆使用SQL计划基线为应用程序升级进行预演

◆手动演变一个SQL计划基线

参考书目和附加读物

我希望我在本文中已经给你彻底展示了技术方面的特征，同时我相信还有许多更好的文档可供参考，因此，在你尝试在一个生产环境中部署前，我强烈建议你密切关注对应的Oracle文档，以获取更清晰的理解，请注意我已经在下面列出了Oracle数据库11gR1有关的文档：

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

Oracle数据库11gR1性能调整指南

B28279-02 Oracle Database 11gR1 New Features Guide

Oracle数据库11gR1新特征指南

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

Oracle数据库11gR1 PL/SQL包和类型参考

下面在Oracle Metalink上的记录也提供了关于Oracle 8i及更高版本存储概要有价值的背景信息：

67536.1: Using Stored Outlines

使用存储概要

102311.1: How to Move Stored Outlines for One Application from One Database to Another

如何为一个应用程序从一个数据库到另外一个数据库移动存储概要

132547.1: Stored Outlines Quick Reference

存储概要快速参考