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

转载声明： DBA+社群（dbaplus）。http://dbaplus.cn/news-10-108-1.html

在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性能问题。

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

扫码关注

DBAplus社群

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

转载声明： DBA+社群（dbaplus）。http://dbaplus.cn/news-10-108-1.html

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

转载于:http://blog.itpub.net/26390465/viewspace-1847704/