PLSQL程序优化和性能分析方法.doc

前言 目的 性能测试是测试中比较重要的工作，性能测试应分为压力的测试和性能的测试，其中性能问题中绝大部分都是由于程序编写的不合理、不规范造成的。本文档说明了程序中常见的不优化的脚本编写，导致的性能问题，并且在也描述了怎样去跟踪和解决程序上的性能问题的方法。 在最后一章里面描述了做一个白盒测试工具测试性能问题的设计思想。 文档说明 本文档只说明PLSQL编写的优化问题，不包括ORACLE本身的性能优化（内存SGA、系统参数、表空间等）、操作系统的性能问题和硬件的性能问题。对于PLSQL程序优化方面的内容有很多，本文档列出在我们实际工作中一些常见的情况。本文档难免有不正确的地方，也需要大家给予指正。 本文档举例说明的问题语句不是实际程序中真正存在的，只是让大家能看起来更容易理解，但这些语句也不代表在我们程序中其他部分语句不存在这些问题。 举例说明中的语句采用的是社保核心平台的数据字典，在举例描述中没有标明表名和字段名的含义，还需单独参考。 词汇表 词汇名称 词汇含义 备注 参考资料 编号 资料名称 作者 日期 出版单位 《ORACLE SQL性能优化系列 PLSQL程序优化原则 导致性能问题的内在原因 导致系统性能出现问题从系统底层分析也就是如下几个原因： CPU占用率过高，资源争用导致等待 内存使用率过高，内存不足需要磁盘虚拟内存 IO占用率过高，磁盘访问需要等待 PLSQL优化的核心思想 PLSQL优化实际上就是避免出现“导致性能问题的内在原因”，实际上编写程序，以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。 PLSQL程序占用CPU的情况 系统解析SQL语句执行，会消耗CPU的使用 运算（计算）会消耗CPU的使用 PLSQL程序占用内存的情况 读写数据都需要访问内存 内存不足时，也会使用磁盘 PLSQL程序增大IO的情况 读写数据都需要访问磁盘IO 读取的数据越多，IO就越大 大家都知道CPU现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问相对前两个相比速度就差的非常大了，因此PLSQL性能优化的重点也就是减少IO的瓶颈，换句话说就是尽量减少IO的访问。 性能的优先级CPU-内存-IO，影响性能的因素依次递增。根据上面的分析，PLSQL优化的核心思想为： 避免过多复杂的SQL脚本，减少系统的解析过程 避免过多的无用的计算，例如：死循环 避免浪费内存空间没有必要的SQL脚本，导致内存不足 内存中计算和访问速度很快 尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想。 尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想。 下面的章节具体介绍常见影响性能的SQL语句情况。 ORACLE优化器 ORACLE的优化器a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.选择最有效率的表名顺序只在基于规则的优化器中有效ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表 16,384 条记录 表 1 条记录 选择作为基础表 (好的方法)