摘要：Oracle数据库11gR1新的SQL计划管理工具给每个Oracle DBA都有能力捕获并保存最有效的SQL语句执行计划。本文—该系列文章的第二篇—解释在升级一个现存的Oracle10gR2数据库到Oracle11g时如何使用SQL计划管理，与部署新应用程序代码一样，有效地限制SQL语句性能异常回退。

　　本系列的前一篇提供了Oracle数据库11g新的SQL计划管理（SPM）特色的入门介绍，包括一些如何使用SPM工具帮助过滤执行计划以识别并隔离最佳的计划以提升SQL语句性能的简单例子。

　　因为我已经解释并说明了SQL计划管理的基本架构，现在我将注意力集中到讨论两种每个Oracle DBA都会遇到的情况：升级现有的Oracle数据库到一个更新的版本，部署新的应用程序代码到现有的数据库。前面的文章已经说明了如何使用Oracle11g的新DBMS_SPM包捕获新的SQL计划基线，我将利用这两种情况来说明Oracle11g企业管理器数据库控制的SQL计划控制规则来捕获新的SQL计划基线候选者，以及管理现有的SQL计划基线。

　　SPM情景#1：升级一个现有的数据库

　　在我看来，升级一个现有的数据库有新的版本即使对一个经验丰富的Oracle DBA而言也是相当痛苦的事情，因为很难精确地判断升级后哪个语句性能降低了。在Oracle11g之前的环境中，我已经找到了限制这个不确定性最好的方法，就是在我的QA服务器上构造一个与生产环境完全一致的副本，捕获充足的应用程序最关键的SQL语句工作负载，并捕获这些语句的执行计划。一旦我启动升级开关升级QA数据库和环境到新的数据库版本，我要为这些语句重新生成执行计划并比较结果以发现性能倒退的语句。

　　通过这种野兽般的测试方法我得以相当顺利的从旧版本升级到Oracle11g，我也希望有更可靠的方法精确地判断升级对现有SQL语句性能的影响，我已经在早先的关于SQL性能分析器的文章中论述过了，因此现在隔离任何性能倒退的SQL语句非常简单，甚至对内部相对较小的升级也很简单（如从11.1.0.5.0升级到11.1.0.6.0）。一旦使用SQL性能分析器识别出所有性能倒退的SQL语句，在我执行升级操作之前，我将拿起SQL计划管理的强大工具捕获那些语句到SQL调整集（STS）中。

　　从STS捕获语句的SQL文本、绑定变量、执行计划和执行统计后，我将保留它们直到数据库版本升级完毕，到那时我将转换这些语句执行计划成为SQL计划基线，当这些语句对于升级后的数据库第一次执行时，无论如何，基于成本的优化器（CBO）都将检测SQL计划基线是否仍然可用，如果CBO判断SQL计划基线提供了一个更有效的执行计划，它将用该基线计划替换原有的计划，最终结果是一个可能很严重的SQL计划倒退是可以完全避免的。

　　收集SQL工作负载

　　要说明这个概念，我将首先在Oracle10gR2上创建一个SQL工作负载。我将在销售历史方案（SH）的多个表中使用5个查询，查询SQL（SPM_2_1.SQL），用来模拟一个在数据仓库应用程序中的SQL工作负载，在我开始工作负载前，我将初始化列表2.1中的代码，它使用DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET来捕获工作负载的SQL语句到一个名叫STS_SPM_200的SQL调整集中。

　　打包并导出SQL调整集

　　一旦我将SQL工作负载捕获到一个SQL调整集中，我准备将它转移到Oracle11gR1数据库中，列表2.2展示了如何做：

　　◆创建中间表作为SQL调整集STS_SPM_200的容器

　　◆通过存储过程DBMS_SQLTUNE.PACK_STGTAB_SQLSET转移SQL调整集到中间表

　　◆通过数据泵导出工具导出中间表数据到一个名叫DumpStagingTable.dmp的导出文件

　　转移SQL调整集

　　在我拷贝了数据泵导出文件到我的Oracle11g数据库的默认数据泵目录后，我将使用Oracle数据泵导入工具和适当的参数导入中间表到目标Oracle11gR1数据库，然后，我会使用存储过程DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET打开存储在中间表中的SQL工作负载。列表2.3展示了转移过程的详细情况。

　　载入SQL调整集内容到SPM

　　为了完成转移存储在SQL调整集中的语句，我将直接载入这些语句到SQL管理库，而不用存储过程DBMS_SPM来完成，我会使用企业管理器数据库控制的SQL计划控制接口，从“server”页面有链接到它的入口：

图2.1 SQL计划控制主面板

　　注意在我们早前的实验中产生的SQL计划基线在这里列出来了，包括它们当前的状态和可用性。在下面的图2.2中，我将从面板中那些可用的调整集中选择SQL调整集STS_SPM_200：

图2.2 载入SQL调整集到SMB中

　　一旦我选择了恰当的SQL调整集并点击了载入（Load）按钮，Oracle11g直接从选择的STS中自动载入SQL语句到SMB中，如下面图2.3：

图2.3 SQL调整集载入的结果

　　注意有五个SQL计划基线的状态都是ENABLED和ACCEPTED，意味着当一条SQL语句遇到了匹配的hash值时，CBO会使用它们作为直接的候选者。点击名称（Name）栏的链接可以查看每个SQL计划基线的详细情况。下面是选择基线名称为SYS_SQL_685ea4c28ec1a586的结果：

图2.4.1SQL语句SPM_2.1.3执行计划（一）

图2.4.2SQL语句SPM_2_1.3执行计划（二）

　　概念证明

　　为了证明潜在的SQL语句性能倒退减少了或完全消除了，现在我将简单地执行SPM_2_1.SQL中相同的五条SQL语句，并检验CBO的确选择了预先载入的SQL计划基线而不是执行一个新的执行计划效率更低的解析。判断这个的最简单的方法是为这五条语句执行DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE存储过程，当为FORMAT参数解析TYPICAL+NOTE的值时需要显示CBO已经选择的计划，如果CBO的确选择了一个现有的SQL计划基线作为它的执行计划，+NOTE指令指示存储过程显示一条记录。列表2.4显示了为SPM_2_1.sql中的五条语句执行这个存储过程的结果。

　　SPM情景#2：部署一个新应用程序

　　每当我部署一个新的应用程序到我的生产数据库时，我知道我实际上受应用程序开发人员和质量保证人员的摆布，即使我坚决主张严谨的数据库对象命名标准，严格坚持PL/SQL最佳实践，给不同绑定变量使用不同的值对SQL语句进行广泛地测试，我也知道这些指导意见往往不切实际。当我商店的应用程序开发团队和QA资源不能达成一致的标准时，或如果我的IT管理上司认为我们花费了太多时间进行测试，我知道这是一个新开发的内部应用程序对调整好的Oracle数据库施加严重的性能影响的好机会。

　　另一个可能引起数据库性能下降的因素是那些经过管理层批准强制部署的第三方应用程序，每个经验丰富的Oracle DBA有至少遇到过一次这种情况：一个外部应用程序正引起严重的性能问题，经过调查显示该应用程序的开发人员使用了“agnostic SQL”以满足在任何操作系统环境中的任何数据库上都能运行该应用程序，结果是，SQL代码不能直接调整，因为它是放置在应用程序中的。

　　好消息是当部署一个崭新的应用程序到下列环境中时，Oracle 11g提供了一些避免降低性能的希望：

　　◆对于一个内部应用程序，我只需要捕获应用程序最典型的SQL语句到SQL调整集进入QA环境进行评估

　　◆对于一个第三方应用程序，在应用程序部署之前，我可以要求厂商就所有SQL语句发送给我，因此我可以预先捕获并评估它们的性能，或者：当它在一个QA或开发环境中执行时，我直接从库缓存中捕获它最常用的SQL语句。

　　一旦我捕获了新应用程序的SQL语句，我就可以使用现有的QA或开发数据库捕获它们对应的SQL计划基线，然后直接转移那些基线进入生产数据库的SMB中，最终结果如何呢？当应用程序最终部署好后这些执行计划早已经准备好了，因此，在首次部署到生产环境中时，可以让CBO不再为这些忙碌的SQL语句建立执行计划了，最好的情况是，如果最佳执行计划提升了这些SQL语句的性能，CBO将自动演变这些改进多的计划。

　　准备模拟

　　在我模拟这个情景之前，我执行了一些配置任务，我在列表2.5中将它们集中起来了：

　　◆首先我用存储过程DBMS_SPM.DROP_SQL_PLAN_BASELINE清除了我当前的Oracle 11g数据库SMB中的SQL计划基线。这个存储过程接受“SQL句柄”（SQL handle）和“计划名字”（plan name）作为参数，然后从SMB中移除对应的SQL计划基线，我编写了一个无名的PL/SQL块，它利用一个游标捕获所有的SQL计划基线，游标的SQL文本包括了一个类素SPM_2的注释，然后反回对应的SQL句柄/计划名字组合给存储过程，这个方法允许细粒度的控制SMB中的内容，我将贯穿这些情景多次使用它，当必要时有选择性地减少SMB的粒子数。

　　◆然后我会构造组成销售人员管理系统的基本组件，如在SFA_Setup.sql中显示的内容，我就创建一个新的方案所有者（SFA），授予它适当的系统和对象权限，最后在该方案中创建并填充几个表。

　　捕获SQL计划基线

　　在列表2.6中我说明了对这个新应用程序捕获一个模拟的SQL工作负载的步骤：

　　◆为了在测试环境中模拟捕获一个简单的工作负载测试，在执行SPM_2_2.sql中的代码之前，首先我会保证当前的Oracle 11g数据库库缓存和数据库缓存是清空的，有六个查询 -- 所有查询都被用（SPM_2_2）加以注释以便识别 -- 展示了几个不同的用途，用户可能决定在区域、地区和领域层联合新应用程序SFA方案中关于我的销售人员和销售历史方案（SH）中的销售历史信息，注意我在这些查询中也使用了大量的绑定变量，因此我有机会在将来的示例中评估其他的执行计划。

　　◆一旦工作负载被生成，这六个查询也被解析到Oracle 11g数据库的库缓存中，通过函数DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE捕获它们的SQL计划基线进入SMB中就相对简单了，这个函数的过滤功能允许我只捕获那些有SPM_2_2注释信息的SQL语句。

　　从测试环境中导出SQL计划基线

　　象列表2.7展示的那样，我将才我的模拟测试环境中导出捕获的SQL计划基线：

　　◆首先，我将使用存储过程DBMS_SPM.CREATE_STGTAB_BASELINE创建一个SQL计划管理临时表

　　◆然后我会使用函数DBMS_SPM.PACK_STGTAB_BASELINE用SYS用户创建的SQL语句填充那个临时表

　　◆最后，我将调用数据泵导出工具导出临时表的元数据和内容

　　导入SQL计划基线到一个生产环境

　　为了结束这个情景，我将模拟部署SQL计划基线到一个生产环境（查看列表2.8）：

　　◆首先，我将导入SQL计划基线临时表到我的目标生产环境

　　◆因为我正使用相同的Oracle 11g数据库模拟测试和生产环境，我将再次使用存储过程DBMS_SPM.DROP_SQL_PLAN_BASELINE清除所有具有SPM_2_2注释信息的SQL计划基线

　　◆然后，我将在我的生产数据库中通过函数DBMS_SPM.UNPACK_STGTAB_BASELINE直接重新构建那些基线

 

　　概念证明

　　我已经通过查询数据字典视图DBA_SQL_PLAN_BASELINES校验了成功的“unpacking”。（在本系列前面的文章中我使用过相同的查询，请参考列表1.3）。这六个目标查询的SQL计划基线显示的输出结果在列表2.9中，将MANUAL_LOAD值标记为ORIGIN以指示来源于DBA的干预而不是自动SQL计划基线捕获方式。

　　下一步

　　我已经讲述了在数据库升级过程中SQL计划基线如何有效地限制（如果不能消除）SQL语句性能突然倒退，以及在部署一个崭新的应用程序之前如何捕获SQL计划基线以限制计划性能降低，在本系列下一篇也是最后一篇中，我将讲述另外一个SQL计划管理情景，在Oracle 10g和11g数据库之间不用导出SQL计划基线进行数据库升级，我还将讨论如何：

　　◆控制自动演变SQL计划基线

　　◆使用手工方法撤销计划演变

　　◆管理和监视自动清除过时的SQL计划基线

　　SPM情景#1：

　　/*

　　|| Script: SPM_2_1.sql

　　|| Purpose: Generate several SQL statements that perform Data

　　|| Warehouse query loads on an Oracle 10gR2 database

　　|| for capture into a SQL Tuning Set (STS) that will be

　　|| transferred to an Oracle 11g database to demonstrate

　　|| SQL Plan Baseline seeding.

　　*/

　　-----

　　-- Query: SPM_2_1.1

　　-- Purpose: Summarize sales activity within Country and Promotion

　　-----

　　SELECT /*SPM_2_1.1*/

　　CTY.country_total_id

　　,PR.promo_total_id

　　,COUNT(S.amount_sold)

　　,SUM(S.amount_sold)

　　,SUM(S.quantity_sold)

　　FROM

　　sh.sales S

　　,sh.customers C

　　,sh.countries CTY

　　,sh.promotions PR

　　WHERE S.cust_id = C.cust_id

　　AND C.country_id = CTY.country_id

　　AND S.promo_id = PR.promo_id

　　GROUP BY

　　CTY.country_total_id

　　,PR.promo_total_id

　　-----

　　-- Query: SPM_2_1.2

　　-- Purpose: Summarize sales activity within Country and Promotion

　　-----

　　SELECT /*SPM_2_1.2*/

　　CTY.country_id

　　,CTY.country_subregion_id

　　,CTY.country_region_id

　　,CTY.country_total_id

　　,PR.promo_total_id

　　,COUNT(S.amount_sold)

　　,SUM(S.amount_sold)

　　,SUM(S.quantity_sold)

　　FROM

　　sh.sales S

　　,sh.customers C

　　,sh.countries CTY

　　,sh.promotions PR

　　WHERE S.cust_id = C.cust_id

　　AND C.country_id = CTY.country_id

　　AND S.promo_id = PR.promo_id

　　GROUP BY

　　CTY.country_id

　　,CTY.country_subregion_id

　　,CTY.country_region_id

　　,CTY.country_total_id

　　,PR.promo_total_id

　　-----

　　-- Query: SPM_2_1.3

　　-- Purpose: Summarize sales activity within Country, Product,

　　-- Channel and Promotion

　　-----

　　SELECT /*SPM_2_1.3*/

　　CTY.country_total_id

　　,P.prod_id

　　,P.prod_subcategory_id

　　,P.prod_category_id

　　,P.prod_total_id

　　,CH.channel_id

　　,CH.channel_class_id

　　,CH.channel_total_id

　　,PR.promo_total_id

　　,COUNT(S.amount_sold)

　　,SUM(S.amount_sold)

　　,SUM(S.quantity_sold)

　　FROM

　　sh.sales S

　　,sh.customers C

　　,sh.countries CTY

　　,sh.products P

　　,sh.channels CH

　　,sh.promotions PR

　　WHERE S.cust_id = C.cust_id

　　AND C.country_id = CTY.country_id

　　AND S.prod_id = P.prod_id

　　AND S.channel_id = CH.channel_id

　　AND S.promo_id = PR.promo_id

　　GROUP BY

　　CTY.country_total_id

　　,P.prod_id

　　,P.prod_subcategory_id

　　,P.prod_category_id

　　,P.prod_total_id

　　,CH.channel_id

　　,CH.channel_class_id

　　,CH.channel_total_id

　　,PR.promo_total_id

　　-----

　　-- Query: SPM_2_1.4

　　-- Purpose: Summarize sales activity within Country, Product,

　　-- Channel and Promotion

　　-----

　　SELECT /*SPM_2_1.4*/

　　CTY.country_total_id

　　,P.prod_category_id

　　,P.prod_total_id

　　,CH.channel_id

　　,CH.channel_class_id

　　,CH.channel_total_id

　　,PR.promo_total_id

　　,COUNT(S.amount_sold)

　　,SUM(S.amount_sold)

　　,SUM(S.quantity_sold)

　　FROM

　　sh.sales S

　　,sh.customers C

　　,sh.countries CTY

　　,sh.products P

　　,sh.channels CH

　　,sh.promotions PR

　　WHERE S.cust_id = C.cust_id

　　AND C.country_id = CTY.country_id

　　AND S.prod_id = P.prod_id

　　AND S.channel_id = CH.channel_id

　　AND S.promo_id = PR.promo_id

　　GROUP BY

　　CTY.country_total_id

　　,P.prod_category_id

　　,P.prod_total_id

　　,CH.channel_id

　　,CH.channel_class_id

　　,CH.channel_total_id

　　,PR.promo_total_id

　　-----

　　-- Query: SPM_2_1.5

　　-- Purpose: Summarize sales activity within Country, Product,

　　-- Channel and Promotion

　　-----

　　SELECT /*SPM_2_1.5*/

　　CTY.country_id

　　,CTY.country_subregion_id

　　,CTY.country_region_id

　　,CTY.country_total_id

　　,P.prod_id

　　,P.prod_subcategory_id

　　,P.prod_category_id

　　,P.prod_total_id

　　,CH.channel_id

　　,CH.channel_class_id

　　,CH.channel_total_id

　　,PR.promo_total_id

　　,COUNT(S.amount_sold)

　　,SUM(S.amount_sold)

　　,SUM(S.quantity_sold)

　　FROM

　　sh.sales S

　　,sh.customers C

　　,sh.countries CTY

　　,sh.products P

　　,sh.channels CH

　　,sh.promotions PR

　　WHERE S.cust_id = C.cust_id

　　AND C.country_id = CTY.country_id

　　AND S.prod_id = P.prod_id

　　AND S.channel_id = CH.channel_id

　　AND S.promo_id = PR.promo_id

　　GROUP BY

　　CTY.country_id

　　,CTY.country_subregion_id

　　,CTY.country_region_id

　　,CTY.country_total_id

　　,P.prod_id

　　,P.prod_subcategory_id

　　,P.prod_category_id

　　,P.prod_total_id

　　,CH.channel_id

　　,CH.channel_class_id

　　,CH.channel_total_id

　　,PR.promo_total_id

　　SPM情景#2：

　　/*

　　|| SFA_Queries.sql

　　||

　　|| Various queries to produce workload against simulated new

　　|| Sales Force Administration application

　　||

　　*/

　　-----

　　-- Set up and initialize bind variables

　　-----

　　VARIABLE rgn_abbr VARCHAR2(4);

　　VARIABLE rgn_desc VARCHAR2(40);

　　VARIABLE cust_id NUMBER;

　　BEGIN

　　:rgn_abbr := 'NE00';

　　:rgn_desc := 'South%';

　　:cust_id := 9090;

　　END;

　　/

　　-----

　　-- Query: SPM_2.2.1

　　-- Purpose: Summarize quantity sold and revenue within U.S. States

　　-----

　　SELECT /*SPM_2.2.1*/

　　C.cust_state_province

　　,SUM(sh.quantity_sold)

　　,SUM(sh.amount_sold)

 

　　FROM

　　sh.sales SH

　　,sh.customers C

　　,sh.countries T

　　WHERE SH.cust_id = C.cust_id

　　AND C.country_id = T.country_id

　　AND T.country_iso_code IN ('GB','PL')

　　GROUP BY C.cust_state_province

　　-----

　　-- Query: SPM_2.2.2

　　-- Purpose: Show distribution of geographic areas within

　　-- Sales Region and District

　　-----

　　SELECT /*SPM_2.2.2*/

　　SR.abbr,

　　SD.abbr,

　　SZ.geo_id,

　　COUNT(C.cust_id) "Count"

　　FROM

　　sfa.sales_regions SR,

　　sfa.sales_districts SD,

　　sfa.sales_zones SZ,

　　sh.customers C

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND SR.abbr = 'SE00'

　　GROUP BY

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　-----

　　-- Query: SPM_2.2.3

　　-- Purpose: Accumulate quantities and revenue within Sales Region, District,

　　-- and Territory. Note that this query +should+ take advantage of

　　-- materialized view SFA.MV_SALES_SUMMARY for most effective retrieval

　　-----

　　SELECT /*SPM_2_2.3*/

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　,C.cust_id

　　,SUM(SH.quantity_sold)

　　,AVG(SH.quantity_sold)

　　,COUNT(SH.quantity_sold)

　　,SUM(SH.amount_sold)

　　,AVG(SH.amount_sold)

　　,COUNT(SH.amount_sold)

　　FROM

　　sfa.sales_regions SR

　　,sfa.sales_districts SD

　　,sfa.sales_zones SZ

　　,sh.customers C

　　,sh.sales SH

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND C.cust_ID = SH.cust_id

　　GROUP BY

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　,C.cust_id

　　ORDER BY

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　,C.cust_id

　　-----

　　-- Query: SPM_2.2.4

　　-- Purpose: Accumulate quantities and revenue within Sales Region, District,

　　-- and Territory by querying directly against SFA.MV_SALES_SUMMARY

　　-----

　　SELECT /*SPM_2_2.4*/

　　rgn_abbr

　　,dst_abbr

　　,ter_abbr

　　,cust_id

　　,tot_qty_sold

　　,avg_qty_sold

　　,cnt_qty_sold

　　,tot_amt_sold

　　,avg_amt_sold

　　,cnt_amt_sold

　　FROM

　　sfa.mv_sales_summary

　　WHERE dst_abbr = 'NE20'

　　ORDER BY

　　rgn_abbr

　　,dst_abbr

　　,ter_abbr

　　,cust_id

　　-----

　　-- Query: SPM_2.2.5

　　-- Purpose: Accumulate quantities and revenue within Sales Region and District.

　　-- for a selected Region. Note that this query +should+ take advantage

　　-- of materialized view SFA.MV_SALES_SUMMARY for most effective retrieval

　　-----

　　SELECT /*SPM_2_2.5*/

　　SR.abbr

　　,SD.abbr

　　,SUM(SH.quantity_sold)

　　,AVG(SH.quantity_sold)

　　,COUNT(SH.quantity_sold)

　　,SUM(SH.amount_sold)

　　,AVG(SH.amount_sold)

　　,COUNT(SH.amount_sold)

　　FROM

　　sfa.sales_regions SR

　　,sfa.sales_districts SD

　　,sfa.sales_zones SZ

　　,sh.customers C

　　,sh.sales SH

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND C.cust_ID = SH.cust_id

　　AND SR.abbr = :rgn_abbr

　　GROUP BY

　　SR.abbr

　　,SD.abbr

　　ORDER BY

　　SR.abbr

　　,SD.abbr

　　-----

　　-- Query: SPM_2.2.6

　　-- Purpose: Accumulate quantities and revenue within Sales Region. Note that

　　-- this query +cannot+ take advantage of SFA.MV_SALES_SUMMARY for

　　-- effective retrieval because of the selection criteria against

　　-- Region Description

　　-----

　　SELECT /*SPM_2_2.6*/

　　SR.abbr

　　,SUM(SH.quantity_sold)

　　,AVG(SH.quantity_sold)

　　,COUNT(SH.quantity_sold)

　　,SUM(SH.amount_sold)

　　,AVG(SH.amount_sold)

　　,COUNT(SH.amount_sold)

　　FROM

　　sfa.sales_regions SR

　　,sfa.sales_districts SD

　　,sfa.sales_zones SZ

　　,sh.customers C

　　,sh.sales SH

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND C.cust_ID = SH.cust_id

　　AND C.cust_id = :cust_id

　　AND SR.description LIKE :rgn_desc

　　GROUP BY

　　SR.abbr

　　ORDER BY

　　SR.abbr