Oracle 11g：SQL性能分析器（三）(1)

作者: Jim Czuprynski/黄永兵 译 出处:51CTO.com　

 

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

摘要：Oracle数据库11g第一版（11gR1）新的SQL性能分析器许诺较大地减轻了忙碌的Oracle DBA的工作量，因为它提供了一个精确地评估前后性能变化的完整的数据库工作负载的方法。本文 --关于Oracle数据库11g新的SQL性能分析器增强的特性系列文章的第三篇 -- 探讨SQL性能分析器在修改了数据库初始化参数前后如何有效地分析SQL语句性能的改变。

续本系列前面的文章探讨Oracle数据库11g的新SQL性能分析器（SPA）工具集可以做些什么：

◆从Oracle 10gR2数据库转移一个SQL工作负载到Oracle 11gR1数据库
◆使用SPA企业管理器接口执行并分析SQL工作负载
◆评估运行在两个不同基于成本的优化器（CBO）之间的SQL工作负载SQL语句的性能

本文 -- 本系列的最后一篇 -- 将展示如何使用SPA处理让人抓狂的SQL性能调整挑战：修改数据库初始化参数对SQL语句性能的影响。我也将探讨更多的SQL性能分析器的报告和分析选项，以全面了解引起SQL语句性能倒退的原因，以及这个新工具如何帮助SQL计划管理器创建SQL计划基线。

评估初始化参数改变：准备工作

如前面两篇文章提到的，执行SQL性能分析任务最重要的第一步是为评估识别、准备以及捕获目标SQL语句：

为模拟做准备

我将使用与前面两篇文章相同的例子，除了在本例中我只需要访问我的样本Oracle 11gR1数据库。同样，我使用表SH.SALES_AGENTS和样本方案中的其他表，列表1.1和LoadSalesAgents.sql中分布包括了创建表SH.SALES_AGENTS和填充初始化数据的DDL和DML代码。

收集SQL工作负载

只要对不同初始化参数的值进行对比，就很容易找出所有性能得到改进的或倒退的SQL语句，我创建了一个由4条SQL语句组成的SQL工作负载：

◆语句LDGN5.1使用一个谓词在表SH.SALES_AGENTS中搜索所有最后的名字以“pitt”开头的Sales Agent条目，如果初始化参数OPTIMIZER_INDEX_COST_ADJ被设置为一个比它默认值100小的数值，基于成本的优化器（CBO）将倾向于在表上进行索引搜索，因为LAST_NAME列上建立有索引，CBO可能认为这个查询的性能受益于那个索引，可以更快速地检索到结果集，当然，如果真这样的话，使用索引会比进行全表扫描的成本肯定要低。
◆语句LDGN5.2从销售历史（SH）方案中的SALES表中从顾客和产品维度进行销售额数量汇总，CBO可能认为这个SQL语句会从初始化参数OPTIMIZER_INDEX_CACHING一个不同的设置中受益，当设置为一个大于它的默认值（0）的值时，这个参数告诉CBO应该多长时间一次在数据库缓存中查找已经缓存了的索引块。
◆语句LDGN5.3从销售历史（SH）方案的多个表中收集并汇总销售数据，如果初始化参数STAR_TRANSFORMATION_ENABLED被设置为TRUE（默认值是FALSE）的话，该语句可能获得更好的性能。
◆最后，语句LDGN5.4使用一个谓词在表SH.SALES_AGENTS中基于主键SALESPERSON_ID搜索大量的条目，与LDGN5.1一样，如果初始化参数OPTIMIZER_INDEX_COST_ADJ被设置为一个低于它的默认值100的数值时，CBO将倾向于利用主键索引来更快速地查找这些行。

组成这个SQL工作负载的语句在GenerateSPAWorkload_3.sql中，列表3.1中的代码捕获这些SQL语句进入一个叫做STS_SPA_300的SQL调整集。