分区表SQL TUNING时容易“被欺骗”的场景之一
近几天没有用户找到,除了看看书,就是上网浏览点东西,好不惬意。可惜好景不长,正在享受悠闲惬意的日子时,一个用户的工作人员QQ找到我,说他们在统计一些数据,但一个SQL特别慢,或者说就从来没出过数据,我说,SQL操作的表很大吗?他说,很大,但我们已经对表进行了分区,而且我们就取10分钟的数据,但还是这么慢。听到用户这么说,我放下手里的书,想了想,还是感觉有点奇怪,于是向用户要了他们的SQL,很简单的一个SQL语句,如下:
select distinct rec_no from rec_log
where rec_date >= sysdate - 1 / 144
and rec_date < sysdate;
嗯,确实如用户所说,看到这个分区表相关的SQL,虽然能确定两个可能的因素,但不能确定到底什么具体因素导致了这个SQL如此之慢,我们做SQL TUNING,最先想到的是执行计划,用户用他们常用的工具取了执行计划,给我发了过来,虽然信息不是很全,先看看再说,如下:
看了这个计划,大家可能就稍微清楚点了,原因是扫描了该表的所有分区,而且没有走索引,由用户抱怨一直没出过结果,可以判断,这个表可能很大,而且分区粒度也可能较大,后来问用户,确定整张表近1T,那么,为什么既没走索引,也没走分区剪裁呢?于是,问用户,分区键是rec_date吗?用户说是,整个过程中,我向用户核实了好几遍,他说分区键是rec_date,看这个字段的名字,也像是个分区键,于是不好再询问。先看看索引情况再说,于是,让用户反馈了该表上索引的情况:
select table_name,index_name,column_name from user_ind_columns where table_name='REC_LOG';
table_name index_name column_name
------------------- ---------------------- -----------
REC_LOG IDX_REC_DATE REC_DATE
REC_LOG PK_REC_LOG REC_ID
...
这个rec_date上还有索引,而且经过进一步查询,这还是个global索引,那么,既然有索引,rec_date还是分区键,为什么既没走索引,也没走分区剪裁呢?有些奇怪,最后还是让用户给了这个表的建表SQL,如下:
create table REC_LOG
(
REC_ID NUMBER not null,
CREATE_DATE DATE,
REC_COMMENT VARCHAR2(500),
REC_IMAGE BLOB,
REC_STAT NUMBER default 0,
REC_DATE DATE
REC_NO NUMBER
)
partition by range (CREATE_DATE)
...
至此,真相大白,用户记错了,我们也被这个字段的名字蒙蔽了,因此,系统扫描所有分区也是没办法的事儿。那么,既然在rec_date上有global索引,为什么没走呢?我们不得而知。。。让小比例收集了统计信息后,该SQL执行计划依然不变,最后,只能加hint试试:
select /*+ index(r idx_rec_date) */distinct rec_no from rec_log r
where rec_date >= sysdate - 1 / 144
and rec_date < sysdate;
加hint后执行计划改变,如下:
看了执行计划,知道性能不会太差,让用户跑了一下,6~7s出结果,征求了下用户的意见,说加hint可以,因为他们就是统计下数据,否则,对高版本的oracle,可以采取其他办法搞定,至此,问题解决,大家可以参照学习,禁止转载。