PLSQL程序优化和性能测试方法.doc
《PLSQL程序优化和性能测试方法.doc》由会员分享,可在线阅读,更多相关《PLSQL程序优化和性能测试方法.doc(32页珍藏版)》请在咨信网上搜索。
1、PLSQL程序优化和性能测试方法 作者: 日期:2 个人收集整理 勿做商业用途PLSQL程序优化和性能分析方法 沈阳东软软件股份有限公司文件编号:TD文档编号版本号10分册名称第 册/共 册总页数正文附录编制张悦审批生效日期沈阳东软软件股份有限公司版本号更改条款及内容更改人审批人更改日期1。 前言51。1 目的51。2 文档说明51.3 词汇表51。4 参考资料52. PLSQL程序优化原则62。1 导致性能问题的内在原因62。2 PLSQL优化的核心思想62.3 ORACLE优化器62.4 PLSQL优化72。4.1 选择最有效率的表名顺序72.4.2 WHERE子句中的连接顺序82。4。3
2、 SELECT子句中避免使用 * 82。4。4 用EXISTS替代IN82。4.5 用NOT EXISTS替代NOT IN92。4。6 用表连接替换EXISTS92.4。7 用EXISTS替换DISTINCT102.4.8 减少对表的查询102。4。9 避免循环(游标)里面嵌查询112。4.10 尽量用union all替换union132.4.11 使用DECODE函数来减少处理时间132.4。12 group by优化132.4。13 尽量避免用order by142。4.14 用Where子句替换HAVING子句142.4.15 使用表的别名(Alias)142。4。16 删除重复记录1
3、42。4。17 COMMIT使用152。4。18 减少多表关联152.4。19 批量数据插入152.5 索引使用优化162.5.1 避免在索引列上使用函数或运算162.5.2 避免改变索引列的类型。172.5.3 避免在索引列上使用NOT172。5.4 用=替代182。5.5 避免在索引列上使用IS NULL和IS NOT NULL182.5.6 带通配符(%)的like语句182。5。7 总是使用索引的第一个列192.5。8 多个平等的索引192。5。9 不明确的索引等级192。5。10 自动选择索引192。5。11 使用提示(Hints)192。5.12 表上存在过旧的分析202。5。13
4、 表上存在并行212.5。14 关于索引建立213。 PLSQL程序性能问题测试方法213。1 性能问题分析213.2 Expain Plan分析索引使用223。3 TOPSQL分析243.4 针对性语句搜索283.5 后台存储过程跟踪293。6 性能监控304. 性能测试工具设计思想311. 前言1.1 目的性能测试是测试中比较重要的工作,性能测试应分为压力的测试和性能的测试,其中性能问题中绝大部分都是由于程序编写的不合理、不规范造成的。本文档说明了程序中常见的不优化的脚本编写,导致的性能问题,并且在也描述了怎样去跟踪和解决程序上的性能问题的方法。在最后一章里面描述了做一个白盒测试工具测试性
5、能问题的设计思想。1.2 文档说明本文档只说明PLSQL编写的优化问题,不包括ORACLE本身的性能优化(内存SGA、系统参数、表空间等)、操作系统的性能问题和硬件的性能问题.对于PLSQL程序优化方面的内容有很多,本文档列出在我们实际工作中一些常见的情况。本文档难免有不正确的地方,也需要大家给予指正。本文档举例说明的问题语句不是实际程序中真正存在的,只是让大家能看起来更容易理解,但这些语句也不代表在我们程序中其他部分语句不存在这些问题.举例说明中的语句采用的是社保核心平台的数据字典,在举例描述中没有标明表名和字段名的含义,还需单独参考。1.3 词汇表词汇名称词汇含义备注1.4 参考资料编号资
6、料名称作者日期出版单位1ORACLE SQL性能优化系列232. PLSQL程序优化原则2.1 导致性能问题的内在原因导致系统性能出现问题从系统底层分析也就是如下几个原因:l CPU占用率过高,资源争用导致等待l 内存使用率过高,内存不足需要磁盘虚拟内存l IO占用率过高,磁盘访问需要等待2.2 PLSQL优化的核心思想PLSQL优化实际上就是避免出现“导致性能问题的内在原因”,实际上编写程序,以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。l PLSQL程序占用CPU的情况n 系统解析SQL语句执行,会消耗CPU的使用n 运算(计算)会消耗CPU的使用l PLSQL程序占用内存的情况n
7、 读写数据都需要访问内存n 内存不足时,也会使用磁盘l PLSQL程序增大IO的情况n 读写数据都需要访问磁盘IOn 读取的数据越多,IO就越大大家都知道CPU现在都很高,计算速度非常快;访问内存的速度也很快;但磁盘的访问相对前两个相比速度就差的非常大了,因此PLSQL性能优化的重点也就是减少IO的瓶颈,换句话说就是尽量减少IO的访问。性能的优先级CPU内存IO,影响性能的因素依次递增。根据上面的分析,PLSQL优化的核心思想为:1. 避免过多复杂的SQL脚本,减少系统的解析过程2. 避免过多的无用的计算,例如:死循环3. 避免浪费内存空间没有必要的SQL脚本,导致内存不足4. 内存中计算和访
8、问速度很快5. 尽可能的减少磁盘的访问的数据量,该原则是PLSQL优化中重要思想.6. 尽可能的减少磁盘的访问的次数,该原则是PLSQL优化中重要思想。下面的章节具体介绍常见影响性能的SQL语句情况.2.3 ORACLE优化器ORACLE的优化器:a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化
9、器(CBO, CostBased Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器。在
10、oracle10g前默认的优化模式是CHOOSE,10g默认是ALL_ROWS,我不建议大家去改动ORACLE的默认优化模式.2.4 PLSQL优化主要说明了在SQL编写上和PLSQL程序编写上可以优化的地方。2.4.1 选择最有效率的表名顺序只在基于规则的优化器rule中有效,目前我们oracle选择的优化器基本都不选择rule,因此该问题基本不会出现,但为了安全和规范起见,建议编程习惯采用该规则。ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。 在FROM子句中包含多个表的情况下,你必须选择记
11、录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 例如: 表 ac01有 16,384 条记录 表 ab01 有1 条记录 选择ab01作为基础表 (好的方法) select count(*) from ac01,ab01 执行时间0。96秒 选择ac01作为基础表 (不好的方法) select count(*) from ab01,ac01 执行时间26。09秒 2.4.2 W
12、HERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前例如:(低效) SELECT ab01.aab001,ab02.aab051 FROM ab01,ab02 WHERE ab02。aae140=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
13、子句中列出所有的COLUMN时,使用动态SQL列引用 * 是一个方便的方法。不幸的是,这是一个非常低效的方法。 实际上,ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。2.4.4 用EXISTS替代IN 实际情况看,使用exists替换in效果不是很明显,基本一样。在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 低效: SELECT FROM ac01Where aac001 in (select aac001 f
14、rom ac02 where aab001=str_aab001 and aae140=31);或SELECT * FROM ac01Where aac001 in (select distinct aac001 from ac02 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的常量列表是优化的
15、(例如:aab019 in (20,30),不用exists替换;in列表相当于or2.4.5 用NOT EXISTS替代NOT INOracle在10g之前版本not in都是最低效的语句,虽然在10g上not in做到了一些改进,但仍然还是存在一些问题,因此我们一定要使用not exists来替代not in的写法。在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成NOT EXISTS。 例如: SELECT FROM ac01 WHERE aab
16、001 NOT IN (SELECT aab001 from ab01 where aab020=100);为了提高效率。改写为: SELECT * FROM ac01 WHERE not 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 ac02 where aac001=ac
17、01.aac001 and aab001=ac01。aab001 and aae140=31 and aae041=200801);高效:SELECT ac01。 FROM ac02,ac01Where ac02。aac001=ac01。aac001 and ac02。aab001=ac01.aab001 and ac02。aae140=31 and aae041=200801;到底exists和表关联哪种效率高,其实是根据两个表之间的数据量差别大小是有关的,如果差别不大实际上速度基本差不多。2.4.7 用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如个人基本信息表和个人参保
18、信息表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXISTS替换 例如: 低效: select distinct ac01。aac001from ac02,ac01where ac02。aac001 = ac01。aac001and 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;EXIS
19、TS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.因此如果不是特别研究和追求速度的话(例如:数据转换),查询一个表的数据需要关联其他表的这种情况查询,建议采用EXISTS的方式。2.4.8 减少对表的查询该问题是我们编程中出现过的问题,请大家一定注意,并且该类问题优化可以带来较大性能的提升.例如: 低效 cursor cur_kc24_mz isSelect akc260from kc24where akb020 =str_akb020and aka130=11; cursor cur_kc24_zy isSelect akc260from kc24wher
20、e 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;高效 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_
21、kc24。aka130=21 then 住院处理.end if; end loop;高效的做法使用同样的条件(或者说是索引)只访问一次磁盘,低效的做法访问了2次磁盘,这样速度差别将近2倍.2.4.9 避免循环(游标)里面嵌查询游标里面不能嵌入查询(或者再嵌游标),其实也不能有update delete等语句,只能有insert语句。但在实际的编程情况下是不可能完全避免的,但我们一定要尽量避免。该类问题也是我们程序中出现过的问题,该类问题也可以大大提升程序效率,请大家一定注意。例如:低效:Cursor cur_ac04 is Select aac001,akc010 From ac04 Wher
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PLSQL 程序 优化 性能 测试 方法
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【精****】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【精****】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。