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

1、PLSQL程序优化和性能分析方法1. 前言1.1 目的性能测试是测试中比较重要的工作，性能测试应分为压力的测试和性能的测试，其中性能问题中绝大部分都是由于程序编写的不合理、不规范造成的。本文档说明了程序中常见的不优化的脚本编写，导致的性能问题，并且在也描述了怎样去跟踪和解决程序上的性能问题的方法。在最后一章里面描述了做一个白盒测试工具测试性能问题的设计思想。1.2 文档说明本文档只说明PLSQL编写的优化问题，不包括ORACLE本身的性能优化（内存SGA、系统参数、表空间等）、操作系统的性能问题和硬件的性能问题。对于PLSQL程序优化方面的内容有很多，本文档列出在我们实际工作中一些常见的情况。

2、本文档难免有不正确的地方，也需要大家给予指正。本文档举例说明的问题语句不是实际程序中真正存在的，只是让大家能看起来更容易理解，但这些语句也不代表在我们程序中其他部分语句不存在这些问题。举例说明中的语句采用的是社保核心平台的数据字典，在举例描述中没有标明表名和字段名的含义，还需单独参考。1.3 词汇表词汇名称词汇含义备注1.4 参考资料编号资料名称作者日期出版单位1 ORACLE SQL性能优化系列2 3 2. PLSQL程序优化原则2.1 导致性能问题的内在原因导致系统性能出现问题从系统底层分析也就是如下几个原因： CPU占用率过高，资源争用导致等待 内存使用率过高，内存不足需要磁盘虚拟内存

3、IO占用率过高，磁盘访问需要等待2.2 PLSQL优化的核心思想PLSQL优化实际上就是避免出现“导致性能问题的内在原因”，实际上编写程序，以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。 PLSQL程序占用CPU的情况 系统解析SQL语句执行，会消耗CPU的使用 运算（计算）会消耗CPU的使用 PLSQL程序占用内存的情况 读写数据都需要访问内存 内存不足时，也会使用磁盘 PLSQL程序增大IO的情况 读写数据都需要访问磁盘IO 读取的数据越多，IO就越大大家都知道CPU现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问相对前两个相比速度就差的非常大了，因此PLSQL性能优

4、化的重点也就是减少IO的瓶颈，换句话说就是尽量减少IO的访问。性能的优先级CPU-内存-IO，影响性能的因素依次递增。根据上面的分析，PLSQL优化的核心思想为：1. 避免过多复杂的SQL脚本，减少系统的解析过程2. 避免过多的无用的计算，例如：死循环3. 避免浪费内存空间没有必要的SQL脚本，导致内存不足4. 内存中计算和访问速度很快5. 尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想。6. 尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想。下面的章节具体介绍常见影响性能的SQL语句情况。2.3 ORACLE优化器ORACLE的优化器：a. RULE (基于规

5、则) 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),那么实际的优化器模式将和是否运行过analy

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

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

8、如: 表 ac01有 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.aab051 FROM ab01,ab02 WHERE ab02.aae1

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

10、TS替代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 ac02 where aab001=s

11、tr_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都是最低效的语句，虽然在10g上not in做到了一些改进，但仍然还是存在一些问题，

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

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

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

15、001and ac02.aae140=31and ac01.aab001=100100;高效: select ac01.aac001from ac01where exists(select 1 from ac02 where aac001 = ac01.aac001and aae140=31)and ac01.aab001=100100;EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。因此如果不是特别研究和追求速度的话（例如：数据转换），查询一个表的数据需要关联其他表的这种情况查询，建议采用EXISTS的方式。2.4.8 减少对表的查询该问题是我们

16、编程中出现过的问题，请大家一定注意，并且该类问题优化可以带来较大性能的提升。例如: 低效 cursor cur_kc24_mz isSelect akc260from kc24where akb020 =str_akb020and aka130=11; cursor cur_kc24_zy isSelect akc260from kc24where akb020 =str_akb020and aka130=21;for rec_mz in cur_kc24_mz loop 门诊处理.end loop; for rec_mz in cur_kc24_zy loop 住院处理.end loop;高

17、效 cursor cur_kc24 isSelect akc260,aka130from kc24where akb020 =str_akb020and aka130 in (11,21);for rec_kc24 in cur_kc24 loop if rec_kc24.aka130=11 then 门诊处理. end if; if rec_kc24.aka130=21 then 住院处理.end if; end loop;高效的做法使用同样的条件（或者说是索引）只访问一次磁盘，低效的做法访问了2次磁盘，这样速度差别将近2倍。2.4.9 避免循环（游标）里面嵌查询游标里面不能嵌入查询(或者再

18、嵌游标)，其实也不能有update delete等语句，只能有insert语句。但在实际的编程情况下是不可能完全避免的，但我们一定要尽量避免。该类问题也是我们程序中出现过的问题，该类问题也可以大大提升程序效率，请大家一定注意。例如：低效：Cursor cur_ac04 is Select aac001,akc010 From ac04 Where aab001= prm_aab001;For rec_ac04 in cur_ac04 loop Select aac008 Into str_aac008 from ac01where aac001=rec_ac04.aac001; if str_

19、aac008=1 then n_jfje := rec_ac04.akc010*0.08; end if; if str_aac008=2 then n_jfje := rec_ac04.akc010*0.1; end if;End loop;高效：Cursor cur_ac04 is Select ac01.aac001,ac04.akc010,ac01.aac008 From ac04,ac01 Where ac04.aac001=ac01.aac001and aab001= prm_aab001;For rec_ac04 in cur_ac04 loop if rec.aac008=1

20、then n_jfje := rec_ac04.akc010*0.08; end if; if rec.aac008=2 then n_jfje := rec_ac04.akc010*0.1; end if;end loop;优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来，这样相当于只访问了1次磁盘读到内存；如果放到游标中的话，假如游标有100万数据量，那么程序需要100万次磁盘，可以想象浪费了多少IO的访问。如果在程序编写上没有办法避免游标中有查询语句的话（一般情况是可以避免的），那么也要保证游标中的查询使用的索引（即查询速度非常快），例如：游标100万数据量，游标中的查询

21、语句执行需要0.02秒，从这个速度上来说是很快的，但总体上看100万*0.02秒=2万秒=5小时33分钟，如果写一个不够优化的语句需要1秒，那么需要几天能执行完呢？2.4.10 尽量用union all替换unionUnion会去掉重复的记录，会有排序的动作，会浪费时间。因此在没有重复记录的情况下或可以允许有重复记录的话，要尽量采用union all来关联。2.4.11 使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 例如: （低效)select count(1) from ac01 where aab001=100001 and aac00

22、8=1; select count(1) from ac01 where aab001=100001 and aac008=2;（低效)Select count(1),aac008 From ac01Where aab001=100001 and aac008 in (1,2)group by aac008;（高效）select count(decode(aac008,1,1,null) zz,count(decode(aac008,2,1,null) txfrom ac01where aab001=100001;特别说明：group by和order by 都会影响性能，编程时尽量避免没有必

23、要的分组和排序，或者通过其他的有效的编程办法去替换，比如上面的处理办法。2.4.12 group by优化Group by需要查询后排序，速度慢影响性能，如果查询数据量大，并且分组复杂，这样的查询语句在性能上是有问题的。尽量避免使用分组或者采用上面的一节的办法去代替。采用group by的也一定要进行优化。例如：低效select ac04.aac001,ac01.aac002,ac01.aac003,sum(aac040),ac01.aab001 from ac04,ac01 where ac04.aac001=ac01.aac001 and ac01.aab001=1000000370 gr

24、oup by ac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001;高效：select ac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001 from (select aac001,sum(aac040) gzze from ac04 group by aac001) ac04,ac01 where ac04.aac001=ac01.aac001 and aab001=1000000370;2.4.13 尽量避免用order byOrder by需要查询后排序，速度慢影响性能，如果查询数据量大，排序的

25、时间就很长。但我们也不能避免不使用，这样大家一定注意一点的是如果使用order by那么排序的列表必须符合索引，这样在速度上会得到很大的提升。2.4.14 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 例如: 低效: SELECT aac008,count(1) FROM ac01GROUP BY aac008 HAVING aac008 in (1,2); 高效 SELECT aac008,count(1) FROM

26、 ac01Where aac008 in (1,2)GROUP BY aac008 ;HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中2.4.15 使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.2.4.16 删除重复记录一般数据转换的程序经常会使用到该方法。最高效的删除重复记录方法 ( 因为使用了ROWID) DELETE FROM ac01 aWHERE a.rowid (SELECT

27、 MIN(b.rowid) FROM ac01 bWHERE a.aac002=b.aac002and a.aac003=b.aac003 );2.4.17 COMMIT使用数据转换的程序需要关注这一点。1. Commit执行也是有时间的，不过时间特别短，但提交频率特别大，必然也会浪费时间。2. commit可以释放资源，在大量数据更新时，必须及时提交。a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费例如：Cur_ac20有5000万数据n_count :=0;For arec in

28、cur_ac20 loop Insert into ac20 n_count := n_count + 1; If n_count = = 100000 then -10万一提交 commit; n_count := 0; End if;End loop;Commit;如果1条一提交，需要提交5000万必然浪费时间；如果整体提交，资源不能释放，性能必须下降。在实际编程时，应注意提交的次数和提交的数据量的平衡关系。2.4.18 减少多表关联表关联的越多，查询速度就越慢，尽量减少多个表的关联，建议表关联不要超过3个（子查询也属于表关联）。数据转换上会存在大数据量表的关联，关联多了会影响索引的效率，

29、可以采用建立临时表的办法，有时更能提高速度。2.4.19 批量数据插入数据转换时或者大业务数据插入时，有以下几种办法进行数据插入（不包括imp、impdp和sqlloader） Insert into select 方式将查询的结果一次插入到目标表中。例如：Insert into ac01_bak select * from ac01;由于是一次查询一次插入，并且最后一次提交，他的速度要比下面描述的curosr的方式速度要快。但查询插入的数据量过大必然会占用更多的内存和undo表空间，只能在插入完成后提交，这样资源不能释放，会导致回滚表空间不足和快照过旧的问题，另外一旦失败需要全部回滚。因此建议小数据量（例如：300万以下）的导入采用该种方式。 Insert /*+append */ into select方式该种方式同上种方式，不过由于有append的提示，这种语句不走回滚段直接插入数据文件，速度非常快。注意系统开发编程不能使用该种方式，数据转换可以灵活使用。 Cursor方式定义游标，然后逐行进行插入，然后定量提交。例如：Cusor cur_ac20 is Select * from ac20;.n_count :=0;For rec_ac20 in cur_ac20 loop Insert into ac20_bak (aac001,.) Values (rec_ac20.