ITPub博客

首页 > 数据库 > Oracle > Oracle SQL优化总结

Oracle SQL优化总结

Oracle 作者:kingsql 时间:2015-07-23 09:39:01 0 删除 编辑



标题:Oracle SQL优化总结 

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]



***  SQL 优化也做了一段时间了,特此总结本人在工作中遇到的各个点,见笑了~


一、缩小范围    减少额外io/cpu/物理/逻辑等性能消耗

①、缩小范围(全扫能否加过滤条件最好是索引条件’)

②、分区表尽量加分区条件

执行计划区别:(PARTITION RANGE ALL分区全扫)  (PARTITION RANGE SINGLE分区范围)”

③、是否存在没意义的关联条件
例如1A.id in (Select id from a1) and A.id in (Select id from a1 where id=1)

      此时可以注释掉①部分避免没必要的关联和查询。

例如2:业务功能类似,功能1 需要范围abcde5张表,功能2范围abc3张表;

排除的/字段都等需求都相同,开发可能为了省事改写,直接将功能 2 sql直接使用功能1 sql

最终最外层将不需要字段筛选掉。

④、left join 确认能否改成join

left join 关联方式 存在选错驱动表被驱动表,某些时候可能还会存在量小应该走索引时不走索引。

且产生大量不必要的中间结果,产生大量物理/逻辑读;Join可以过滤数据(过滤条件)

二、执行计划

①、小表/小分区 变大表/大分区,不统计引起的 执行计划错误问题;

②、特殊情况可加hint driving_sitefullno_indexuse_hash ..)语句加hint需要和dba协商

数据库统计信息没问题时,大多数没特殊情况都是不需要加hint指定执行计划的;

③、善于用绑定变量;

④、使用标量子查询要量力而行;

⑤、能传入定值部分尽量传入定值

若某字段传一定值,凡是用到这个传定值字段的部分都尽量直接传值 (例如:关联条件,group by)

oracle在生成执行计划时要通过关联条件等衡量2个表哪个驱动被驱动表,若直接写定值,

数据库会更准确的选择最好的执行计划去数据库中读取数据,group by 排序部分为了减少资源消耗。

执行计划出现如下情况需要严重注意的:

①、CARTESIAN,笛卡尔乘积,需要确认是否有落下的表没有和其他表做关联;

②、filter,驱动表量越大越会引起性能问题;

③、Nest loop 驱动表或被驱动表全扫时
最糟糕的情况:驱动表和被驱动表都是全扫,且随着量的增加后续会存在性能问题..

大表做循环-->加索引

选择性很差的索引循环-->删索引

-->2大表做nest loop循环时,需要衡量使用频繁度且尽量和dba协商,来适当加索引;

-->nest loop是选择性很差(distinct key 很小)的索引(且执行时间较慢)时,需要dba分析该索引;

若删除索引,建议做个统计确认这几个月是否真的没人用再进行删除;

参考博客:http://blog.itpub.net/28602568/viewspace-1362044/

三、经验点

①、update 和merge的选择参考 Oracle 如何选择update、merge 

②、去重 distinct、group by 的选择;

子查询里包含group by 可能存在子查询外层数据量很小传不到子查询内层的情况;参考Oracle 针对子查询里有group by 表全扫的优化 

③、不要使用(id = 1 or in 子查询 )形式

原因:执行计划会选择filter,驱动表过滤后条目很多的话,被驱动表会产生热点块;

博客参考:Oracle 'or exists/in'结合使用引起的filter执行计划 的优化

④、exists 和 in 的选择(避免filter)

exists 比 in 产生filter执行计划几率大,所以当sql慢,且按F5发现执行计划中有filter 可以试着改成in
若出现
in 子查询里表全扫或者还存在慢的情况,可以考虑将in (子查询) A与外层Bjoin关联,
若还存在问题,确认是否存在本文提到的情况,针对整体衡量去优化。
补充:[not] exists 子查询里不存在与外层关联的条件,最终结果有无数据的现象   
参考博客:http://blog.itpub.net/28602568/viewspace-1666675/

⑤、分页:rownumrow_number的选择

博客参考:Oracle rownum 分页引起的效率问题及优化思路

⑥、适当选择使用rowid...

博客参考:Oracle 通过rowid秒优SQL

⑦、尽量不写is null 等会引起不走索引的条件,“索引不记录空值

若字段有nvl函数索引可以写NVL(字段, 0)=0来当做is null判断,可走函数索引

⑧、代码If count >0.. 做判断时,若rownum=1能查出数据就满足count>0
就不必全部取出再判断count>0...所以这样的判断可以统一加 rownum=1
全部做更改后dba需要跟踪sql确认是否有存在rownum缺点引起的问题
Oracle rownum 分页引起的效率问题及优化思路
mysql 使用limit 1oracle 使用rownum=1 来取数据中一行。

⑨、 合理使用临时表解决大量下载功能

 Oracle SQL优化了解业务多重要 http://blog.itpub.net/28602568/viewspace-1685600/

⑩、 临时用的表: 临时属性表?普通表?

临时表:不可统计,create/insert大量后,再调用临时表可能存在选择索引情况引起性能问题可能;

普通表:create as select ... 不统计,再调用时,数据库会使用动态采样去产生正确的执行计划;若是insert需要及时统计,否则可能会存在和临时表的问题。


  【源于本人笔记】 若有书写错误,表达错误,请指正...



此条目发表在   SQL、SQL优化篇  分类目录。将固定连接加入收藏夹。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28389881/viewspace-1745258/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
OCM

注册时间:2013-02-28

  • 博文量
    231
  • 访问量
    827832