性能优化

ORACLE 性能优化 （初级版）

1.sql脚本

（1）DML指令

delete 命令

• 为加快删除速度，尽量使用truncate，而不使用delete。DELETE 需要查找每一行对每一行进行判断并删除，truncate是直接批量删除，
  使用delete时进行删除时，只要没有commit，oracle会将被删除的数据，放在回滚段中，以便回滚时数据恢复；
  而truncate，是隐式提交，不可以回滚，所以资源很少被占用，所以缩短了执行时间，加快了执行效率。
• 如果有可能删除或禁用索引之后再删除内容
  当对表中的数据进行删除的时候，索引也要动态的维护，这样就降低了数据的维护速度。
• 使用merge来删除（数据量比较大的情况下
  merge效率更快，特别大时二者可能没什么区别。）（delete直接跟在update后面）

INSERT INTO 命令

• 插入多条记录时,使用查询结果集批量插入，代替一行行的插入
• 对表插入数据时，删除掉索引或者使索引失效（实际并不怎么可行，因为会带来额外的
  重新创建或启用、重新排序的工作）
  当对表中的数据进行删除的时候，索引也要动态的维护，这样就降低了数据的维护速度。

UPDATE 命令

• 版本支持的情况下，考虑一下merge into的用法
  UPDATE 是每一行对每一行进行判断，对符合条件行的对应列值进行逐个跟新；而 merge INTO 是批量查找批量更新，所有效率更快。
• 对表更新数据时，删除掉索引或者使索引失效
  当对表中的数据进行更新的时候，索引也要动态的维护，这样就降低了数据的维护速度。
• 需要修改多列时，尽量放在一个字句中。

多用commit

（2）查询(DQL)指令

From 关键词

• 利用with As 临时表、视图，减少嵌套的层数。
  嵌套的查询越多，查询效率越低，而利用临时表、视图可以把查询出来的结果建立一个表，这样就减少了嵌套的层数。
• 表的顺序，大表在前，小表在后
  Oracle按照从右到左的顺序对数据表进行解析。因此From最后面的表为基础表，一般情况要选择记录数据最少的表作为基础表，可能加快查询速度。
• 多取别名，减轻识别负担

Where 关键词

能过滤更多内容的条件写在后面

避免索引失效的情况

• 隐式转换导致索引失效
  对索引列进行运算导致索引失效,指的对索引列进行运算包括(+，-，*，/，! 等)

• 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引，例如rowid。

• 以下使用会使索引失效，应避免使用；
  a. 使用 <> 、not in 、not exist、!=
  b. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
  c. 单独引用复合索引里非第一位置的索引列。应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。
  d. 字符型字段为数字时在where条件里不添加引号。

OR 和 union

• 用or代替union，用or条件代替union，避免两表去做集合运算，直接where条件过滤出符合条件的数据记录
• 使用union替换or（仅适用于索引列，对索引列使用or将造成全表扫描）、union all替换union

用 >= 替换>

• 用>=替换>（前者会直接找到开始匹配的区间，后者会先找>对应的区间，再从后面再从后面找匹配的记录，所以前者会少一次比较。)当用>=条件时会先查出边界值，然后再进行筛选，最后用not in 边界值去除，在一些情况下可能比>快。

Exists 替换 In 和 关联

In 的时候 小表在里面好 用in替换or（后者会多扫描表几次进行比较，在oracle8i下二者是相同的） Exists 的时候 ，大表在里。

Group By 关键词

去重时优先使用group by与rowid

Having 关键词

尽量使用where 代替having

Select 关键词

• 用exists 替换一 OR Distinct
• 不用 *，多取别名
  在多表查询中，选择的列名前尽量跟上表的别名（这样可以减少识别时间，还能避免column
  歧义的语法错误）

Order BY

尽量不要 order By

（3）缓冲池

oracle中适当调整缓冲池大小，选择前面已有的sql语句执行（oracle每次执行都会先从SGA查看是否有相同的sql语句，有的话就直接执行该语句）

（4）表的问题

• 是否分区，是否合理分区。
• 是否有建立合适的索引。
• 如果这些操作，都做完了，我还会去看执行计划，分析一下性能瓶颈，是否可以改善。

（5）执行计划

扫描机制

建立索引，使用索引扫描

关联机制

尝试的用hints指令进行修改

避免排序

在出现union、intersect、minus时，用其它不会排序的语句代替（因为这几个都会进行排序）

最后

如果说从代码上优化的效果不尽人意，我会请教领导和业务，自己思考，是否可以在数理逻辑，业务逻辑进行优化。

2.sql程序块

一直慢

• 较少嵌套，控制循环
  尽量减少嵌套，更改不必要的循环
• 临时表用完就删
• 变量，参数的命名规范
• 数据量特别大时控制游标的使用
• 逻辑的优化
• 临时表用完就删
• 控制游标的使用
• DML指令多Commit，释放资源，防止出现死锁的情况

突然变慢

• 数据量确实突增
• 并发
• 事务锁定的情况
• 缓存的情况