转载

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

表数据量约有3.6亿数据。

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

统计信息不准确的原因?

 

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

 

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

 

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

 

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

 

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

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

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

 

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

 

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

 

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

 

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

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

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

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

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

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

 

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

 

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

 

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

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

 

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

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

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

 

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

正文到此结束
Loading...