2022年PLSQL程序优化和性能测试技巧.docx

PLSQL 程序优化和性能分析方法沈阳东软软件股份有限公司文件编号：TD文档编号版本号1 0分册名称第 册/共 册总页数编制张悦正文审批附录生效日期沈阳东软软件股份有限公司版本号更换条款及内容更换人审批人更换日期1. 前言 31.1 目的 31.2 文档说明 31.3 词汇表 41.4 参考资料 42. PLSQL程序优化原就 42.1 导致性能问题的内在缘由42.2 PLSQL 优化的核心思想42.3 ORACLE优化器 52.4 PLSQL 优化 62.4.1 挑选最有效率的表名次序62.4.2 WHERE子句中的连接次序62.4.3 SELECT子句中防止使用 *6 2.4.4 用 EXISTS 替代 IN72.4.5 用 NOT EXISTS替代 NOT IN72.4.6 用表连接替换 EXISTS82.4.7 用 EXISTS 替换 DISTINCT82.4.8 削减对表的查询 92.4.9 防止循环（游标）里面嵌查询92.4.10 尽量用 union all 替换 union112.4.11 使用 DECODE函数来削减处理时间112.4.12 group by优化 112.4.13 尽量防止用 order by122.4.14 用 Where 子句替换 HAVING子句 122.4.15 使用表的别名 Alias122.4.16 删除重复记录122.4.17 COMMIT使用 132.4.18 削减多表关联132.4.19 批量数据插入132.5 索引使用优化 142.5.1 防止在索引列上使用函数或运算142.5.2 防止转变索引列的类型.152.5.3 防止在索引列上使用NOT152.5.4 用>=替代 >152.5.5 防止在索引列上使用IS NULL和 IS NOT NULL162.5.6 带通配符（ %）的 like 语句 162.5.7 总是使用索引的第一个列 162.5.8 多个公平的索引 172.5.9 不明确的索引等级 172.5.10 自动挑选索引 172.5.11 使用提示 Hints172.5.12 表上存在过旧的分析 182.5.13 表上存在并行 182.5.14 关于索引建立 183. PLSQL程序性能问题测试方法 193.1 性能问题分析 193.2 E XPAIN PLAN 分析索引使用 203.3 TOPSQL分析 213.4 针对性语句搜寻 253.5 后台储备过程跟踪 263.6 性能监控 错误！未定义书签；4. 性能测试工具设计思想错误！未定义书签；1. 前言1.1 目的性能测试是测试中比较重要的工作，性能测试应分为压力的测试和性能的测试，其中性能问题中绝大部分都是由于程序编写的不合理、不规范造成的；本文档说明白程序中常见的不优化的脚本编写，导致的性能问题，并且在也描述了怎样去跟踪和解决程序上的性能问题的方法；在最终一章里面描述了做一个白盒测试工具测试性能问题的设计思想；1.2 文档说明本文档只说明PLSQL 编写的优化问题，不包括ORACLE 本身的性能优化（内存SGA、系统参数、表空间等）、操作系统的性能问题和硬件的性能问题；对于PLSQL 程序优化方面的内容有很多，本文档列出在我们实际工作中一些常见的情形；本文档难免有不正确的地方，也需要大家赐予指正；本文档举例说明的问题语句不是实际程序中真正存在的，只是让大家能看起来更简单懂得，但这些语句也不代表在我们程序中其他部分语句不存在这些问题；举例说明中的语句采纳的是社保核心平台的数据字典，在举例描述中没有标明表名和字段名的含义，仍需单独参考；词汇名称词汇含义备注1.4 参考资料1.3 词汇表编号资料名称作者日期出版单位1ORACLE SQ性L 能优化系列232. PLSQL 程序优化原就2.1 导致性能问题的内在缘由导致系统性能显现问题从系统底层分析也就是如下几个缘由：CPU 占用率过高，资源争用导致等待内存使用率过高，内存不足需要磁盘虚拟内存IO 占用率过高，磁盘拜访需要等待2.2 PLSQL 优化的核心思想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.3 ORACLE 优化器ORACLE的优化器：a.RULE 基 于 规 就 b.COST 基 于 成 本 c.CHOOSE 选 择 性 设置缺省的优化器 ,可以通过对 init.ora 文件中 OPTIMIZER_MODE参数的各种声明 ,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS. 你当然也在SQL 句级或是会话session级对其进行覆盖.为了使用基于成本的优化器 CBO, Cost-Based Optimizer , 你必需常常运行 analyze 命令,以 增 加 数 据 库 中 的 对 象 统 计 信 息 objectstatistics 的 准 确 性 .假如数据库的优化器模式设置为挑选性CHOOSE,那么实际的优化器模式将和是否运 行过 analyze命令有关 . 假如 table 已经被 analyze 过, 优化器模式将自动成为 CBO , 反之, 数据库将采用RULE形式的优化器. 在缺省情形下 ,ORACLE 采纳 CHOOSE 优化器 , 为了防止那些不必要的全表扫描full table scan , 你必需尽量防止使用 CHOOSE 优化器,而直接采纳基于规章或者基于成本的优化器.选择ac01作select count* from ab01,ac012.4.2 WHERE 子句中的连接ORACLE采纳自下而上的顺条件之前例如 :为执行时间次序序解读基础表26.09 秒WHERE 子句 ,依据这个原理不好的方,表之间的连接必需写在其他法WHERE2.4.3 SELECT 子句中防止使用 *的是 ,这是一个特别低效的方法. 实际上 ,ORACLE 在解读的过程中 , 会将 '*' 依次转换成全部的列名作是通过查询数据字典完成的, 这意味着将耗费更多的时间；方法 .不幸, 这个工在 oracle10g 前默认的优化模式是 CHOOSE， 10g 默认是 ALL_ROWS ，我不建议大家去改动 ORACLE 的默认优化模式；2.4 PLSQL 优化主要说明白在 SQL 编写上和 PLSQL 程序编写上可以优化的地方；2.4.1 挑选最有效率的表名次序只在基于规章的优化器rule 中有效，目前我们oracle 挑选的优化器基本都不挑选rule ，因此该问题基本不会显现，但为了安全和规范起见，建议编程习惯采纳该规章；ORACLE的解读器依据从右到左的次序处理FROM 子句中的表名 ,因此 FROM 子句中写在最终的表基础表 driving table 将被最先处理 . 在 FROM 子句中包含多个表的情形下,你必需挑选记录条数最少的表 作为基础表 .当 ORACLE处理多个表时 , 会运用排序及合并的方式连接它们.第一,扫描第一个表 FROM子句中最终的那个表 并对记录进行派序,然后扫描其次个表 FROM子句中最终其次个表 ,最终将全部从其次个表中检索出的记录与第一个表中合适记录进行合并.例如:表表ac01ab01有有116,384条条记记录录选择ab01作为基础表好的方法selectcount*fromac01,ab01执行时间0.96秒SELECT低ab01.aab001,ab02.aab051FROM效ab01,ab02WHEREab02.aae140=31ANDab01.aab001=ab02.aab001； SELECTWHERE ab01.aab001=ab02.aab001高ab01.aab001,ab02.aab051FROM AND ab02.aae140= 31；效ab01,ab02当你想在 SELECT 子句中列出全部的COLUMN时,使用动态 SQL 列引用 *'是一个便利的2.4.4 用 EXISTS 替代 IN实际情形看，使用exists 替换 in 成效不是很明显，基本一样；在很多基于基础表的查询中,为了满意一个条件, 往往需要对另一个表进行联接. 在这种情形下, 使用EXISTS或NOTEXISTS通常将提高查询的效率.低效: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=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 都是最低效的语句，虽然在10g 上 not in 做到了一些改进，但仍旧仍是存在一些问题，因此我们肯定要使用not exists 来替代 not in 的写法；在子查询中 ,NOT IN 子句将执行一个内部的排序和合并. 无论在哪种情形下 ,NOT IN 都是最低效的 由于它对子查询中的表执行了一个全表遍历. 为了防止使用NOTIN,我们可以把它改写成NOT EXISTS.例如:SELECT*FROMac01WHEREaab001NOTINSELECTaab001fromab01whereaab020=100；为了提高效率.改写为: SELECT*FROMac01WHERE not exists SELECT 1 from ab01 where aab001=ac01.aab001 and aab020= 100 ；2.4.6 用表连接替换 EXISTS在子查询的表和主表查询是多对一的情形，一般采纳表连接的方式比EXISTS更有效率；例如：低效：SELECTFROMac01.*ac01Wherewhereexistsselect1fromac02aac001=ac01.aac001andandaab001=ac01.aab001aae140='31'and aae041='202101'；高效：SELECTac01.* FROMac02,ac01Whereac02.aac001=ac01.aac001andac02.aab001=ac01.aab001andac02.aae140='31'and aae041='202101'；究竟 exists 和表关联哪种效率高，其实是依据两个表之间的数据量差别大小是有关的，假如差别不大实际上速度基本差不多；2.4.7 用 EXISTS 替换 DISTINCT当提交一个包含一对多表信息比如个人基本信息表和个人参保信息表的查询时 ,防止在 SELECT子句中使用DISTINCT.一般可以考虑用EXISTS替换例如:低效:selectdistinctac01.aac001fromac02,ac01whereac02.aac001=ac01.aac001and andac01.aab001='100100'ac02.aae140='31'；高效:selectac01.aac001fromac01whereexistsselect1fromac02whereaac001=ac01.aac001andaae140='31'and ac01.aab001='100100' ；EXISTS 使查询更为快速 ,由于 RDBMS 核心模块将在子查询的条件一旦满意后,马上返回结果；因此假如不是特殊讨论和追求速度的话（例如：数据转换），查询一个表的数据需要关联其他表的这种情形查询，建议采纳EXISTS 的方式；2.4.8 削减对表的查询该问题是我们编程中显现过的问题，请大家肯定留意，并且该类问题优化可以带来较大性能的提升；例如:低效 cursor cur_kc24_mz is Select akc260from kc24where akb020 =str_akb020 and aka130=11；cursor cur_kc24_zy is Select akc260 from kc24where akb020 =str_akb020 and 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 is Select akc260,aka130 from kc24where akb020 =str_akb020 and aka130 in 11,21 ；for rec_kc24 in cur_kc24 loopif rec_kc24.aka130= 11then门诊处理 . end if ；if rec_kc24.aka130= 21then住院处理 . end if ；end loop ；高效的做法使用同样的条件（或者说是索引）只拜访一次磁盘，低效的做法拜访了2 次磁盘，这样速度差别将近 2 倍；2.4.9 防止循环（游标）里面嵌查询游标里面不能嵌入查询或者再嵌游标 ，其实也不能有update delete 等语句，只能有insert 语句；但在实际的编程情形下是不行能完全防止的，但我们肯定要尽量防止；该类问题也是我们程序中显现过的问题，该类问题也可以大大提升程序效率，请大家肯定留意；例如：低效：Cursor cur_ac04 is Select aac001,akc010 From ac04Where aab001= prm_aab001 ；For rec_ac04 in cur_ac04 loop Select 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.aac008 From ac04,ac01Where ac04.aac001=ac01.aac001 and aab001= prm_aab001；For rec_ac04 in cur_ac04 loop if 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 ；优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来，这样相当于只拜访了1 次磁盘读到内存；假如放到游标中的话，假如游标有100 万数据量，那么程序需要100 万次磁盘，可以想象铺张了多少 IO 的拜访；假如在程序编写上没有方法防止游标中有查询语句的话（一般情形是可以防止的），那么也要保证游标中的查询使用的索引（即查询速度特别快），例如：游标100 万数据量，游标中的查询语句执行需要0.02 秒，从这个速度上来说是很快的，但总体上看100 万*0.02 秒=2 万秒=5 小时 33 分钟，假如写一个不够优化的语句需要1 秒，那么需要几天能执行完呢？2.4.10 尽量用 union all 替换 unionUnion 会去掉重复的记录，会有排序的动作，会铺张时间；因此在没有重复记录的情形下或可以答应有重复记录的话，要尽量采纳union all 来关联；2.4.11 使用 DECODE 函数来削减处理时间使 用DECODE函 数 可 以 避 免 重 复 扫 描 相 同 记 录 或 重 复 连 接 相 同 的 表 .例如 :（低效 select count1 from ac01 where aab001=100001and aac008=1；select count1 from ac01 where aab001=100001and aac008=2；（低效 Select count1,aac008From ac01Where aab001=100001 and aac008 in 1,2 group by aac008；（高效） select countdecodeaac008,1,1,null zz, countdecodeaac008,2,1,null txfrom ac01where aab001=100001；特殊说明：group by 和 order by 都会影响性能，编程时尽量防止没有必要的分组和排序，或者通过其他的有效的编程方法去替换，比如上面的处理方法；2.4.12 group by 优化Group by 需要查询后排序，速度慢影响性能，假如查询数据量大，并且分组复杂，这样的查询语句在性能上是有问题的；尽量防止使用分组或者采纳上面的一节的方法去代替；采纳 group by 的也肯定要进行优化；例如： 低效selectac04.aac001,ac01.aac002,ac01.aac003,sumaac040,ac01.aab001fromac04,ac01whereac04.aac001=ac01.aac001andac01.aab001='1000000370' group by ac04.aac001,ac01.aac002,ac01.aac003,ac01.aab001；高效：selectac04.aac001,ac01.aac002,ac01.aac003,gzze,ac01.aab001 fromselectaac001,sumaac040gzzefromac04groupbyaac001ac04,ac01 whereac04.aac001=ac01.aac001and aab001='1000000370'；2.4.13 尽量防止用 order byOrder by 需要查询后排序，速度慢影响性能，假如查询数据量大，排序的时间就很长；但我们也不能防止不使用，这样大家肯定留意一点的是假如使用order by 那么排序的列表必需符合索引，这样在速度上会得到很大的提升；2.4.14 用 Where 子句替换 HAVING 子句防止使用 HA VING 子句 , HA VING只会在检索出全部记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作 .如果能通过WHERE子句限制 记录的数目,那就能减少这方 面 的 开销 .例如:低效:SELECTaac008,count1FROMac01GROUPBYaac008HA VINGaac008in1,2；高效SELECT aac008,count1FROM ac01Whereaac008in1,2GROUP BY aac008 ；HAVING 中的条件一般用于对一些集合函数的比较的条件应当写在 WHERE 子句中,如 COUNT等等 . 除此而外 ,一般2.4.15 使用表的别名 Alias当在 SQL 语句中连接多个表时 , 请使用表的别名并把别名前缀于每个Column 上.这样一来 ,就可以削减解读的时间并削减那些由Column 歧义引起的语法错误 .2.4.16 删除重复记录一般数据转换的程序常常会使用到该方法；最高效的删除重复记录方法因为使用了ROWID DELETEFROMac01aWHEREa.rowid>SELECTMINb.rowid FROMac01b WHERE a.aac002=b.aac002and a.aac003=b.aac003 ；2.4.17 COMMIT 使用数据转换的程序需要关注这一点；1. Commit 执行也是有时间的，不过时间特殊短，但提交频率特殊大，必定也会铺张时间；2. commit可 以 释 放 资 源 ， 在 大 量 数 据 更 新 时 ， 必 须 及 时 提 交 ；a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redologbuffer中的空间d. ORACLE 为治理上述 3 种资源中的内部花费例如：Cur_ac20 有 5000 万数据n_count :=0 ；For arec in 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 个（子查询也属于表关联）；数据转换上会存在大数据量表的关联，关联多了会影响索引的效率，可以采纳建立暂时表的方法，有时更能提高速度；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_bakaac001, .Valuesrec_ac20.aac001, .；If n_count :=100000 then Commit ；n_count :=0 ；End if ；End loop ；批绑定的方式通过游标查询将数据逐行写到数组里（实际上就是内存），然后通过批绑定的语句forall in insert into values ；将内存的数据一次写入到数据文件中；相比cursor 的方式削减了对io 的拜访次数，提高了速度，但留意内存别溢出了；2.5 索引使用优化在实际的应用系统中索引问题导致性能问题可能能占到80%，在程序优化上索引问题是需要我们特殊关注的；本节主要描述什么情形索引会不生效；2.5.1 防止在索引列上使用函数或运算这个问题是在我们实际编程中显现过的，请大家肯定留意；在索引列上使用函数或运算，查询条件都不会使用索引；例如：不使用索引Select * from ka02 where aka060= 10001000and to_charaae030,yyyymm = 202101；使用索引Select * from ka02 where aka060= 10001000and aae030=to_date202101,yyyymm ；不使用索引Select * from ka02 where aka060= 10001000and aae031+1=sysdate；使用索引Select * from ac04 where aac001= 10001000and aae031=sysdate -1；假如肯定要对使用函数的列启用索引, ORACLE新的功能 : 基于函数的索引Function-Based Index CREATEINDEXIDX_KA02_AKA066ONKA02UPPERAKA066； /* 建 立 基 于 函 数 的 索 引 */SELECT * FROM KA02 WHERE UPPERAKA066 =ASPL； /* 将使用索引 */不是极特殊情形，建议不要使用；2.5.2 防止转变索引列的类型 .索引列的条件假如类型不匹配，就不能使用索引；例如：不使用索引Select * from ac01 where aac001=10001000 ；使用索引Select * from ac01 where aac001= 10001000；2.5.3 防止在索引列上使用 NOT防止在索引列上使用NOT, NOT 不会使查询条件使用索引；对于.= 这样的判定也是不能使用索引的，索引只能告知你什么存在于表中, 而不能告知你什么不存在于表中例如:低效:这里,不使用索引 select *From ac02Wherenotaab019=10；高效:这里,使用了索引 select *From ac02Where aab019 in 20,30；2.5.4 用>=替代>虽然成效不是特殊明显，但建议采纳这种方式低效:SELECT*FROMab01WHEREaab019>10高效 :SELECT*FROMab01WHEREaab019 >= 20两者的区分在于 , 前者 DBMS 第肯定位到aab019=10 的记录并且向前扫描到第一个 aab019 大于 10 的记录 ,而后者 DBMS 将直接跳到第一个aab019 等于 10 的记录2.5.5 防止在索引列上使用IS NULL 和 IS NOT NULL对于索引列使用 is null 或 is not null不会使用上索引由于空值不存在于索引列中,所以 WHERE 子句中对索引列进行空值比较将使ORACLE停用该索引.举例:低效:索引失效select*fromab01whereaab019isnotnull；高效:索引有效select * from ab01 where aab019 in 10,20,30；在实际开发中，对于这类的问题很难防止，假如不是特殊影响速度或者要求速度的，可以忽视；2.5.6 带通配符（ %）的 like 语句%在常量前面索引就不会使用；例如：不使用索引Select * from ac01 where aac002 like%210104 ； Select * from ac01 where aac002 like%210104% ；使用索引Select * from ac01 where aac002 like210104% ；2.5.7 总是使用索引的第一个列假如索引是建立在多个列上, 只有在它的第一个列被where 子句引用时 ,优化器才会挑选使用该索引；例如：Ac02 的复合索引： aac001、aae140、 aae041Select * from ac02 where aae140= 31and aae041=202101； - 不会使用索引 Select * from ac02