Oracle数据库11g：SQL计划管理（三）

　　摘要：Oracle数据库11gR1提供一套新工具集—SQL计划管理（SPM），它让每个Oracle DBA都可以为任何SQL语句捕获并保存最有效的执行计划。本文是本系列中的最后一篇，主要讲述如何使用SPM限制现有SQL语句第一次遇到Oracle11g的基本成本的优化器（CBO）时性能突然倒退，同时，也一并介绍一下几个允许非常细粒度的SQL管理库（SMB）的管理的SQL计划管理特色。

　　本系列前面的文章探讨了Oracle数据库11g新的SQL计划管理（SPM）特色，它可以用于Oracle10g升级到Oracle11g时捕获和载入SQL计划基线，以便在数据库升级过程中消除SQL语句性能倒退，前面的文章也讲述了如何保证新应用程序代码在部署到生产环境之前，为SQL语句选择最有效的执行计划。

　　本文将讲述如何使用SPM：

　　◆当它们尝试利用Oracle11g新的优化器特色时，限制SQL语句性能不必要的倒退。

　　◆通过手工方法捕获特定SQL语句的SQL计划基线。

　　◆控制现有SQL计划基线的演变。

　　◆管理SQL计划管理基线（SMB）的内容，包括如何清除过时的或不希望有的SQL计划基线。

　　SPM情景#3：依靠前面的优化器版本使用SQL性能分析器（SPA）

　　在前面文章中的第一个情景讲述了如何使用SPM为SQL语句捕获SQL计划基线，该SQL语句的性能在实施Oracle10g升级到Oracle11g的过程中可能倒退。那个情景实际上涉及到在一个现有的Oracle10g数据库中执行该SQL语句组成的SQL工作负载。解决这个问题的一个可选的方法是在一个现有的Oracle11g数据库环境中通过控制初始化参数OPTIMIZER_FEATURES_ENABLE的值模拟一个Oracle10g环境。

　　模拟准备

　　要说明这个情景，我将利用前面文章中SPM情景#2中SQL调整集STS_SPM_200中捕获的相同的SQL语句，在我执行任何新的分析之前，我将从SQL管理库（SMB）中移除早先创建的所有SQL计划基线，我将使用函数DBMS_SPM.DROP_SQL_PLAN_BASELINE移除那些标记有注释字符串“SPM_2”的语句（查看列表3.1），然后，我会准备一个新的名叫SPA_SPM_300的SQL性能分析器（SPA）任务，它将分析STS_SPM_200 SQL调整集中的SQL工作负载的性能（查看列表3.2）。

　　分析SQL工作负载

　　接下来，我将瞄准SPA任务SPA_SPM_300，评估在Oracle10gR2和Oracle11gR1数据库环境之间模拟的工作负载性能，首先我会清除我的Oracle11g的库缓存和数据库缓存，确保提供一个干净的性能评估起点。然后，我会设置初始化参数OPTIMIZER_FEATURES_ENABLE的值为10.0.0.0，欺骗优化器相信它是一个Oracle10g数据库，最后，我会通过执行SPA_SPM_300一次测试分析那个模式下的性能，一旦这个顺序完成了，我将设置OPTIMIZER_FEATURES_ENABLE的值为11.1.0.6，然后重复相同的分析，SPA将在Oracle11g环境下评估工作负载，这里用到的代码在列表3.3中。

　　比较相关的工作负载性能

　　一旦这两个工作负载执行测试完成，我下一个任务是判断是否有SQL语句因为优化器设置改变而性能倒退了，我使用的代码在列表3.4中，它们比较两个工作负载模拟情况，然后生成一个关于哪些SQL语句性能下降了的报告。为了在这里展示SQL性能分析器的灵活性，我避开使用一个相对变化的优化成本作为我的度量值，相反，我选择基于估计执行时间进行SQL语句比较。

　　为性能倒退的SQL语句捕获SQL计划基线

　　与分析报告显示的结果一样，在模拟从10.2.0.1升级到11.1.0.6时，有两条SQL语句产生了负面的影响（性能降低了），我将捕获这些语句的执行计划到SQL计划基线中，这将防止CBO使用11g优化器设置运行这些SQL语句，它会因这些语句引起有害的性能，列表3.5中的代码说明了如何实现这个。

　　控制SQL计划演变

　　Oracle11g创建了新术语“SQL计划演变”来描述查找一套SQL语句的最佳执行计划的渐进过程，我在本系列的第一篇文章中提到过，无论何时，SPM为SMB中的SQL语句拦截一个新的执行计划，然后，SPM保存那个计划到SMB中将其作为SQL语句历史的一部分，然而，SPM不允许使用该计划除非它通过了性能评估，能提升性能。如果SPM判断该新计划可以提升性能，SPM将改变该计划的状态为ACCEPTED，这个概念与计划进展一样著名，在SMB中的SQL语句自动演变是SPM的核心。

　　为了说明这个概念，我创建了一个简单的SQL语句（SPM_3_1.SQL）查询在销售历史（SH）方案中的几个表，要创建一个干净的状态SPM会评估这个查询，无论如何，我首先将从标记有注释字符串SPM_的SMB、库缓存和数据库缓存中清除SQL计划基线，如列表3.6显示的那样。一旦清除完成，我将在会话层使用列表3.7中的代码设置初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE的值为FALSE将自动捕获SQL计划基线设置为非激活状态，执行查询，然后使用函数DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手动从库缓存中捕获SQL计划基线的结果。

　　绑定值监视及适应性游标共享

　　我在SQL语句SPM_3_1.sql中故意使用了绑定变量以说明SQL计划管理如何与绑定变量交互，基本成本的优化器从Oracle9.0.1以来就已经可以利用这个特色了，它让CBO基于提供给绑定变量的第一套值判断出一个更准确的执行计划。

　　Oracle11g也引入了一个叫做适应性游标共享的新特性，它能非常高效地与绑定变量和共享游标进行交互，SPM会通过标记每捕获的第一个执行计划作为对应的SQL计划基线解决这两个特性之间的所有冲突，如果我重新执行了相同的查询但为绑定变量使用了不同的值，它相对原始计划性能可能更低了，要预防这个问题，SPM将存储这个执行计划作为SMB中SQL语句计划历史的一部分，但是新的计划不会被使用除非SPM校验了它，并认为它效率更高。（我会在以后的文章中专门讲述这一方面的内容，并讲述适应性游标共享如何工作）

　　请求SQL计划基线演变

　　换句话说，当SPM检测到一个性能更好的计划会发生什么？我将在SH.CUSTOMERS表中的CUST_LAST_NAME列上添加一个索引来模拟这个情景，这个索引对于SQL语句SPM_3_1.sql的性能将起到立竿见影的影响，因为不再会在SH.CUSTOMERS上请求一个全表扫描了。一旦我在会话层将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE的值设置为默认值（TRUE）以重新激活自动捕获SQL计划基线的话，我将再次执行该SQL语句并校验捕获的SQL计划基线的结果，CBO将收集一个新的会使用到该新索引的执行计划，来提升语句的性能，新的执行计划也将记录到SMB中（查看列表3.8）

　　直到SPM演变这个新的计划，否则，这个计划不会通过语句的SQL计划基线被利用，当我执行函数DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE时，Oracle 11g 将立即评估所有可用的SQL计划基线并性能得到提升的计划，这个函数返回一个CLOB类型的值，它列出了所有状态被修改为ACCEPTED的SQL计划基线，我在列表3.9值展示了这个计划演变的结果，为了说明无论何时执行这条sql语句时该SQL计划基线都将被使用到，我对该语句运行了一个执行计划并使用+NOTES指令格式化输出结果。

　　通过SQL调整顾问自动演变计划

　　Oracle 11g 也提供了SQL计划基线的自动演变，通过对每个选择的SQL语句执行SQL调整顾问程序实现，因为自动SQL调整（AST）批处理过程在每天夜间规律地执行时也会请求SQL调整顾问，AST也可以接受SQL配置文件，无论何时，配置文件能比当前的SQL计划基线提供更好的性能。（要查看更多关于自动SQL调整的信息，请查看这篇文章http://www.databasejournal.com/features/oracle/article.php/3701596）

　　修改SQL计划基线的属性

　　Oracle 11g 允许非常细粒度地控制捕获的SQL计划基线的状态和可用性，而不顾它们的起源，要说明这一点，我准备了三条SQL语句（查看SPM_3_2.sql，我用相似的注释标记它们以好识别），一旦执行，我直接从库缓存中捕获它们的执行计划，最后使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE将它们载入SMB中作为SQL计划基线，我应用函数DBMS_SPM.ALTER_SQL_PLAN_BASELINE修改每个SQL计划基线的各种属性，这些修改过的属性包括：

　　◆ENABLED ：设置该属性的值为NO告诉Oracle 11g临时禁用某个计划，一个SQL计划必须同时标记为ENABLED和ACCEPTED，否则CBO将忽略它

　　◆FIXED：实际上一个SQL计划基线可能比一个执行计划更有参考意义，当这个属性对至少一个SQL计划基线的计划被设置为YES是，那个计划将是优化器唯一的选择，即使如果某个计划可能拥有更低的成本。这让DBA可以撤销SMB的默认行为，对于转换一个存储概要进入一耳光稳定的SQL计划基线特别有用，注意当一个新计划被添加到被标记为FIXED的SQL计划基线，该新计划不能被利用除非它申明为FIXED状态

　　◆AUTOPURG：设置这个属性的值为NO告诉Oracle 11g无限期保留它，从而不用担心SMB的自动清除机制

　　列表3.10中的代码展示了我如何设置捕获这些SQL语句以及如何修改这些SQL计划基线的属性。Oracle 11g实际上提供了四个超负荷的方法执行这个功能，我谈到的方法可能非常复杂，因为它接受一个可以过滤的SPM属性，其他三个方法接受一组：

　　◆一个SQL ID，一个SQL计划基线hash值和一个简单的过滤SQL文本的过滤器

　　◆一个SQL ID，一个SQL句柄和一个SQL计划基线hash值

　　◆一个SQL ID，一个SQL计划基线hash值

　　控制自动SMB管理特性

　　Oracle 11g也提供了两个自动管理特性，在严格控制下它可以帮助保持SMB的相对大小和内容。

　　SMB空间管理

　　Oracle每周会检查SMB的大小是否超过了它的空间预算限制（默认值：SYSAUX表空间的10%），如果超过了限制，会在数据库的警报日志中记录一条警告信息，除非满足下面的条件空间警告信息将一直持续：

　　◆为SYSAUX表空间分配更多的空间；或者

　　◆加大空间限制的默认值；或者

　　◆从SMB中清除SQL计划基线或SQL配置文件

　　SMB自动清除

　　每周，有一个自动任务运行用于清除SMB中所有在未用计划保留周期内的SQL计划，这个周期默认是53周，但可以调整为5--523周之间的一个值（如：大约10年）

　　这些限制可以通过存储过程DBMS_SPM.CONFIGURE来设置，它们当前的值可以通过查询数据字典视图DBA_SQL_MANAGEMENT_CONFIG获取，我在列表3.11中说明了如何修改SMB空间管理的默认设置以及自动清除SMB。

　　用企业管理器管理SQL计划基线

　　本文主要集中精力讲述基于脚本的管理SQL计划基线的方法，但是，如果我不叙述Oracle 11g企业管理器数据库控制台也提供了一个非常优秀的接口用于复审和管理SMB的内容，那将对不起读者，图3.1展示了在“服务器”（Server）页面上选择SQL计划控制链接的结果：

图3.1 SQL计划基线管理主面板

　　这个接口使得可以不用任何复杂的PL/SQL代码就可以非常容易地执行下面的SPM功能：

　　◆启用或禁用某个SQL计划基线

　　◆为某个选中的SQL计划基线演变一个更好的计划

　　◆删除一个现有的SQL计划基线

　　◆打包某个SQL计划基线的内容进入某个临时表

　　◆打开某个临时表到一个SQL计划基线

　　图3.1中的例子说明，限制这个屏幕上显示的SQL计划基线列表也是可能的。

　　结论

　　这一系列的文章足以说明Oracle 11g的SQL计划管理（SPM）新特性为Oracle DBA提供了一个保证SQL语句能访问绝对最佳的执行计划的强大工具，同时，在SQL语句正式进入生产环境之前，提供了一个稳定其性能的机会，SQL计划基线可以从多个源捕获 -- 甚至从现有的Oracle 10g数据库 -- 可以为新应用程序预先载入以减轻潜在的崩溃可能，使得部署顺利进行，最后，SPM提供了几个方法影响SQL计划基线，基于成本的优化器将为每条独立的SQL语句选择最佳的执行计划，维护当前的SQL计划基线列表，以及自动或手动清除不再使用的SQL计划基线。