PLSQL程序优化和性能分析方法.docx
《PLSQL程序优化和性能分析方法.docx》由会员分享，可在线阅读，更多相关《PLSQL程序优化和性能分析方法.docx（30页珍藏版）》请在冰豆网上搜索。
PLSQL程序优化和性能分析方法
1.前言
1.1目的
性能测试是测试中比较重要的工作，性能测试应分为压力的测试和性能的测试，其中性能问题中绝大部分都是由于程序编写的不合理、不规范造成的。
本文档说明了程序中常见的不优化的脚本编写，导致的性能问题，并且在也描述了怎样去跟踪和解决程序上的性能问题的方法。
在最后一章里面描述了做一个白盒测试工具测试性能问题的设计思想。
1.2文档说明
本文档只说明PLSQL编写的优化问题，不包括ORACLE本身的性能优化（内存SGA、系统参数、表空间等）、操作系统的性能问题和硬件的性能问题。
对于PLSQL程序优化方面的内容有很多，本文档列出在我们实际工作中一些常见的情况。
本文档难免有不正确的地方，也需要大家给予指正。
本文档举例说明的问题语句不是实际程序中真正存在的，只是让大家能看起来更容易理解，但这些语句也不代表在我们程序中其他部分语句不存在这些问题。
举例说明中的语句采用的是社保核心平台的数据字典，在举例描述中没有标明表名和字段名的含义，还需单独参考。
1.3词汇表
词汇名称
词汇含义
备注
1.4参考资料
编号
资料名称
作者
日期
出版单位
1
《ORACLESQL性能优化系列》
2
3
2.PLSQL程序优化原则
2.1导致性能问题的内在原因
导致系统性能出现问题从系统底层分析也就是如下几个原因：
●CPU占用率过高，资源争用导致等待
●内存使用率过高，内存不足需要磁盘虚拟内存
●IO占用率过高，磁盘访问需要等待
2.2PLSQL优化的核心思想
PLSQL优化实际上就是避免出现“导致性能问题的内在原因”，实际上编写程序，以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。
●PLSQL程序占用CPU的情况
⏹系统解析SQL语句执行，会消耗CPU的使用
⏹运算（计算）会消耗CPU的使用
●PLSQL程序占用内存的情况
⏹读写数据都需要访问内存
⏹内存不足时，也会使用磁盘
●PLSQL程序增大IO的情况
⏹读写数据都需要访问磁盘IO
⏹读取的数据越多，IO就越大
大家都知道CPU现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问相对前两个相比速度就差的非常大了，因此PLSQL性能优化的重点也就是减少IO的瓶颈，换句话说就是尽量减少IO的访问。
性能的优先级CPU->内存->IO，影响性能的因素依次递增。
根据上面的分析，PLSQL优化的核心思想为：
1.避免过多复杂的SQL脚本，减少系统的解析过程
2.避免过多的无用的计算，例如：
死循环
3.避免浪费内存空间没有必要的SQL脚本，导致内存不足
4.内存中计算和访问速度很快
5.尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想。
6.尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想。
下面的章节具体介绍常见影响性能的SQL语句情况。
2.3ORACLE优化器
ORACLE的优化器：
a.RULE（基于规则）b.COST（基于成本）c.CHOOSE（选择性）
设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话（session）级对其进行覆盖.
为了使用基于成本的优化器（CBO,Cost-BasedOptimizer）,你必须经常运行analyze命令,以增加数据库中的对象统计信息（objectstatistics）的准确性.
如果数据库的优化器模式设置为选择性（CHOOSE）,那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.
在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描（fulltablescan）,你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.
在oracle10g前默认的优化模式是CHOOSE，10g默认是ALL_ROWS，我不建议大家去改动ORACLE的默认优化模式。
2.4PLSQL优化
主要说明了在SQL编写上和PLSQL程序编写上可以优化的地方。
2.4.1选择最有效率的表名顺序
只在基于规则的优化器rule中有效，目前我们oracle选择的优化器基本都不选择rule，因此该问题基本不会出现，但为了安全和规范起见，建议编程习惯采用该规则。
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表（基础表drivingtable）将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表（FROM子句中最后的那个表）并对记录进行派序,然后扫描第二个表（FROM子句中最后第二个表）,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如:
表ac01有16,384条记录
表ab01有1条记录
选择ab01作为基础表（好的方法）
selectcount（*）fromac01,ab01执行时间0.96秒
选择ac01作为基础表（不好的方法）
selectcount（*）fromab01,ac01执行时间26.09秒
2.4.2WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前
例如:
（低效）
SELECTab01.aab001,ab02.aab051
FROMab01,ab02
WHEREab02.aae140=’31’
ANDab01.aab001=ab02.aab001;
（高效）
SELECTab01.aab001,ab02.aab051
FROMab01,ab02
WHEREab01.aab001=ab02.aab001
ANDab02.aae140=’31’;
2.4.3SELECT子句中避免使用‘*‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*'是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。
2.4.4用EXISTS替代IN
实际情况看，使用exists替换in效果不是很明显，基本一样。
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS（或NOTEXISTS）通常将提高查询的效率.
低效:
SELECT*
FROMac01
Whereaac001in（selectaac001fromac02whereaab001=str_aab001andaae140=’31’）;
或
SELECT*
FROMac01
Whereaac001in（selectdistinctaac001fromac02whereaab001=str_aab001andaae140=’31’）;
注意使用distinct也会影响速度
高效:
SELECT*
FROMac01
Whereexists（select1fromac02whereaac001=ac01.aac001andaab001=str_aab001andaae140=’31’）;
in的常量列表是优化的（例如：
aab019in（‘20’,’30’）），不用exists替换；in列表相当于or
2.4.5用NOTEXISTS替代NOTIN
Oracle在10g之前版本notin都是最低效的语句，虽然在10g上notin做到了一些改进，但仍然还是存在一些问题，因此我们一定要使用notexists来替代notin的写法。
在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的（因为它对子查询中的表执行了一个全表遍历）.为了避免使用NOTIN,我们可以把它改写成NOTEXISTS.
例如:
SELECT*
FROMac01
WHEREaab001NOTIN（SELECTaab001fromab01whereaab020=’100’）;
为了提高效率.改写为:
SELECT*
FROMac01
WHEREnotexists（SELECT1fromab01whereaab001=ac01.aab001andaab020=’100’）;
2.4.6用表连接替换EXISTS
在子查询的表和主表查询是多对一的情况，一般采用表连接的方式比EXISTS更有效率。
例如：
低效：
SELECTac01.*
FROMac01
Whereexists（select1fromac02
whereaac001=ac01.aac001
andaab001=ac01.aab001
andaae140='31'
andaae041='200801'）;
高效：
SELECTac01.*
FROMac02,ac01
Whereac02.aac001=ac01.aac001
andac02.aab001=ac01.aab001
andac02.aae140='31'
andaae041='200801';
到底exists和表关联哪种效率高，其实是根据两个表之间的数据量差别大小是有关的，如果差别不大实际上速度基本差不多。
2.4.7用EXISTS替换DISTINCT
当提交一个包含一对多表信息（比如个人基本信息表和个人参保信息表）的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXISTS替换
例如:
低效:
selectdistinctac01.aac001
fromac02,ac01
whereac02.aac001=ac01.aac001
andac02.aae140='31'
andac01.aab001='100100';
高效:
selectac01.aac001
fromac01
whereexists（select1fromac02whereaac001=ac01.aac001
andaae140='31'）
andac01.aab001='100100';
EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
因此如果不是特别研究和追求速度的话（例如：
数据转换），查询一个表的数据需要关联其他表的这种情况查询，建议采用EXISTS的方式。
2.4.8减少对表的查询
该问题是我们编程中出现过的问题，请大家一定注意，并且该类问题优化可以带来较大性能的提升。
例如:
低效
cursorcur_kc24_mzis
Selectakc260
fromkc24
whereakb020=str_akb020
andaka130=’11’;
cursorcur_kc24_zyis
Selectakc260
fromkc24
whereakb020=str_akb020
andaka130=’21’;
forrec_mzincur_kc24_mzloop
门诊处理…..
endloop;
forrec_mzincur_kc24_zyloop
住院处理…..
endloop;
高效
cursorcur_kc24is
Selectakc260,aka130
fromkc24
whereakb020=str_akb020
andaka130in（’11’,’21’）;
forrec_kc24incur_kc24loop
ifrec_kc24.aka130=’11’then
门诊处理…..
endif;
ifrec_kc24.aka130=’21’then
住院处理…..
endif;
endloop;
高效的做法使用同样的条件（或者说是索引）只访问一次磁盘，低效的做法访问了2次磁盘，这样速度差别将近2倍。
2.4.9避免循环（游标）里面嵌查询
游标里面不能嵌入查询（或者再嵌游标），其实也不能有updatedelete等语句，只能有insert语句。
但在实际的编程情况下是不可能完全避免的，但我们一定要尽量避免。
该类问题也是我们程序中出现过的问题，该类问题也可以大大提升程序效率，请大家一定注意。
例如：
低效：
Cursorcur_ac04is
Selectaac001,akc010
Fromac04
Whereaab001=prm_aab001;
……
Forrec_ac04incur_ac04loop
Selectaac008
Intostr_aac008
fromac01
whereaac001=rec_ac04.aac001;
ifstr_aac008=’1’then
n_jfje:
=rec_ac04.akc010*0.08;
endif;
ifstr_aac008=’2’then
n_jfje:
=rec_ac04.akc010*0.1;
endif;
Endloop;
高效：
Cursorcur_ac04is
Selectac01.aac001,ac04.akc010,ac01.aac008
Fromac04,ac01
Whereac04.aac001=ac01.aac001
andaab001=prm_aab001;
……
Forrec_ac04incur_ac04loop
ifrec.aac008=’1’then
n_jfje:
=rec_ac04.akc010*0.08;
endif;
ifrec.aac008=’2’then
n_jfje:
=rec_ac04.akc010*0.1;
endif;
endloop;
优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来，这样相当于只访问了1次磁盘读到内存；如果放到游标中的话，假如游标有100万数据量，那么程序需要100万次磁盘，可以想象浪费了多少IO的访问。
如果在程序编写上没有办法避免游标中有查询语句的话（一般情况是可以避免的），那么也要保证游标中的查询使用的索引（即查询速度非常快），例如：
游标100万数据量，游标中的查询语句执行需要0.02秒，从这个速度上来说是很快的，但总体上看100万*0.02秒=2万秒=5小时33分钟，如果写一个不够优化的语句需要1秒，那么需要几天能执行完呢？
2.4.10尽量用unionall替换union
Union会去掉重复的记录，会有排序的动作，会浪费时间。
因此在没有重复记录的情况下或可以允许有重复记录的话，要尽量采用unionall来关联。
2.4.11使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
（低效）
selectcount
（1）fromac01whereaab001=’100001’andaac008=’1’;
selectcount
（1）fromac01whereaab001=’100001’andaac008=’2’;
（低效）
Selectcount
（1）,aac008
Fromac01
Whereaab001=’100001’
andaac008in（’1’,’2’）
groupbyaac008;
（高效）
selectcount（decode（aac008,’1’,’1’,null））zz,
count（decode（aac008,’2’,’1’,null））tx
fromac01
whereaab001=’100001’;
特别说明：
groupby和orderby都会影响性能，编程时尽量避免没有必要的分组和排序，或者通过其他的有效的编程办法去替换，比如上面的处理办法。
2.4.12groupby优化
Groupby需要查询后排序，速度慢影响性能，如果查询数据量大，并且分组复杂，这样的查询语句在性能上是有问题的。
尽量避免使用分组或者采用上面的一节的办法去代替。
采用groupby的也一定要进行优化。
例如：
低效
selectac04.aac001,ac01.aac002,ac01.aac003,sum（aac040）,ac01.aab001
fromac04,ac01
whereac04.aac001=ac01.aac001andac01.aab001='1000000370'
groupbyac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001;
高效：
selectac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001
from（selectaac001,sum（aac040）gzzefromac04groupbyaac001）ac04,ac01
whereac04.aac001=ac01.aac001
andaab001='1000000370';
2.4.13尽量避免用orderby
Orderby需要查询后排序，速度慢影响性能，如果查询数据量大，排序的时间就很长。
但我们也不能避免不使用，这样大家一定注意一点的是如果使用orderby那么排序的列表必须符合索引，这样在速度上会得到很大的提升。
2.4.14用Where子句替换HAVING子句
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
例如:
低效:
SELECTaac008,count
（1）
FROMac01
GROUPBYaac008
HAVINGaac008in（‘1’,’2’）;
高效
SELECTaac008,count
（1）
FROMac01
Whereaac008in（‘1’,’2’）
GROUPBYaac008;
HAVING中的条件一般用于对一些集合函数的比较,如COUNT（）等等.除此而外,一般的条件应该写在WHERE子句中
2.4.15使用表的别名（Alias）
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
2.4.16删除重复记录
一般数据转换的程序经常会使用到该方法。
最高效的删除重复记录方法（因为使用了ROWID）
DELETEFROMac01a
WHEREa.rowid>（SELECTMIN（b.rowid）
FROMac01b
WHEREa.aac002=b.aac002
anda.aac003=b.aac003）;
2.4.17COMMIT使用
数据转换的程序需要关注这一点。
1.Commit执行也是有时间的，不过时间特别短，但提交频率特别大，必然也会浪费时间。
2.commit可以释放资源，在大量数据更新时，必须及时提交。
a.回滚段上用于恢复数据的信息.
b.被程序语句获得的锁
c.redologbuffer中的空间
d.ORACLE为管理上述3种资源中的内部花费
例如：
Cur_ac20有5000万数据
n_count:
=0;
Forarecincur_ac20loop
Insertintoac20……
n_count:
=n_count+1;
Ifn_count==100000then--10万一提交
commit;
n_count:
=0;
Endif;
Endloop;
Commit;
如果1条一提交，需要提交5000万必然浪费时间；如果整体提交，资源不能释放，性能必须下降。
在实际编程时，应注意提交的次数和提交的数据量的平衡关系。
2.4.18减少多表关联
表关联的越多，查询速度就越慢，尽量减少多个表的关联，建议表关联不要超过3个（子查询也属于表关联）。
数据转换上会存在大数据量表的关联，关联多了会影响索引的效率，可以采用建立临时表的办法，有时更能提高速度。
2.4.19批量数据插入
数据转换时或者大业务数据插入时，有以下几种办法进行数据插入（不包括imp、impdp和sqlloader）
●Insertinto…select方式
将查询的结果一次插入到目标表中。
例如：
Insertintoac01_bakselect*fromac01;
由于是一次查询一次插入，并且最后一次提交，他的速度要比下面描述的curosr的方式速度要快。
但查询插入的数据量过大必然会占用更多的内存和undo表空间，只能在插入完成后提交，这样资源不能释放，会导致回滚表空间不足和快照过旧的问题，另外一旦失败需要全部回滚。
因此建议小数据量（例如：
300万以下）的导入采用该种方式。
●Insert/*+append*/into…select方式
该种方式同上种方式，不过由于有append的提示，这种语句不走回滚段直接插入数据文件，速度非常快。
注意系统开发编程不能使用该种方式，数据转换可以灵活使用。
●Cursor方式
定义游标，然后逐行进行插入，然后定量提交。
例如：
Cusorcur_ac20is
Select*fromac20;
….
n_count:
=0;
Forrec_ac20incur_ac20loop
Insertintoac20_bak
（aac001,
…….）
Values
（rec_ac20.
升级会员 