PLSQL程序性能分析及优化.doc

PLSQL 程序 性能分析及优化 巨龙软件工程公司 总工办 文件编号 TD 文档编号版本号 0 1 文档名称第 册 共 册 总页数28正文24附录 编制赵欣审批生效日期 巨龙软件工程公司 PLSQL 程序性能分析及优化 第 2 页 共 28 页 版本号更改条款及内容更改人审批人更改日期 0 1 PLSQL 程序性能分析及优化 第 3 页 共 28 页 1 前言 5 1 1 目的 5 1 2 文档说明 5 1 3 词汇表 5 1 4 参考资料 5 2 程序性能分析方法 6 2 1 EXPAIN PLAN 6 3 PLSQL 优化的核心思想 8 3 1 导致性能问题的内在原因 8 3 2 PLSQL 优化的核心思想 8 4 程序书写规范及优化原则 9 4 1 书写方面 9 4 1 1 SQL 语句要统一成大写 9 4 1 2 涉及到多表检索时 明确地为每个字段指定表名 9 4 1 3 SELECT 子句中避免使用 10 4 2 条件语句 10 4 2 1 在进行多个表连接时 FROM 中的表的顺序要按照记录数由多到少的顺序来排列 10 4 2 2 可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 11 4 2 3 WHERE 子句中的连接顺序 12 4 2 4 不要通过 LIKE 运算来执行中间一致或后方一致的检索 12 4 2 5 用 EXISTS 替代 IN 12 4 2 6 用 NOT EXISTS 替代 NOT IN 13 4 2 7 用表连接替换 EXISTS 14 4 2 8 用 EXISTS 替换 DISTINCT 14 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 PLSQL 程序性能分析及优化 第 4 页 共 28 页 4 3 2 当有多个索引可供选择时 使用的是 DB 设计者所希望的索引 20 4 4 存储函数 21 4 4 1 对于经常使用的 SQL 语句 循环处理中使用的 SQL 等 可以通过预编译 绑定变量来提高 性能 21 4 5 分组和排序的使用 21 4 5 1 去掉没有意义的 GROUP BY ORDER BY 子语 21 4 5 2 group by 优化 22 4 5 3 尽量避免用 order by 22 4 6 优化 22 4 6 1 减少对表的查询 22 4 6 2 避免循环 游标 里面嵌查询 24 4 6 3 尽量用 union all 替换 union 25 4 6 4 使用 DECODE 函数来减少处理时间 25 4 6 5 避免全表扫描的查询方式 26 4 6 6 删除重复记录 26 4 6 7 COMMIT 使用 26 4 6 8 批量数据插入 27 4 6 9 如果 DBMS 能够产生执行计划 验证一下是否是最优的 SQL 28 PLSQL 程序性能分析及优化 第 5 页 共 28 页 1 前言 1 1 目的 开发过程中 经常会使用PL SQL Developer 工具进行数据转换和处理业务数据 通过性能 优化来提高程序执行效率是必须掌握的一份技能 性能问题中绝大部分都是由于程序编写的 不合理 不规范造成的 本文档阐述了程序中常见的不优化的脚本编写 导致的性能问题 期望通过阅读该文档 能够使大家有所收获 1 2 文档说明 本文档只涉及 PLSQL 编写的书写规范和优化问题 不包括 ORACLE 本身的性能优化 内存 SGA 系统参数 表空间等 操作系统的性能问题和硬件的性能问题 对于 PLSQL 程序优化方面的内容有很多 本文档列出在我们实际工作中一些常见的情况 文档中难免有 不正确的地方 请给予指正 完善 举例说明中的语句采用的社保核心平台的数据字典 没有标明表名和字段名的含义 以 下文档中用红色加粗表明之处属于大部分情况下正确 但是存在特例情况 因此在日常使用 情况下已给与关注 1 3 词汇表 词汇名称词汇含义备注 重要度 Sql 语句书写规范的执行程度 1 必须 2 可选 1 4 参考资料 编号资料名称作者日期出版单位 1 Oracle 建议书写规范 2 ORACLE SQL 性能优化系列 PLSQL 程序性能分析及优化 第 6 页 共 28 页 3 2 程序性能分析方法 2 1 Expain Plan E 在 PL SQL Developer 工具中有一个 Expain Plan 分析的功能 这个功能可以帮助我们分析 SQL 语句的执 行情况 比如 是否使用了索引 使用哪些索引 使用索引的效果及执行成本等 1 执行 explain plan 分析功能 方法一 在 SQL Window 程序界面下 点击快捷键 F5 方法二 在 SQL Window 程序界面下 依次点击 TOOLS Expain Plan 菜单 2 分析内容说明 PLSQL 程序性能分析及优化 第 7 页 共 28 页 Optimizer goal 使用的优化器模式 在 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 Based Optimizer 需要经常运行 analyze 或 DBMS STATS gather table stats 分析命令 以增加数据库中的对象统计信息 object statistics 的准确性 如果数据库的优化器模式设置为选择性 CHOOSE 那么实际的优化器模式将和是否运行过 analyze 命 令有关 如果 table 已经被分析过 优化器模式将自动成为 CBO 反之 数据库将采用 RULE 形式的优 化器 Description 解释计划 从解释计划上能看到哪个条件语句使用了索引 哪个没有使用 哪个表使用 了索引 使用了哪个索引 哪些表是全表扫描的 TABLE ACCESS FULL 全表扫描的 TABLE ACCESS FULL 肯定是速度最慢的 如果是大数据量的表 那么这个语句是绝对 影响性能的 索引常见的使用类型 1 INDEX UNIQUE SCAN 唯一索引扫描 速度最快 2 INDEX RANGE SCAN 范围索引扫描 使用这个索引时 就需要看 COST Cardinality Bytes 的 大小了 另外使用了索引也不一定性能就高 因为索引使用也有效率的情况 如果特别大 有时候还 可能速度低于全表扫描的速度 Cost 基于成本的优化器权重值 值越小程序执行效率越高值越小程序执行效率越高 如采用基于规则的优化方式 该列值为 PLSQL 程序性能分析及优化 第 8 页 共 28 页 空 Cardinality 所访问行数的估计值 Bytes 所访问字节的估计值 通过设置 我们还能看到更多的信息 例如 CPU 使用 时间等 3 PLSQL 优化的核心思想 3 1 导致性能问题的内在原因 导致系统性能出现问题从系统底层分析也就是如下几个原因 CPU 占用率过高 资源争用导致等待 内存使用率过高 内存不足需要磁盘虚拟内存 IO 占用率过高 磁盘访问需要等待 3 2 PLSQL 优化的核心思想 PLSQL 优化实际上就是避免出现 导致性能问题的内在原因 实际上编写程序 以及 PLSQL 程序性能分析及优化 第 9 页 共 28 页 性能问题跟踪应该本着这个核心思想去考虑和解决问题 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 1 SQL 语句要统一成大写 说明 SQL 语句转换成大写 可缩短些 SQL 的解析时间 通过统一成大写 可提高 SQL 的再利用率 缩短 SQL 解析时间 重要度 必须 举例 高效的写法 SELECT FROM A TABLE 低效的写法 select from a table 参考 10g 条件下测试结果基本没有差别 ORACLE 建议按此方式书写 4 1 2 涉及到多表检索时 明确地为每个字段指定表名 说明 通过在 A TABLE B TABLE 中指定别名 A B 就可不再需要调查 PLSQL 程序性能分析及优化 第 10 页 共 28 页 A ITEM B ITEM A KEY B KEY 是哪儿个表中的项目 从而缩短 SQL 解析时间 重要度 可选 举例 高效的写法 SELECT A A ITEM B B ITEM FROM A TABLE A B TABLE B WHERE A A KEY B B KEY 低效的写法 SELECT A ITEM FROM A TABLE B TABLE WHERE A KEY B KEY 参考 为方便编码可以为表名指定别名 当在 SQL 语句中连接多个表时 请使用表的别名并把别名前缀于每个 Column 上 这样一来 就可 以减少解析的时间并减少那些由 Column 歧义引起的语法错误 4 1 3 SELECT 子句中避免使用 说明 ORACLE 在解析的过程中 会将 依次转换成所有的列名 这个工作是通过查询数据字典完 成的 这意味着将耗费更多的时间 重要度 可选 举例 高效的写法 SELECT AAC001 AAC002 AAC003 FROM AC01 低效的写法 SELECT FROM AC01 参考 适用于 Oracle 数据库 4 2 条件语句 4 2 1 在进行多个表连接时 FROM 中的表的顺序要按照记录数由多到少的顺 序来排列 说明 只在基于规则的优化器 rule 中有效 目前我们 oracle 选择的优化器基本都不选择 rule 因此该问 题基本不会出现 但为了安全和规范起见 建议编程习惯采用该规则 PLSQL 程序性能分析及优化 第 11 页 共 28 页 ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名 因此 FROM 子句中写在最后的 表 基础表 driving table 将被最先处理 在 FROM 子句中包含多个表的情况下 你必须选择记录条 数最少的表作为基础表 当 ORACLE 处理多个表时 会运用排序及合并的方式连接它们 首先 扫描 第一个表 FROM 子句中最后的那个表 并对记录进行派序 然后扫描第二个表 FROM 子句中最后 第二个表 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并 重要度 必须 举例 表 ac01 有 16 384 条记录 表 ab01 有 1 条记录 高效的写法 SELECT COUNT FROM AC01 AB01 执行时间 0 96 秒 低效的写法 SELECT COUNT FROM AB01 AC01 执行时间 26 09 秒 参考 只在基于规则的优化器 rule 中有效 ORACLE 建议按此方式书写 减少多表关联减少多表关联 表关联的越多 查询速度就越慢 尽量减少多个表的关联 建议表关联不要超过 3 个 子查询也 属于表关联 数据转换上会存在大数据量表的关联 关联多了会影响索引的效率 可以采用建立临时表的办法 有时更能提高速度 4 2 2 可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 说明 ORACLE 采用自下而上的顺序解析 WHERE 子句 根据这个原理 表之间的连接必须写在其他 WHERE 条件之前 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 重要度 必须 举例 高效的写法 SELECT FROM AC01 WHERE AAC004 1 AND AAC003 周东芝 低效的写法 SELECT FROM AC01 WHERE AAC003 周东芝 AND AAC004 1 参考 PLSQL 程序性能分析及优化 第 12 页 共 28 页 4 2 3 WHERE 子句中的连接顺序 说明 ORACLE 采用自下而上的顺序解析 WHERE 子句 根据这个原理 表之间的连接必须写在其他 WHERE 条件之前 重要度 可选 举例 高效的写法 SELECT ab01 aab001 ab02 aab051 FROM ab01 ab02 WHERE ab01 aab001 ab02 aab001 AND ab02 aae140 31 低效的写法 SELECT ab01 aab001 ab02 aab051 FROM ab01 ab02 WHERE ab02 aae140 31 AND ab01 aab001 ab02 aab001 参考 4 2 4 不要通过 LIKE 运算来执行中间一致或后方一致的检索 说明 中间或后方一致性检索时 索引是无效的 重要度 可选 举例 高效的写法 SELECT FROM AC01 WHERE AAC003 LIKE 梁海 低效的写法 SELECT FROM AC01 WHERE AAC003 LIKE 梁海 SELECT FROM AC01 WHERE AAC003 LIKE 梁海 参考 重新分析一下看是否真的需要中间一致或后方一致检索 如果真的需要的话 看是否能需要重新 规划一下 在常量前面索引就不会使用 4 2 5 用 EXISTS 替代 IN 说明 实际情况看 使用 exists 替换 in 效果不是很明显 基本一样 在许多基于基础表的查询中 为了满足一个条件 往往需要对另一个表进行联接 在这种情况下 使 PLSQL 程序性能分析及优化 第 13 页 共 28 页 用 EXISTS 或 NOT EXISTS 通常将提高查询的效率 重要度 必选 举例 高效的写法 SELECT FROM ac01 Where exists select 1 from ac02 where aac001 ac01 aac001 and aab001 str aab001 and aae140 31 in 的常量列表是优化的 例如 aab019 in 20 30 不用 exists 替换 in 列表相当于 or 低效的写法 SELECT FROM ac01 Where aac001 in select aac001 from ac02 where aab001 str aab001 and aae140 31 或 SELECT FROM ac01 Where aac001 in select distinct aac001 from ac02 where aab001 str aab001 and aae140 31 注意使用 distinct 也会影响速度 参考 4 2 6 用 NOT EXISTS 替代 NOT IN 说明 Oracle 在 10g 之前版本 not in 都是最低效的语句 虽然在 10g 上 not in 做到了一些改进 但仍然 还是存在一些问题 因此我们一定要使用 not exists 来替代 not in 的写法 在子查询中 NOT IN 子句将执行一个内部的排序和合并 无论在哪种情况下 NOT IN 都是最低效 的 因为它对子查询中的表执行了一个全表遍历 为了避免使用 NOT IN 我们可以把它改写成 NOT EXISTS 重要度 必选 举例 高效的写法 SELECT FROM ac01 WHERE not exists SELECT 1 from ab01 where aab001 ac01 aab001 and aab020 100 低效的写法 SELECT FROM ac01 WHERE aab001 NOT IN SELECT aab001 from ab01 where aab020 100 参考 PLSQL 程序性能分析及优化 第 14 页 共 28 页 4 2 7 用表连接替换 EXISTS 说明 在子查询的表和主表查询是多对一的情况 一般采用表连接的方式比 EXISTS 更有效率 重要度 可选 举例 高效的写法 SELECT ac01 FROM ac02 ac01 Where ac02 aac001 ac01 aac001 and ac02 aab001 ac01 aab001 and ac02 aae140 31 and aae041 200801 低效的写法 SELECT ac01 FROM ac01 Where exists select 1 from ac02 where aac001 ac01 aac001 and aab001 ac01 aab001 and aae140 31 and aae041 200801 参考 到底 exists 和表关联哪种效率高 其实是根据两个表之间的数据量差别大小是有关的 如果差别 不大实际上速度基本差不多 4 2 8 用 EXISTS 替换 DISTINCT 说明 当提交一个包含一对多表信息 比如个人基本信息表和个人参保信息表 的查询时 避免在 SELECT 子句中使用 DISTINCT 一般可以考虑用 EXISTS 替换 重要度 可选 举例 高效的写法 select ac01 aac001 from ac01 where exists select 1 from ac02 where aac001 ac01 aac001 and aae140 31 and ac01 aab001 100100 低效的写法 select distinct ac01 aac001 from ac02 ac01 PLSQL 程序性能分析及优化 第 15 页 共 28 页 where ac02 aac001 ac01 aac001 and ac02 aae140 31 and ac01 aab001 100100 参考 EXISTS 使查询更为迅速 因为 RDBMS 核心模块将在子查询的条件一旦满足后 立刻返回结果 因此如果不是特别研究和追求速度的话 例如 数据转换 查询一个表的数据需要关联其他表 的这种情况查询 建议采用 EXISTS 的方式 4 2 9 用 Where 子句替换 HAVING 子句 说明 避免使用 HAVING 子句 HAVING 只会在检索出所有记录之后才对结果集进行过滤 这个处理需 要排序 总计等操作 如果能通过 WHERE 子句限制记录的数目 那就能减少这方面的开销 重要度 可选 举例 高效的写法 SELECT aac008 count 1 FROM ac01 Where aac008 in 1 2 GROUP BY aac008 低效的写法 SELECT aac008 count 1 FROM ac01 GROUP BY aac008 HAVING aac008 in 1 2 参考 HAVING 中的条件一般用于对一些集合函数的比较 如 COUNT 等等 除此而外 一般的条件应 该写在 WHERE 子句中 4 2 10 对于经常使用的 SQL 语句 循环处理中使用的 SQL 等 可以通过预 编译 绑定变量来提高性能 说明 由于 SQL 可以被再利用 所以可缩短 SQL 解析时间 重要度 必须 举例 高效的写法 VAR 0 SELECT COUNT FROM A TABLE A WHERE A KEY VAR VAR 1 SELECT COUNT FROM A TABLE A WHERE A KEY VAR PLSQL 程序性能分析及优化 第 16 页 共 28 页 低效的写法 SELECT COUNT FROM A TABLE A WHERE A KEY 0 SELECT COUNT FROM A TABLE A WHERE A KEY 1 参考 4 3 索引的使用 在实际的应用系统中索引问题导致性能问题可能能占到 80 在程序优化上索引问题是需要我们特别关 注的 本节主要描述什么情况索引会不生效 4 3 1 对于索引列不要执行 NULL 值的检索 说明 NULL 检索是指 查找没有的东西 所以如果不全部都调查的话无法判断出是有还是没有 索引只做成 有 的数据 调整成不执行 NULL 检索的 SQL 或者修改表的定义 对于索引列使用 is null 或 is not null 不会使用上索引 因为空值不存在于索引列中 所以 WHERE 子句中对索引列进行空值比较将使 ORACLE 停用该索引 重要度 必须 举例 参考 在实际开发中 对于这类的问题很难避免 如果不是特别影响速度或者要求速度的 可以忽略 4 3 2 对于索引列 不要使用 NOT 比较运算 说明 可缩短 SQL 解析时间 避免在索引列上使用 NOT NOT 不会使查询条件使用索引 对于 这样的判断也是不能使用索引 的 索引只能告诉你什么存在于表中 而不能告诉你什么不存在于表中 重要度 必须 举例 高效的写法 select From ac02 Where aab019 in 20 30 低效的写法 select From ac02 Where not aab019 10 参考 PLSQL 程序性能分析及优化 第 17 页 共 28 页 4 3 3 用 替代 说明 虽然效果不是特别明显 但建议采用这种方式 重要度 可选 举例 高效的写法 SELECT FROM ab01 WHERE aab019 20 低效的写法 SELECT FROM ab01 WHERE aab019 10 参考 两者的区别在于 前者 DBMS 首先定位到 aab019 10 的记录并且向前扫描到第一个 aab019 大于 10 的记录 而后者 DBMS 将直接跳到第一个 aab019 等于 10 的记录 4 3 4 对于索引列不要使用函数和计算式 说明 索引将不能发挥索引的作用 重要度 必须 举例 高效的写法 SELECT FROM A TABLE A WHERE A KEY TO DATE 20030101 YYYYMMDD 低效的写法 SELECT FROM A TABLE A WHERE TO CHAR A KEY YYYYMMDD 20030101 参考 如必须使用 可考虑建函数索引 4 3 5 对于多键值索引 要按照索引的定义顺序来使用 说明 如果索引是建立在多个列上 只有在它的第一个列 leading column 被 where 子句引用时 优化器才 会选择使用该索引 重要度 必须 举例 PLSQL 程序性能分析及优化 第 18 页 共 28 页 高效的写法 在按照 A KEY1 A KEY2 来定义索引的情况下 SELECT FROM A TABLE A WHERE A KEY1 KEY1 AND A KEY2 KEY2 低效的写法 在按照 A KEY1 A KEY2 AKEY3 来定义索引的情况下 SELECT FROM A TABLE A WHERE A KEY2 KEY2 AND A KEY3 KEY3 参考 总是使用索引的第一个列 如果索引是建立在多个列上 只有在它的第一个列被 where 子句引用时 优化器才会选择使用该索 引 例如 Ac02 的复合索引 aac001 aae140 aae041 Select from ac02 where aae140 31 and aae041 200801 不会使用索引 Select from ac02 where aac001 10001000 可以使用索引 如果不使用索引第一列基本上不会使用索引 使用索引要按照索引的顺序使用 另外使用复合索 引的列越多 查询的速度就越快 4 3 6 避免改变索引列的类型 说明 索引列的条件如果类型不匹配 则不能使用索引 重要度 必须 举例 高效的写法 SELECT FROM A TABLE A WHERE A NUMBER COL 123 低效的写法 SELECT FROM A TABLE A WHERE A NUMBER COL 123 参考 4 3 7 多个平等的索引 当 SQL 语句的执行路径可以使用分布在多个表上的多个索引时 ORACLE 会同时使用多个索引并 在运行时对它们的记录进行合并 检索出仅对全部索引有效的记录 在 ORACLE 选择执行路径时 唯一性索引的等级高于非唯一性索引 然而这个规则只有 当 WHERE 子句中索引列和常量比较才有效 如果索引列和其他表的索引类相比较 这种子句在优 化器中的等级是非常低的 如果不同表中两个相同等级的索引将被引用 FROM 子句中表的顺序将决定哪个会被率先使用 FROM 子句中最后的表的索引将有最高的优先级 PLSQL 程序性能分析及优化 第 19 页 共 28 页 如果同一表中有两个相同等级的索引被引用 oracle 会分析最有效的索引去引用 其他的索引不 会使用 如果这些相同等级的索引效果差不多 oracle 可能会自动合并进行使用 4 3 1 不明确的索引等级 E 当 ORACLE 无法判断索引的等级高低差别 优化器将只使用一个索引 它就是在 WHERE 子句中被 列在最前面的 4 3 2 自动选择索引 如果表中有两个以上 包括两个 索引 其中有一个唯一性索引 而其他是非唯一性 在这种情况下 ORACLE 将使用唯一性索引而完全忽略非唯一性索引 4 3 3 使用提示 Hints 对于表的访问 可以使用两种 Hints FULL 和 ROWID FULL hint 告诉 ORACLE 使用全表扫描的方式访问指定表 例如 SELECT FULL AC01 FROM AC01 WHERE AAC001 10001000 如果一个大表没有被设定为缓存 CACHED 表而你希望它的数据在查询结束是仍然停留 在 SGA 中 你就可以使用 CACHE hint 来告诉优化器把数据保留在 SGA 中 通常 CACHE hint 和 FULL hint 一起使用 例如 SELECT FULL AC01 CACHE AC01 FROM AC01 ROWID hint 告诉 ORACLE 使用 TABLE ACCESS BY ROWID 的操作访问表 采用 TABLE ACCESS BY ROWID 的方式特别是当访问大表的时候 使用这种方式 你需要知道 ROIWD 的值或者使用索引 索引 hint 告诉 ORACLE 使用基于索引的扫描方式 你不必说明具体的索引名称 例如 SELECT index IDX AC01 AAC002 aac001 FROM AC01 WHERE aac002 2101111111111111111 在不使用 hint 的情况下 以上的查询应该也会使用索引 然而 如果该索引的重复值过多而你的优 PLSQL 程序性能分析及优化 第 20 页 共 28 页 化器是 CBO 优化器就可能忽略索引 在这种情况下 你可以用 INDEX hint 强制 ORACLE 使用 该索引 ORACLE hints 还包括 ALL ROWS FIRST ROWS RULE USE NL USE MERGE USE HASH 等等 使用 hint 表示我们对 ORACLE 优化器缺省的执行路径不满意 需要手工修改 这是一个很有技巧性的工作 除非特定的情况 例如 数据转换 其他情况最好不用 4 3 1 表上存在过旧的分析 我们现在很多项目都存在性能问题 其中有很多种情况都是由于分析过旧导致 ORACLE 判断索 引级别和资源成本上出现问题 会导致 ORACLE 判断错误不使用索引 我个人觉得这是 ORACLE 不够完善的地方 解决办法 第一种办法 删除分析 停止 oracle10g 的自动分析 但不使用分析 oracle 访问数据的 CPU 消 耗就过大 第二种办法 重新分析 但过长时间后 索引是否会再次失效 没有验证过 4 3 1 表上存在并行 表上存在并行 ORACLE 判断索引级别和资源成本上出现问题 会导致 ORACLE 判断错误不使 用索引 这个问题我不知道有什么好的处理办法 从现场实际应用速度角度比较 我还是选择去掉并行 因为不使用索引进行全表扫描肯定是不能忍受的 4 3 1 关于索引建立 E 索引的使用是肯定会大大提高查询的速度 但索引其实也是一种数据 它也是存放的用户类型的 表空间下的 索引建立的越多越大 占用的空间也越大 从用户的环境来说这也不是问题 但如 果一个表有过多过大的查询 必然会影响 insert delete 和 update 索引列的速度 因为这些操作 改变了整个表的索引顺序 oracle 需要进行调整 这样性能就下降了 因此我们一定要合理的建 立好有效的索引 编程也要符合索引的规则 而不能是索引符合编程的规则 案例 某项目数据转换 采用游标循环 insert 的方式 总共 2000 万的数据 总共用了 4 个小时 原因 就是目标表里面有很多索引 解决方法是先删除索引再执行转换脚本 结果不用 1 小时就完成了 建立全部的索引不到半个小时 原因就是第一种方式每次 insert 都改变索引顺序 共执行改变 2000 万次 而第二种方式整体上 执行索引顺序就一次 4 3 2 当有多个索引可供选择时 使用的是 DB 设计者所希望的索引 说明 使用能最大限度 效率高 地提取到数据的索引 一般来说 能够抽取原始数据的 1 50 以内的 索引 才是效率高的 如果不具备这样的条件的话 或许全表扫描也许会很快 PLSQL 程序性能分析及优化 第 21 页 共 28 页 重要度 可选 举例 参考 4 4 存储函数 4 4 1 对于经常使用的 SQL 语句 循环处理中使用的 SQL 等 可以通过预 编译 绑定变量来提高性能 E 说明 由于 SQL 可以被再利用 所以可缩短 SQL 解析时间 重要度 必须 举例 高效的写法 VAR 0 SELECT COUNT FROM A TABLE A WHERE A KEY VAR VAR 1 SELECT COUNT FROM A TABLE A WHERE A KEY VAR 低效的写法 SELECT COUNT FROM A TABLE A WHERE A KEY 0 SELECT COUNT FROM A TABLE A WHERE A KEY 1 参考 4 5 分组和排序的使用 4 5 1 去掉没有意义的 GROUP BY ORDER BY 子语 说明 对于数据库来说 ORDER BY GROUP BY 执行起来最耗费资源的处理 重要度 必须 举例 高效的写法 A ITEM 只抽取 1 条数据 所以 GROUP BY 结果也是 1 行 没有意义 SELECT 1 A ITEM FROM A TABLE A WHERE A ITEM ABC 低效的写法 A ITEM 只抽取 1 条数据 所以 GROUP BY 结果也是 1 行 没有意义 PLSQL 程序性能分析及优化 第 22 页 共 28 页 SELECT COUNT A ITEM FROM A TABLE A WHERE A ITEM ABC GROUP BY A ITEM 参考 4 5 2 group by 优化 说明 Group by 需要查询后排序 速度慢影响性能 如果查询数据量大 并且分组复杂 这样的查询语 句在性能上是有问题的 尽量避免使用分组或者采用上面的章节的办法去代替 采用 group by 的也一定要进行优化 重要度 必须 举例 高效的写法 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 低效的写法 select ac04 aac001 ac01 aac002 ac01 aac003 sum aac040 ac01 aab001 from ac04 ac01 where ac04 aac001 ac01 aac001 and ac01 aab001 1000000370 group by ac04 aac001 ac01 aac002 ac01 aac003 ac01 aab001 参考 4 5 3 尽量避免用 order by E 说明 Order by 需要查询后排序 速度慢影响性能 如果查询数据量大 排序的时间就很长 但我们也 不能避免不使用 这样大家一定注意一点的是如果使用 order by 那么排序的列表必须符合索引 这样在速度上会得到很大的提升 重要度 可选 举例 参考 4 6 优化 4 6 1 减少对表的查询 说明 该问题是我们编程中出现过的问题 请大家一定注意 并且该类问题优化可以带来较大性能的提 PLSQL 程序性能分析及优化 第 23 页 共 28 页 升 重要度 必选 举例 高效的写法 cursor cur kc24 is Select akc260 aka130 from kc24 where akb020 str akb020 and 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 低效的写法 cursor cur kc24 mz is Select akc260 from kc24 where akb020 str akb020 and aka130 11 cursor cur kc24 zy is Select akc260 from kc24 where 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 参考 高效的做法使用同样的条件 或者说是索引 只访问一次磁盘 低效的做法访问了 2 次磁盘 这 样速度差别将近 2 倍 PLSQL 程序性能分析及优化 第 24 页 共 28 页 4 6 2 避免循环 游标 里面嵌查询 E 说明 游标里面不能嵌入查询 或者再嵌游标 其实也不能有 update delete 等语句 只能有 insert 语句 但在实际的编程情况下是不可能完全避免的 但我们一定要尽量避免 该类问题也是我们程序中 出现过的问题 该类问题也可以大大提升程序效率 请大家一定注意 重要度 可选 举例 高效的写法 Cursor cur ac04 is Select ac01 aac001 ac04 akc010 ac01 aac008 From ac04 ac01 Where ac04 aac001 ac01 aac001 and aab001 prm aab001 For rec ac04 in cur ac04 loop if rec aac008 1 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 低效的写法 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 ac01 where aac001 rec ac04 aac001 if str 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 参考 优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来 这样相当于只访问了 PLSQL 程序性能分析及优化 第 25 页 共 28 页 1 次磁盘读到内存 如果放到游标中的话 假如游标有 100 万数据量 那么程序需要 100 万次磁 盘 可以想象浪费了多少 IO 的访问 如果在程序编写上没有办法避免游标中有查询语句的话 一般情况是可以避免的 那么也要保 证游标中的查询使用的索引 即查询速度非常快 例如 游标 100 万数据量 游标中的查询语 句执行需要 0 02 秒 从这个速度上来说是很快的 但总体上看 100 万 0 02 秒 2 万秒 5 小时 33 分钟 如果写一个不够优化的语句需要 1 秒 那么需要几天能执行完呢 4 6 3 尽量用 union all 替换 union 说明 Union 会去掉重复的记录 会有排序的动作 会浪费时间 因此在没有重复记录的情况下或可以 允许有重复记录的话 要尽量采用 union all 来关联 重要度 可选 举例 参考 4 6 4 使用 DECODE 函数来减少处理时间 说明 使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表 重要度 必选 举例 高效的写法 select count decode aac008 1 1 null zz count decode aac008 2 1 null tx from ac01 where aab001 100001 低效的写法 select count 1 from ac01 where aab001 100001 and aac008 1 select count 1 from ac01 where aab001 100001 and aac008 2 Select count 1 aac008 From ac01 Where aab001 100001 and aac008 in 1