PLSQL程序性能分析及优化.docx
《PLSQL程序性能分析及优化.docx》由会员分享，可在线阅读，更多相关《PLSQL程序性能分析及优化.docx（40页珍藏版）》请在冰点文库上搜索。
PLSQL程序性能分析及优化
PLSQL程序
性能分析及优化
巨龙软件工程公司
（总工办）
文件编号：
TD
文档编号
版本号
0.1
文档名称
第册/共册
总页数
28
正文
24
附录
编制
赵欣
审批
生效日期
巨龙软件工程公司
版本号
更改条款及内容
更改人
审批人
更改日期
0.1
1.前言5
1.1目的5
1.2文档说明5
1.3词汇表5
1.4参考资料5
2.程序性能分析方法6
2.1ExpainPlan6
3.PLSQL优化的核心思想8
3.1导致性能问题的内在原因8
3.2PLSQL优化的核心思想8
4.程序书写规范及优化原则9
4.1书写方面9
4.1.1SQL语句要统一成大写9
4.1.2涉及到多表检索时，明确地为每个字段指定表名9
4.1.3SELECT子句中避免使用’*’10
4.2条件语句10
4.2.1在进行多个表连接时，FROM中的表的顺序要按照记录数由多到少的顺序来排列10
4.2.2可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾11
4.2.3WHERE子句中的连接顺序12
4.2.4不要通过LIKE运算来执行中间一致或后方一致的检索12
4.2.5用EXISTS替代IN12
4.2.6用NOTEXISTS替代NOTIN13
4.2.7用表连接替换EXISTS14
4.2.8用EXISTS替换DISTINCT14
4.2.9用Where子句替换HAVING子句15
4.2.10对于经常使用的SQL语句（循环处理中使用的SQL等），可以通过预编译、绑定变量来提高性能15
4.3索引的使用16
4.3.1对于索引列不要执行NULL值的检索16
4.3.2对于索引列，不要使用"NOT"、"!
="、"<>"比较运算16
4.3.3用>=替代>17
4.3.4对于索引列不要使用函数和计算式17
4.3.5对于多键值索引，要按照索引的定义顺序来使用17
4.3.6避免改变索引列的类型18
4.3.7多个平等的索引18
4.3.1不明确的索引等级19
4.3.2自动选择索引19
4.3.3使用提示（Hints）19
4.3.1表上存在过旧的分析20
4.3.1表上存在并行20
4.3.1关于索引建立20
4.3.2当有多个索引可供选择时，使用的是DB设计者所希望的索引20
4.4存储函数21
4.4.1对于经常使用的SQL语句（循环处理中使用的SQL等），可以通过预编译、绑定变量来提高性能21
4.5分组和排序的使用21
4.5.1去掉没有意义的GROUPBY、ORDERBY子语21
4.5.2groupby优化22
4.5.3尽量避免用orderby22
4.6优化22
4.6.1减少对表的查询22
4.6.2避免循环（游标）里面嵌查询24
4.6.3尽量用unionall替换union25
4.6.4使用DECODE函数来减少处理时间25
4.6.5避免全表扫描的查询方式26
4.6.6删除重复记录26
4.6.7COMMIT使用26
4.6.8批量数据插入27
4.6.9如果DBMS能够产生执行计划，验证一下是否是最优的SQL28
1.前言
1.1目的
开发过程中，经常会使用PL/SQLDeveloper工具进行数据转换和处理业务数据。
通过性能优化来提高程序执行效率是必须掌握的一份技能。
性能问题中绝大部分都是由于程序编写的不合理、不规范造成的。
本文档阐述了程序中常见的不优化的脚本编写，导致的性能问题。
期望通过阅读该文档，能够使大家有所收获。
1.2文档说明
本文档只涉及PLSQL编写的书写规范和优化问题，不包括ORACLE本身的性能优化（内存SGA、系统参数、表空间等）、操作系统的性能问题和硬件的性能问题。
对于PLSQL程序优化方面的内容有很多，本文档列出在我们实际工作中一些常见的情况。
文档中难免有不正确的地方，请给予指正、完善。
举例说明中的语句采用的社保核心平台的数据字典，没有标明表名和字段名的含义。
以下文档中用红色加粗表明之处属于大部分情况下正确，但是存在特例情况，因此在日常使用情况下已给与关注。
1.3词汇表
词汇名称
词汇含义
备注
重要度
Sql语句书写规范的执行程度。
1、必须2、可选
1.4参考资料
编号
资料名称
作者
日期
出版单位
1
《Oracle建议书写规范》
2
《ORACLESQL性能优化系列》
3
2.程序性能分析方法
2.1ExpainPlan（E）
在PL/SQLDeveloper工具中有一个ExpainPlan分析的功能，这个功能可以帮助我们分析SQL语句的执行情况。
比如：
是否使用了索引、使用哪些索引、使用索引的效果及执行成本等。
1.执行explainplan分析功能
方法一：
在SQLWindow程序界面下，点击快捷键F5。
方法二：
在SQLWindow程序界面下，依次点击“TOOLS–>ExpainPlan”菜单。
2.分析内容说明
●Optimizergoal：
使用的优化器模式。
在oracle10g前默认的优化模式是CHOOSE，10g 及以上版本默认是ALL_ROWS，采用基于成本的优化器。
建议不调整，使用默认即可。
ORACLE的优化器：
a.RULE（基于规则）b.COST（基于成本）c.CHOOSE（选择性）
设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话（session）级对其进行覆盖.
为了使用基于成本的优化器（CBO,Cost-BasedOptimizer）,需要经常运行analyze或DBMS_STATS.gather_table_stats分析命令,以增加数据库中的对象统计信息（objectstatistics）的准确性.
如果数据库的优化器模式设置为选择性（CHOOSE）,那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被分析过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器。
●Description：
解释计划，从解释计划上能看到哪个条件语句使用了索引，哪个没有使用；哪个表使用了索引，使用了哪个索引，哪些表是全表扫描的（TABLEACCESSFULL）。
全表扫描的（TABLEACCESSFULL）肯定是速度最慢的，如果是大数据量的表，那么这个语句是绝对影响性能的。
。
索引常见的使用类型：
1.INDEXUNIQUESCAN：
唯一索引扫描，速度最快。
2.INDEXRANGESCAN：
范围索引扫描，使用这个索引时，就需要看COST、Cardinality、Bytes的大小了。
另外使用了索引也不一定性能就高，因为索引使用也有效率的情况。
如果特别大，有时候还可能速度低于全表扫描的速度。
●Cost：
基于成本的优化器权重值。
值越小程序执行效率越高。
如采用基于规则的优化方式，该列值为空。
●Cardinality：
所访问行数的估计值
●Bytes：
所访问字节的估计值
●通过设置，我们还能看到更多的信息，例如：
CPU使用、时间等
3.PLSQL优化的核心思想
3.1导致性能问题的内在原因
导致系统性能出现问题从系统底层分析也就是如下几个原因：
●CPU占用率过高，资源争用导致等待
●内存使用率过高，内存不足需要磁盘虚拟内存
●IO占用率过高，磁盘访问需要等待
3.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优化中重要思想。
4.程序书写规范及优化原则
4.1书写方面
4.1.1SQL语句要统一成大写
●说明
SQL语句转换成大写，可缩短些SQL的解析时间。
通过统一成大写，可提高SQL的再利用率，缩短SQL解析时间。
●重要度
必须
●举例
高效的写法：
SELECT*FROMA_TABLE
低效的写法：
select*froma_table
●参考
10g条件下测试结果基本没有差别，ORACLE建议按此方式书写.。
4.1.2涉及到多表检索时，明确地为每个字段指定表名
●说明
通过在A_TABLE、B_TABLE中指定别名｢A｣、｢B｣,就可不再需要调查A_ITEM、B_ITEM、A_KEY、B_KEY是哪儿个表中的项目,从而缩短SQL解析时间。
●重要度
可选
●举例
高效的写法：
SELECTA.A_ITEM,B.B_ITEM
FROMA_TABLEA,B_TABLEB
WHEREA.A_KEY=B.B_KEY;
低效的写法：
SELECTA_ITEM
FROMA_TABLE,B_TABLE
WHEREA_KEY=B_KEY;
●参考
为方便编码可以为表名指定别名。
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
4.1.3SELECT子句中避免使用’*’
●说明
ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。
●重要度
可选
●举例
高效的写法：
SELECTAAC001,AAC002,AAC003FROMAC01;
低效的写法：
SELECT*FROMAC01;
●参考
适用于Oracle数据库。
4.2条件语句
4.2.1在进行多个表连接时，FROM中的表的顺序要按照记录数由多到少的顺序来排列
●说明
只在基于规则的优化器rule中有效，目前我们oracle选择的优化器基本都不选择rule，因此该问题基本不会出现，但为了安全和规范起见，建议编程习惯采用该规则。
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表（基础表drivingtable）将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表（FROM子句中最后的那个表）并对记录进行派序,然后扫描第二个表（FROM子句中最后第二个表）,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
●重要度
必须
●举例
表ac01有16,384条记录
表ab01有1条记录
高效的写法：
SELECTCOUNT（*）FROMAC01,AB01;
执行时间0.96秒
低效的写法：
SELECTCOUNT（*）FROMAB01,AC01;
执行时间26.09秒
●参考
只在基于规则的优化器rule中有效。
ORACLE建议按此方式书写。
减少多表关联:
表关联的越多，查询速度就越慢，尽量减少多个表的关联，建议表关联不要超过3个（子查询也属于表关联）。
数据转换上会存在大数据量表的关联，关联多了会影响索引的效率，可以采用建立临时表的办法，有时更能提高速度.
4.2.2可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
●说明
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
●重要度
必须
●举例
高效的写法：
SELECT*FROMAC01WHEREAAC004='1'ANDAAC003='周东芝';
低效的写法：
SELECT*FROMAC01WHEREAAC003='周东芝'ANDAAC004='1';
●参考
4.2.3WHERE子句中的连接顺序
●说明
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前。
●重要度
可选
●举例
高效的写法：
SELECTab01.aab001,ab02.aab051
FROMab01,ab02
WHEREab01.aab001=ab02.aab001
ANDab02.aae140=’31’;
低效的写法：
SELECTab01.aab001,ab02.aab051
FROMab01,ab02
WHEREab02.aae140=’31’
ANDab01.aab001=ab02.aab001;
●参考
4.2.4不要通过LIKE运算来执行中间一致或后方一致的检索
●说明
中间或后方一致性检索时，索引是无效的。
●重要度
可选
●举例
高效的写法：
SELECT*FROMAC01WHEREAAC003LIKE'梁海%';
低效的写法：
SELECT*FROMAC01WHEREAAC003LIKE'%梁海';
SELECT*FROMAC01WHEREAAC003LIKE'%梁海%';
●参考
重新分析一下看是否真的需要中间一致或后方一致检索，如果真的需要的话，看是否能需要重新规划一下。
%在常量前面索引就不会使用。
4.2.5用EXISTS替代IN
●说明
实际情况看，使用exists替换in效果不是很明显，基本一样。
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS（或NOTEXISTS）通常将提高查询的效率。
●重要度
必选
●举例
高效的写法：
SELECT*
FROMac01
Whereexists（select1fromac02whereaac001=ac01.aac001andaab001=str_aab001andaae140=’31’）;
in的常量列表是优化的（例如：
aab019in（‘20’,’30’）），不用exists替换；in列表相当于or
低效的写法：
SELECT*
FROMac01
Whereaac001in（selectaac001fromac02whereaab001=str_aab001andaae140=’31’）;
或
SELECT*
FROMac01
Whereaac001in（selectdistinctaac001fromac02whereaab001=str_aab001andaae140=’31’）;
注意使用distinct也会影响速度
●参考
4.2.6用NOTEXISTS替代NOTIN
●说明
Oracle在10g之前版本notin都是最低效的语句，虽然在10g上notin做到了一些改进，但仍然还是存在一些问题，因此我们一定要使用notexists来替代notin的写法。
在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的（因为它对子查询中的表执行了一个全表遍历）.为了避免使用NOTIN,我们可以把它改写成NOTEXISTS。
●重要度
必选
●举例
高效的写法：
SELECT*
FROMac01
WHEREnotexists（SELECT1fromab01whereaab001=ac01.aab001andaab020=’100’）;
低效的写法：
SELECT*
FROMac01
WHEREaab001NOTIN（SELECTaab001fromab01whereaab020=’100’）;
●参考
4.2.7用表连接替换EXISTS
●说明
在子查询的表和主表查询是多对一的情况，一般采用表连接的方式比EXISTS更有效率。
●重要度
可选
●举例
高效的写法：
SELECTac01.*
FROMac02,ac01
Whereac02.aac001=ac01.aac001
andac02.aab001=ac01.aab001
andac02.aae140='31'
andaae041='200801';
低效的写法：
SELECTac01.*
FROMac01
Whereexists（select1fromac02
whereaac001=ac01.aac001
andaab001=ac01.aab001
andaae140='31'
andaae041='200801'）;
●参考
到底exists和表关联哪种效率高，其实是根据两个表之间的数据量差别大小是有关的，如果差别不大实际上速度基本差不多。
4.2.8用EXISTS替换DISTINCT
●说明
当提交一个包含一对多表信息（比如个人基本信息表和个人参保信息表）的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXISTS替换。
●重要度
可选
●举例
高效的写法：
selectac01.aac001
fromac01
whereexists（select1fromac02whereaac001=ac01.aac001
andaae140='31'）
andac01.aab001='100100';
低效的写法：
selectdistinctac01.aac001
fromac02,ac01
whereac02.aac001=ac01.aac001
andac02.aae140='31'
andac01.aab001='100100';
●参考
EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
因此如果不是特别研究和追求速度的话（例如：
数据转换），查询一个表的数据需要关联其他表的这种情况查询，建议采用EXISTS的方式。
4.2.9用Where子句替换HAVING子句
●说明
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
●重要度
可选
●举例
高效的写法：
SELECTaac008,count
（1）
FROMac01
Whereaac008in（‘1’,’2’）
GROUPBYaac008;
低效的写法：
SELECTaac008,count
（1）
FROMac01
GROUPBYaac008
HAVINGaac008in（‘1’,’2’）;
●参考
HAVING中的条件一般用于对一些集合函数的比较,如COUNT（）等等.除此而外,一般的条件应该写在WHERE子句中。
4.2.10对于经常使用的SQL语句（循环处理中使用的SQL等），可以通过预编译、绑定变量来提高性能
●说明
由于SQL可以被再利用，所以可缩短SQL解析时间。
●重要度
必须
●举例
高效的写法：
:
VAR='0';
SELECTCOUNT（*）FROMA_TABLEAWHEREA.KEY=:
VAR;
:
VAR='1';
SELECTCOUNT（*）FROMA_TABLEAWHEREA.KEY=:
VAR;
低效的写法：
SELECTCOUNT（*）FROMA_TABLEAWHEREA.KEY='0';
SELECTCOUNT（*）FROMA_TABLEAWHEREA.KEY='1';
●参考
4.3索引的使用
在实际的应用系统中索引问题导致性能问题可能能占到80%，在程序优化上索引问题是需要我们特别关注的。
本节主要描述什么情况索引会不生效
4.3.1对于索引列不要执行NULL值的检索
●说明
NULL检索是指[查找没有的东西]，所以如果不全部都调查的话无法判断出是有还是没有。
索引只做成[有]的数据。
调整成不执行NULL检索的SQL，或者修改表的定义。
对于索引列使用isnull或isnotnull不会使用上索引．因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引
●重要度
必须
●举例
●参考
在实际开发中，对于这类的问题很难避免，如果不是特别影响速度或者要求速度的，可以忽略。
4.3.2对于索引列，不要使用"NOT"、"!
="、"<>"比较运算
●说明
可缩短SQL解析时间。
避免在索引列上使用NOT,NOT不会使查询条件使用索引。
对于!
=这样的判断也是不能使用索引的，索引只能告诉你什
升级会员 