Oracle 11g：SQL性能分析器（二）

来源：51CTO.com 作者：Jim Czuprynski/黄永出处：巧巧读书 2008-04-22 进入讨论组

摘要：Oracle数据库11g第一版（11gR1）新的SQL性能分析器许诺较大地减轻了忙碌的Oracle DBA的工作量，因为它提供了一个精确地评估前后性能变化的完整的数据库工作负载的方法。

本文 --关于Oracle数据库11g新的SQL性能分析器增强的特性系列文章的第二篇 -- 讨论如何使用SQL性能分析器评估一个SQL工作负载，从一个Oracle 10gR2数据库检测潜在的性能问题，

本系列前一篇文章讲解了Oracle 11g数据库新的SQL性能分析器（SPA）工具的基础知识：
◆捕获一个SQL工作负载
◆记录在当前数据库配置情况下工作负载的性能
◆记录改变数据库配置后相同工作负载的性能
◆比较数据库配置改变前后的工作负载性能

本文我将讲解SQL性能分析器如何分析更复杂的Oracle环境改变 --- 特别是，在两个Oracle数据库版本之间的基于成本的优化器（CBO）的升级 --- 以及如何将一个Oracle 10gR2生成的SQL工作负载转移到Oracle 11gR1。另外，在本系列最后一篇文章中我将集中精力讲解使用PL/SQL接口测量SQL工作负载性能，主要讲解使用Oracle 11g的企业管理器（EM）数据库控制接口用SQL性能分析器分析SQL工作负载（更简单，只需点几下鼠标即可）。

SQL性能分析情景：模拟一个优化器升级

1）准备模拟

我在Oracle 10gR2 home目录中采用标准方式安装了一个名叫DB10G的Oracle 10gR2数据库，为了衔接前一篇文章中的相同的例子，我在这个数据库上创建了SH.SALES_AGENTS表，用相同的样本集填充了大约420，000行，并在该表上创建了相同的五个索引，用列表1.1中相同的DDL语句来完成这些任务的，并且同样使用了LoadSalesAgents.sql中的DML语句来初始化这个表内容的。

2）准备收集SQL工作负载

接下来，我将在DB10G数据库上创建一个SQL工作负载，我会使用前一篇文章中相同的SQL语句访问SH.SALES_AGENTS表，随同几条额外的访问其他演示方案表的SQL语句一起，在GenerateSQLWorkload.sql中展示了组成这个SQL工作负载的语句，注意最后四条SQL语句，标记为LDGN4.1到LDGN4.4的语句使用Oracle 9i中仍然可用的旧优化器提示，但是在Oracle 10g不再完全支持了，我将使用这些语句说明SQL性能分析器是可以模拟来自其他旧的Oracle数据库环境基于成本的优化器的性能的。列表2.1中的代码捕获这些SQL语句进入一个名叫STS_SPA_200的SQL调整集。

3）导出SQL调整集

现在我已经执行了SQL工作负载并将收集的统计结果放入了STS_SPA_200 SQL调整集，我将转移那个工作负载以及它对应的执行统计数据到我的Oracle 11gR1数据库中，我将创建一个临时表来存储这些信息，转移SQL工作负载和它的性能信息到这个表中，然后，再转移那些数据到目标Oracle 11g数据库，列表2.2展示了使用存储过程DBMS_SQLTUNE.PACK_STGTAB_SQLSET转移我捕获（或打包）的SQL调整集到临时表，然后，我使用Oracle数据泵导出工具导出那些临时表到一个叫做DumpStagingTable.dmp的数据泵导出文件。

4）导入、解包和为执行测试准备SQL调整集

在复制数据泵导出文件集到Oracle 11g数据库默认的数据泵导出目录后，我将需要转移临时表中的SQL调整集信息到我的Oracle 11g目标数据库，要分析的就是这些数据，列表2.3举例说明了我如何使用数据泵导入工具和一个正确的参数文件从Oracle 10gR2向Oracle 11gR1数据库导入临时表，然后，我用存储过程DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET解包存储在临时表中的SQL工作负载到我的目标Oracle 11gR1数据库。

5）模拟一个从10.2.0.1到11.0.1.6的优化器升级

现在我的准备工作完成了，我准备让SQL性能分析器任其自由地发现导入的SQL工作负载中的SQL语句的显着的性能影响，我将使用Oracle数据库11g的企业管理器数据库控制接口执行这个分析任务，图2.1展示了SQL性能分析器面板的初始状态。

图2.1 SQL性能分析器主面板

当我在这个面板上选择优化器升级模拟（Optimizer Upgrade Simulation）链接时，企业管理器转到优化器升级模拟面板，如图2.2所示，我具体指定了一个任务名SPA_OUS_100，SYS.STS_SPA_200作为SQL调整集，以及任务的简要描述，注意在这个任务上，我分别指定了“之前”和“之后”的优化器版本为10.2.0.1和11.1.0.6。

图2.2创建一个优化器升级模拟任务