Oracle数据库全表扫描详解(四)

作者简介:惠星星,现就职于北京海天起点,持有OCP 10g、OCP 11g、OCM 11g证书,并有长达8年电力行业业务维护、数据库维护服务经验,擅长Oracle数据库性能优化、故障处理及数据可视化技术研究。

Oracle数据库全表扫描详解(一)

Oracle数据库全表扫描详解(二)

Oracle数据库全表扫描详解(三)

本小节和大家一起分享3例实际生产环境Oracle数据库全表扫描优化案例,希望实际生产环境的性能问题、分析思路、优化方法及经验总结对您在日常工作中有所帮助。

案例1、统计信息过期

问题描述

接到某公司技术人员电话,数据库服务器CPU使用率持续90以上,请求紧急支持优化。

通过AWR报告分析发现,SQL_ID: 8q0javab0jsy2,占用CPU资源较多(前四个SQL为同一张表的相同问题)。

执行计划分析

从执行计划可以发现NF_JCSJ_DQYSB表存在全表扫描,并且该执行计划存在filter类的谓词 DJXH。

统计信息分析

从统计信息可以发现,该表上次统计日期2018-02-12,数据量803行,数据块:60 blocks,字段DJXH上存在索引,业务表从上次统计日期后有近8000万的数据变更,通过前文的讲解我们可以分析出以下结论,

虽然查询键上有索引选择性较好,但由于统计信息过期,数据字典记录业务表只有60个blocks,因此,数据库解析时评估全表扫描需1个IO,索引扫描需要回表需2个IO以上,所以选择进行全表扫描。

优化办法

问题分析清楚后,优化办法就比较简单,对该表重新收集统计信息,数据库整体性能恢复正常。以下为重新收集的统计信息,供大家参考,




经验小结

  1. 定期或大批量业务数据变更后及时收集统计信息。

案例2、程序缺陷_括号()或or的作用范围混乱

问题描述

这个案例我觉得还比较有意思,这里和大家分享一下,某国企最核心收费系统,用户反馈业务办理缓慢,请求DBA进行问题排查及优化。

活动进程分析



查询数据库在线活动进程发现存在批量 latch: cache buffers chains等待事件,SQL_ID: 2fa4cc40y7gm8。

执行计划分析


通过执行计划分析,发现业务表(C_******T)存在全表扫描,造成该语句查询效率较低。

SQL语句

通过SQL语句分析,发现查询条件(t1.sort_code = ’01’业务逻辑存在缺陷,该查询条件不应该写在括号内部。

从执行计划的谓词条件也可以判断,该条件实际并没有起到查询作用。


优化方法

通过SQL语句分析,发现查询条件(t1.sort_code = ’01’)存在业务逻辑缺陷,建议将该查询条件删除或放到主查询外面,测试结果如下,

优化前COST: 15900723,优化后COST: 5,性能提升318万倍。

经验小结

  1. 开发过程中请注意括号()、or的作用范围及影响,避免开发缺陷造成系统运行故障。

案例3、选择键无索引_测试不到位或设计缺陷

问题描述

现场DBA在日常数据库巡检中发现业务高峰期单个SQL_ID同时并行60个抽取历史业务表,占用大量数据库资源。

执行计划分析

由于对业务比较熟悉,刚看到该语句时,部分报表类业务抽取数据量较大,全表扫描无法避免,所以建议应用侧进行分流,避免业务高峰期进行批量报表类操作。

但仔细进行谓词分析后发现,该语句与其它报表抽取语句不同,报表类查询语句的谓词都是弱选择类的列,但EQUIP_ID列为设备唯一标识,选择性较好,为什么还要进行全表扫描呢?


统计信息分析



可以发现EQUIP_ID有唯一值14万,但该列无索引,所以造成批量SQL语句全表扫描,通过与业务沟通该功能为新上线业务,存在性能缺陷。

经验小结

  1. 对选择性较好的索引键创建索引,提升业务SQL性能。
  2. 对新上线功能语句进行充分测试,及时发现性能缺陷。

Oracle全表扫描的系列分享到此结束,文章中的一些知识点和优化案例仅代表现实工作中的个人感悟,如有不足之处,请您多指导,希望对大家有所帮助谢谢。

未经允许不得转载:Oracle一体机用户组 » Oracle数据库全表扫描详解(四)

相关推荐