PLSQL程序优化和性能测试方法

1、PLSQL程序优化和性能分析方法沈阳东软软件股份有限公司文件编号：TD文档编号版本号10分册名称第册 / 共册总页数正文附录编制张悦审批生效日期沈阳东软软件股份有限公司版本号更改条款及内容更改人审批人更改日期1. 前言 .51.1目的 .51.2文档说明 .51.3词汇表 .51.4参考资料 .62. PLSQL 程序优化原则 .62.1导致性能问题的内在原因 .62.2PLSQL 优化的核心思想 .62.3ORACLE 优化器 .72.4PLSQL 优化 .72.4.1选择最有效率的表名顺序 .72.4.2WHERE子句中的连接顺序 .82.4.3SELECT子句中避免使用 *.82.4.4

2、用 EXISTS 替代 IN .82.4.5用 NOT EXISTS替代 NOT IN .92.4.6用表连接替换 EXISTS .92.4.7用 EXISTS 替换 DISTINCT .102.4.8减少对表的查询.112.4.9避免循环（游标）里面嵌查询.122.4.10尽量用 union all替换 union .132.4.11 使用 DECODE函数来减少处理时间 .132.4.12group by 优化 .142.4.13尽量避免用 order by.142.4.14用 Where 子句替换 HAVING子句 .142.4.15使用表的别名 (Alias).152.4.16删除重复

3、记录 .152.4.17COMMIT 使用 .152.4.18减少多表关联 .162.4.19批量数据插入 .162.5 索引使用优化.172.5.1避免在索引列上使用函数或运算.172.5.2避免改变索引列的类型. .172.5.3避免在索引列上使用NOT .182.5.4用 >=替代 > .182.5.5避免在索引列上使用IS NULL和 IS NOT NULL .182.5.6带通配符（ % ）的 like 语句 .192.5.7总是使用索引的第一个列 .192.5.8多个平等的索引.192.5.9不明确的索引等级.202.5.10自动选择索引 .202.5.11使用提示 (

4、Hints).202.5.12表上存在过旧的分析. 212.5.13表上存在并行 .212.5.14关于索引建立 .213. PLSQL 程序性能问题测试方法. 223.1性能问题分析 .223.2E XPAIN PLAN 分析索引使用 .233.3TOPSQL 分析 .243.4针对性语句搜索 .283.5后台存储过程跟踪 .293.6性能监控 .错误！未定义书签。4. 性能测试工具设计思想 .错误！未定义书签。1. 前言1.1 目的性能测试是测试中比较重要的工作，性能测试应分为压力的测试和性能的测试，其中性能问题中绝大部分都是由于程序编写的不合理、不规范造成的。本文档说明了程序中常见的不优

5、化的脚本编写，导致的性能问题，并且在也描述了怎样去跟踪和解决程序上的性能问题的方法。在最后一章里面描述了做一个白盒测试工具测试性能问题的设计思想。1.2 文档说明本文档只说明 PLSQL 编写的优化问题，不包括 ORACLE 本身的性能优化（内存 SGA、系统参数、表空间等）、操作系统的性能问题和硬件的性能问题。对于 PLSQL 程序优化方面的内容有很多， 本文档列出在我们实际工作中一些常见的情况。 本文档难免有不正确的地方，也需要大家给予指正。本文档举例说明的问题语句不是实际程序中真正存在的，只是让大家能看起来更容易理解，但这些语句也不代表在我们程序中其他部分语句不存在这些问题。举例说明中的

6、语句采用的是社保核心平台的数据字典，在举例描述中没有标明表名和字段名的含义，还需单独参考。1.3 词汇表词汇名称词汇含义备注1.4 参考资料编号资料名称作者日期出版单位1 ORACLE SQL性能优化系列232. PLSQL 程序优化原则2.1 导致性能问题的内在原因导致系统性能出现问题从系统底层分析也就是如下几个原因：CPU 占用率过高，资源争用导致等待内存使用率过高，内存不足需要磁盘虚拟内存IO 占用率过高，磁盘访问需要等待2.2 PLSQL 优化的核心思想PLSQL 优化实际上就是避免出现“导致性能问题的内在原因” ，实际上编写程序，以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。

7、PLSQL 程序占用 CPU 的情况系统解析 SQL 语句执行，会消耗CPU 的使用运算（计算）会消耗CPU 的使用PLSQL 程序占用内存的情况读写数据都需要访问内存内存不足时，也会使用磁盘PLSQL 程序增大 IO 的情况读写数据都需要访问磁盘IO读取的数据越多， IO 就越大大家都知道 CPU 现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问相对前两个相比速度就差的非常大了，因此PLSQL 性能优化的重点也就是减少IO 的瓶颈，换句话说就是尽量减少IO 的访问。性能的优先级 CPU->内存 ->IO ，影响性能的因素依次递增。根据上面的分析， PLSQL 优化的

8、核心思想为：1. 避免过多复杂的 SQL 脚本，减少系统的解析过程2. 避免过多的无用的计算，例如：死循环3. 避免浪费内存空间没有必要的 SQL 脚本，导致内存不足4. 内存中计算和访问速度很快5.尽可能的减少磁盘的访问的数据量，该原则是PLSQL 优化中重要思想。6.尽可能的减少磁盘的访问的次数，该原则是PLSQL 优化中重要思想。下面的章节具体介绍常见影响性能的SQL 语句情况。2.3 ORACLE 优化器ORACLE 的优化器：a. RULE (基于规则 ) b. COST (基于成本 ) c. CHOOSE (选择性 )设置缺省的优化器 ,可以通过对 init.ora 文件中 OPT

9、IMIZER_MODE 参数的各种声明 ,如 RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在 SQL 句级或是会话(session)级对其进行覆盖 .为了使用基于成本的优化器 (CBO, Cost-Based Optimizer) , 你必须经常运行 analyze 命令 , 以增加数据库中的对象统计信息 (object statistics)的准确性 .如果数据库的优化器模式设置为选择性 (CHOOSE),那么实际的优化器模式将和是否运行过 analyze 命令有关 . 如果 table 已经被 analyze 过, 优化器模式将自动成为 CBO ,

10、 反之 , 数据库将采用 RULE 形式的优化器 .在缺省情况下 ,ORACLE采用CHOOSE 优化器 , 为了避免那些不必要的全表扫描(fulltable scan) , 你必须尽量避免使用 CHOOSE 优化器 ,而直接采用基于规则或者基于成本的优化器 .在 oracle10g 前默认的优化模式是 CHOOSE， 10g 默认是 ALL_ROWS ，我不建议大家去改动 ORACLE 的默认优化模式。2.4 PLSQL 优化主要说明了在SQL 编写上和PLSQL 程序编写上可以优化的地方。2.4.1 选择最有效率的表名顺序只在基于规则的优化器 rule 中有效，目前我们 oracle 选择

11、的优化器基本都不选择 rule ，因此该问题基本不会出现，但为了安全和规范起见，建议编程习惯采用该规则。ORACLE 的解析器按照从右到左的顺序处理FROM 子句中的表名,因此 FROM 子句中写在最后的表(基础表drivingtable)将被最先处理 . 在 FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表 .当 ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先 ,扫描第一个表 (FROM子句中最后的那个表) 并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.例如 :表 a

12、c01 有 16,384 条记录表 ab01 有 1 条记录选择 ab01 作为基础表(好的方法 )select count(*) from ac01,ab01执行时间0.96 秒选择 ac01 作为基础表(不好的方法 )select count(*) from ab01,ac01执行时间26.09 秒2.4.2 WHERE 子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句 ,根据这个原理,表之间的连接必须写在其他WHERE条件之前例如 :(低效 )SELECT ab01.aab001,ab02.aab051FROM ab01,ab02WHERE ab02.aae140= 31

13、AND ab01.aab001=ab02.aab001;(高效 )SELECT ab01.aab001,ab02.aab051FROM ab01,ab02WHERE ab01.aab001=ab02.aab001AND ab02.aae140= 31;2.4.3 SELECT 子句中避免使用*当你想在SELECT 子句中列出所有的COLUMN时 ,使用动态的是 ,这是一个非常低效的方法. 实际上 ,ORACLE在解析的过程中工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。SQL 列引用*' 是一个方便的方法, 会将 '*' 依次转换成所有的列名.不幸 , 这个

14、2.4.4 用 EXISTS 替代 IN实际情况看，使用exists 替换 in 效果不是很明显，基本一样。在许多基于基础表的查询中,为了满足一个条件, 往往需要对另一个表进行联接EXISTS( 或 NOT EXISTS) 通常将提高查询的效率.在这种情况下, 使用低效 :SELECT *FROM ac01Where aac001 in (select aac001 from ac02 where aab001=str_aab001 and aae140=31);或SELECT *FROM ac01Where aac001 in (select distinct aac001 from ac0

15、2 where aab001=str_aab001 and aae140= 31);注意使用distinct 也会影响速度高效 :SELECT *FROM ac01Where exists (select 1 from ac02 where aac001=ac01.aac001 and aab001=str_aab001 and aae140=31);in 的常量列表是优化的(例如： aab019 in (20,30)，不用 exists 替换； in 列表相当于or2.4.5 用 NOT EXISTS 替代 NOT INOracle 在 10g 之前版本not in 都是最低效的语句，虽然在

16、一些问题，因此我们一定要使用not exists 来替代 not in在子查询中 ,NOT IN 子句将执行一个内部的排序和合并它对子查询中的表执行了一个全表遍历). 为了避免使用10g 上 not in 做到了一些改进，但仍然还是存在的写法。. 无论在哪种情况下,NOT IN 都是最低效的( 因为NOT IN , 我们可以把它改写成NOT EXISTS.例如 :SELECT *FROM ac01WHERE aab001 NOT IN (SELECT aab001 from ab01 where aab020=100);为了提高效率.改写为 :SELECT *FROM ac01WHERE no

17、t exists (SELECT 1 from ab01 where aab001=ac01.aab001 and aab020= 100);2.4.6 用表连接替换 EXISTS在子查询的表和主表查询是多对一的情况，一般采用表连接的方式比EXISTS 更有效率。例如：低效：SELECT ac01.*FROM ac01Where exists (select 1 from ac02where aac001=ac01.aac001and aab001=ac01.aab001and aae140='31'and aae041='200801');高效：SELECT

18、ac01.*FROM ac02,ac01Where ac02.aac001=ac01.aac001and ac02.aab001=ac01.aab001and ac02.aae140='31'and aae041='200801'到底 exists 和表关联哪种效率高，其实是根据两个表之间的数据量差别大小是有关的，如果差别不大实际上速度基本差不多。2.4.7 用 EXISTS 替换 DISTINCT当提交一个包含一对多表信息使用 DISTINCT.一般可以考虑用(比如个人基本信息表和个人参保信息表EXISTS 替换)的查询时,避免在SELECT子句中例如 :低效

19、 :select distinct ac01.aac001from ac02,ac01where ac02.aac001 = ac01.aac001and ac02.aae140='31'and ac01.aab001='100100'高效 :select ac01.aac001from ac01where exists(select 1 from ac02 where aac001 = ac01.aac001 and aae140='31')and ac01.aab001='100100'EXISTS使查询更为迅速,因为RDBM

20、S核心模块将在子查询的条件一旦满足后,立刻返回结果。因此如果不是特别研究和追求速度的话（例如：数据转换），查询一个表的数据需要关联其他表的这种情况查询，建议采用EXISTS 的方式。2.4.8 减少对表的查询该问题是我们编程中出现过的问题，请大家一定注意，并且该类问题优化可以带来较大性能的提升。例如 :低效cursor cur_kc24_mz isSelect akc260from kc24where akb020 =str_akb020and aka130=11;cursor cur_kc24_zy isSelect akc260from kc24where akb020 =str_akb0

21、20and aka130=21;for rec_mz in cur_kc24_mz loop门诊处理.end loop;for rec_mz in cur_kc24_zy loop住院处理.end loop;高效cursor cur_kc24 isSelect akc260,aka130from kc24where akb020 =str_akb020and aka130 in ( 11,21);for rec_kc24 in cur_kc24 loopif rec_kc24.aka130= 11then门诊处理.end if;if rec_kc24.aka130= 21then住院处理.en

22、d if;end loop;高效的做法使用同样的条件（或者说是索引）只访问一次磁盘，低效的做法访问了别将近 2 倍。2 次磁盘，这样速度差2.4.9 避免循环（游标）里面嵌查询游标里面不能嵌入查询(或者再嵌游标)，其实也不能有update delete 等语句，只能有insert 语句。但在实际的编程情况下是不可能完全避免的，但我们一定要尽量避免。该类问题也是我们程序中出现过的问题，该类问题也可以大大提升程序效率，请大家一定注意。例如：低效：Cursor cur_ac04 isSelect aac001,akc010From ac04Where aab001= prm_aab001;For r

23、ec_ac04 in cur_ac04 loopSelect aac008Into str_aac008from ac01where aac001=rec_ac04.aac001;if str_aac008= 1thenn_jfje := rec_ac04.akc010*0.08;end if;if str_aac008= 2thenn_jfje := rec_ac04.akc010*0.1;end if;End loop;高效：Cursor cur_ac04 isSelect ac01.aac001,ac04.akc010,ac01.aac008From ac04,ac01Where ac0

24、4.aac001=ac01.aac001and aab001= prm_aab001;For rec_ac04 in cur_ac04 loopif rec.aac008= 1thenn_jfje := rec_ac04.akc010*0.08;end if;if rec.aac008= 2thenn_jfje := rec_ac04.akc010*0.1;end if;end loop;优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来，读到内存；如果放到游标中的话，假如游标有100 万数据量，那么程序需要了多少 IO 的访问。这样相当于只访问了 1 次磁盘 100 万次磁盘，

25、可以想象浪费如果在程序编写上没有办法避免游标中有查询语句的话（一般情况是可以避免的），那么也要保证游标中的查询使用的索引（即查询速度非常快），例如：游标100 万数据量，游标中的查询语句执行需要0.02 秒，从这个速度上来说是很快的，但总体上看100 万 *0.02 秒 =2 万秒 =5 小时 33 分钟，如果写一个不够优化的语句需要1 秒，那么需要几天能执行完呢？2.4.10尽量用 union all 替换 unionUnion 会去掉重复的记录，会有排序的动作，会浪费时间。因此在没有重复记录的情况下或可以允许有重复记录的话，要尽量采用 union all 来关联。2.4.11使用 DECO

26、DE 函数来减少处理时间使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表.例如 :（低效 )select count(1) from ac01 where aab001= 100001and aac008=1; select count(1) from ac01 where aab001= 100001and aac008=2;（低效 )Select count(1),aac008From ac01Where aab001=100001and aac008 in (1,2)group by aac008;（高效）select count(decode(aac008,1,1,n

27、ull) zz,count(decode(aac008,2,1,null) txfrom ac01where aab001=100001;特别说明：group by 和 order by 都会影响性能，编程时尽量避免没有必要的分组和排序，或者通过其他的有效的编程办法去替换，比如上面的处理办法。2.4.12group by 优化Group by 需要查询后排序，速度慢影响性能，如果查询数据量大，并且分组复杂，这样的查询语句在性能上是有问题的。尽量避免使用分组或者采用上面的一节的办法去代替。采用 group by 的也一定要进行优化。例如：低效select ac04.aac001,ac01.aac

28、002,ac01.aac003,sum(aac040),ac01.aab001from ac04,ac01where ac04.aac001=ac01.aac001 and ac01.aab001='1000000370'group by ac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001;高效：select ac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001from (select aac001,sum(aac040) gzze from ac04 group by aac001

29、) ac04,ac01where ac04.aac001=ac01.aac001and aab001='1000000370'2.4.13尽量避免用 order byOrder by 需要查询后排序，速度慢影响性能，如果查询数据量大，排序的时间就很长。但我们也不能避免不使用，这样大家一定注意一点的是如果使用 order by 那么排序的列表必须符合索引，这样在速度上会得到很大的提升。2.4.14用 Where 子句替换HAVING子句避免使用总计等操作HA VING子句 , HA VING只会在检索出所有记录之后才对结果集进行过滤. 如果能通过WHERE 子句限制记录的数目,那

30、就能减少这方面的开销.这个处理需要排序,例如 :低效 :SELECT aac008,count(1)FROM ac01GROUP BY aac008HA VING aac008 in ( 1,2);高效SELECT aac008,count(1)FROM ac01Where aac008 in (1,2)GROUP BY aac008 ;HA VING WHERE中的条件一般用于对一些集合函数的比较子句中, 如COUNT()等等 .除此而外,一般的条件应该写在2.4.15使用表的别名 (Alias)当在 SQL 语句中连接多个表时的时间并减少那些由Column, 请使用表的别名并把别名前缀于每

31、个歧义引起的语法错误.Column上 .这样一来,就可以减少解析2.4.16删除重复记录一般数据转换的程序经常会使用到该方法。最高效的删除重复记录方法( 因为使用了ROWID)DELETE FROM ac01 aWHERE a.rowid > (SELECT MIN(b.rowid)FROM ac01 bWHERE a.aac002=b.aac002and a.aac003=b.aac003 );2.4.17COMMIT 使用数据转换的程序需要关注这一点。1. Commit 执行也是有时间的，不过时间特别短，但提交频率特别大，必然也会浪费时间。2. commit 可以释放资源，在大量数据更新时，必须及时提交。a. 回滚段上用于恢复数据的信息 .b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE 为管理上述 3 种资源中的内部花费例如：Cur_ac20 有 5000 万数据n_count :=0;For arec in cur_ac20 loopInsert into ac20n_count := n_count + 1;If n_count = = 100000 then-10 万一提交commit;n_count := 0;End if;End loop;Commit;如果 1 条一提交，需要提交5000 万必然浪费时间；如果整体