深入Oracle优化器：一条诡异执行计划的解决之道

阅读原文请点击：http://click.aliyun.com/m/22905/
摘要： CBO计算成本并选择最佳执行计划的至关重要输入物就是表和索引的统计信息，过旧或错误的统计信息则可能导致一个性能极差的执行计划被错误地选中。本文将以一个案例展示诡异的统计信息如何影响执行计划的生成。 1案例介绍    这是一个简单的sql，近两个月来对于告警明细表（分区）做月度汇总查询时，总是出现了异常缓慢的情况。

CBO计算成本并选择最佳执行计划的至关重要输入物就是表和索引的统计信息，过旧或错误的统计信息则可能导致一个性能极差的执行计划被错误地选中。本文将以一个案例展示诡异的统计信息如何影响执行计划的生成。



1案例介绍



这是一个简单的sql，近两个月来对于告警明细表（分区）做月度汇总查询时，总是出现了异常缓慢的情况。



测试SQL：



20160505101224330.png



字段NEALARM_TIME是固定条件，字段RELATED_EMS_CUID是不固定的（这些不固定条件的选择性都不强），分区裁剪到的分区有着1~3月份的数据。



关于HISTORY_ALARM表， 存放20150301至今的数据，每天大约150w数据，有按1天1分区、1个月1分区，第41个分区比较特殊，这是一个有着2016年1月~3月份数据的分区；相信各位了解到这个sql的数据分区情况，第一联系到的访问路径就是分区全表扫描或访问复合索引，毕竟访问的数据占据着1/3个分区的数据。



下面是执行计划：



20160505101234374.png



该表最新收集了表和索引的统计信息，采样比为auto，没有收集直方图，请看执行计划可以注意到其中一些奇怪的细节：

1.索引HIS_ALARM_INDEX1预估基数比父节点回表的基数还小，而且小很多；

2.索引HIS_ALARM_INDEX1是一个复合索引(NEALARM_TIME,NEEND_TIME)，访问的字段只是日期（NEALARM_TIME）



正常情况下，索引选择率>=单表选择率，通过rowid回表后filter所返回的行数要小于索引扫描返回的行数；而如果访问索引只是单纯靠日期（NEALARM_TIME）过滤数据，还要再回表，对于1/3分区数据多达1500w行，其成本代价是远高于分区全表扫描的，这也难怪查询如此缓慢。



从执行计划上可以看到问题入手点：即id 3的索引预估返回值远小于id 4单表预估返回值，这是不合理的；再者即便要访问索引，为什么选择了复合索引，而不是前导列同样为NEALARM_TIME的单字段索引？



210053看问题



为了弄清楚上一步分析后的疑问，我们收集10053 trace帮助解析CBO是如何根据统计信息选择执行计划。



1. 首先计算单表基数



20160505101250771.png




分区裁剪为Part#:40，统计信息来自分区统计信息



单表选择率，没有直方图：



选择率


阅读原文请点击：http://click.aliyun.com/m/22905/