管好统计信息,开启SQL优化之门

卢飞 2015-11-27 09:37:27

转载声明:本文为DBA+社群原创文章,转载必须连同本订阅号二维码全文转载,并注明作者名字及来源:DBA+社群(dbaplus)。

 

 

遇到执行效率低下的SQL语句,对于DBA而言无疑是家常便饭了,但如何快速优化,把它变成小菜一碟,则得看看咱们DBA+社群联合发起人卢飞的经验之谈了。

 

专家简介

 
 

卢飞

DBA+社群联合发起人

 

Oracle 10g OCP,6年Oracle数据库维护经验,对Oracle数据库管理、数据迁移,性能优化有着丰富的实战经验。目前专注于数据库技术及自动化运维方面的研究。

 

在DBA的工作中,SQL优化的工作量占工作很大的一块,我们在平时工作中也是这样,常常遇到一些执行效率低下的SQL语句,而这些执行效率低下的SQL,有的是业务系统刚刚上线的,有的是已经执行很久但因为执行环境变化而导致出现的。这里给大家分享一个SQL的优化案例分析。

 

根据我们监控系统,发现线上OLTP的一个核心业务数据库中有一条SQL执行效率较慢。慢到什么程度?半小时执行278次,平均每次28秒,占用整个DB资源的56%。OLTP系统中,实在太慢了。

 

这里是SQL执行的相关信息。

 

 

 

SQL优化中,有很多人总是第一要看的就是执行计划,那么我们就看看这条SQL的执行计划。下面可以看到单次执行3秒左右,成本为2,consistent gets较高,执行计划中也是走INDEX RANGE SCAN。

 

 

其实单看上面的执行计划Cost还是比较低的,SQL优化中,有很多人总是第一要看的就是执行计划,但是看执行计划一定要结合结构信息,这里的结构信息就是表,索引等结构信息及数据分布信息。

 

我们先看SQL语句吧。以下SQL语句很简单,且在cn字段,c_date字段上都建有索引。

 

 

表数据量约有3.6亿数据。

 

 

在以上执行计划的基础上,根据对业务的理解,我的疑问是为什么不走cn索引?

 

这里其实可以根据谓词条件,各自查询 一下就能看到结果,根据cn查询到3条,而c_date条件查询出76w。走cn索引才对。

 

 

这里也可以使用HINT强制走cn索引看一下效果,使用HINT强制走cn索引后执行时间变为毫秒级。

 

 

 

SQL优化除了了解结构信息(表,索引),统计信息的准确性也很关键。

 

这里发现最后统计信息分析时间是5月份,相差了3个多月,所以统计信息是不正确的。

 

 

统计信息不准确的原因?

 

最终发现Oracle在10g版本中默认的GATHER_STATS_JOB没有启动,这里启动默认的GATHER_STATS_JOB,并单独收集一下表的统计信息。

 

 

 

收集完统计信息,这条SQL的执行时间下降到毫秒级别,执行计划已经变为IDX_REC_LOG_CN索引的RANGE SACN,consistent gets从原来的19409  降低到了7。效果还是很明显。

 

同样的SQL又慢了,现在的执行计划, 又开始走IDX_C_LOG_DATE索引了,而且执行时间又回到了2秒, consistent gets变为10404。以下为执行计划:

 

 

同样我们还是先检查统计信息是否正确,这里可以看到了统计信息又不正确了,但是我们发现GATHER_STATS_JOB每天都能执行成功。这是为什么?

 

 

解决方法就是定义一个单表收集的JOB。

 

这也是为什么大表都单独定义收集统计信息的原因,面试过很多的同学,基本上说出直接原因的没有多少,都说是照着网上这么做的。

 

 

这里也可以看到相关的10053事件中的成本信息,具体可以参考以下的地址了解每个类型的含义。

 


最终我们在业务维护时间创建了cn+c_date联合索引后的执行计划,至今再无类似的SQL性能问题。

 


 

即日起,凡是推送在【DBAplus社群】平台的文章,阅读量超过1000,该文章作者可获得赠书一本。大家如有好的干货文章也可以向我们的订阅号投稿,投稿邮箱:1017465571@qq.com。近期赠书有:白鳝《思想的天空》、杨志洪《Oracle核心技术》……

 

 

小编精心为大家挑选了近日最受欢迎的几篇热文:

回复001,看丁俊的《【重磅干货】看了此文,Oracle SQL优化文章不必再看!》;

回复002,看《灾备故障上了红头文件,容灾技术到底哪家强?》;

回复003,看吕海波的《去不去O,谁说了算?》;

回复004,看胡怡文《PG,一道横跨oltp到olap的梦想之桥》;

回复005,看付新《达梦专家解读:国产数据库也疯狂》;

回复006,看郭耀龙《假事务之名,深入研究UNDO与REDO》;

回复007,看宋日杰《Oracle后台专家解决library cache锁争用的终极武器》。

 

 

关于DBA+社群

DBA+社群是全中国最大的涵盖各种数据库、中间件及架构师线条的微信社群!有100+专家发起人,建有15大城市微信群,6大专业产品群,多达10000+跨界DBA加入队伍。每天1个热议话题,每周2次线上技术分享,不定期线下聚会与原创专家团干货分享,更多精彩,欢迎关注dbaplus微信订阅号!

扫码关注

DBAplus社群

超越DBA圈子,连接的不仅仅是DBA

最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节,那么长度值可能…

访客 2024年03月04日

只能说作者太用心了,优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年(即30岁左右),能做到年入40w-50w;有…

访客 2023年08月20日

230721

活动预告