数据物理分布对执行计划的影响

最近处理了一个比较特殊的案例: 客户的某个业务是只保留某张表最近5天的记录,每天凌晨都会删除5天前的某一天的约300多万记录,耗时100秒左右.然而在9月4日这一天,这个业务却是执行了几个小时也没有完成,执行计划也没有变化,重新收集统计信息也不行.

客户提供了一个test case,可以通过一个相似的简单查询重现这个问题.

CREATE TABLE "QDM_0904" as select DT_CNFRM,SR_NO_ID from TEST_0904;

CREATE TABLE "QDM_0903" as select DT_CNFRM,SR_NO_ID from TEST_0904

where dt_cnfrm < to_date('2018-09-03 00:00:00','YYYY-MM-DD HH24:Mi:SS');

QDM_0903表比QDM_0904表少了一天的数据:

SQL> select dt_cnfrm,count(*) from QDM_0903 group by dt_cnfrm;

DT_CNFRM COUNT(*)

------------------- ----------

2018-08-29 00:00:00 3902217

2018-08-30 00:00:00 3759608

2018-08-31 00:00:00 3499135

2018-08-27 00:00:00 3858181

2018-08-28 00:00:00 3839321

SQL> select dt_cnfrm,count(*) from QDM_0904 group by dt_cnfrm;

DT_CNFRM COUNT(*)

------------------- ----------

2018-08-29 00:00:00 3902217

2018-08-30 00:00:00 3759608

2018-08-31 00:00:00 3499135

2018-08-27 00:00:00 3858181

2018-08-28 00:00:00 3839321

2018-09-03 00:00:00 3620259

下面这个查询QDM_0903表的sql,只需要执行10几秒就能完成:

SELECT count(*)

FROM QDM_0903

WHERE dt_cnfrm NOT IN

( SELECT dt_cnfrm

FROM ( SELECT DISTINCT dt_cnfrm

FROM QDM_0903

ORDER BY dt_cnfrm DESC

)

WHERE ROWNUM <= 4

);

执行计划如下(注意id4~8的starts=5):

而下面这个查询QDM_0904表的sql执行很长时间无法完成,只能执行一段时间后cancel(模拟出了生产环境出现的问题):

SELECT count(*)

FROM QDM_0904

WHERE dt_cnfrm NOT IN

( SELECT dt_cnfrm

FROM ( SELECT DISTINCT dt_cnfrm

FROM QDM_0904

ORDER BY dt_cnfrm DESC

)

WHERE ROWNUM <= 4

);

执行计划如下(注意id4~8的starts<>6):

两个sql的执行计划虽然没有差别,但是执行计划里面的starts列却差别很大,按照正常的filter操作,这个执行计划里面的starts应该是6(上图的71是sql没有执行完情况下的一个值,这个值在执行过程中不断增大).

做了下面几个测试:

1.把QDM_0904表比QDM_0903表多的一天记录插入到QDM_0903:

insert into QDM_0903 select * from QDM_0904 where dt_cnfrm>=to_date('20180903','yyyymmdd');

这时QDM_0903和QDM_0904两个表的记录在逻辑上是一样的,上面的测试sql查询QDM_0903也能在正常时间完成(同样记录查询QDM_0904则无法完成);

2.不加条件复制0904表:

create table qdm_0904_0 as select * from qdm_0904;

上面的测试sql换成查询QDM_0904_0,同查询qdm_0904一样,也不能在正常时间完成;

3.加order by条件复制0904表:

create table qdm_0904_1 as select * from qdm_0904 order by dt_cnfrm;

上面的测试sql换成查询QDM_0904_1,也能在正常时间完成;

测试1和3的执行计划如下(starts=6):

上面测试表明,dt_cnfrm=2018-09-03 00:00:00对应的记录,在qdm_0904表应该是不连续分布的,而不论是在QDM_0903表上补充这一天的记录,还是用order by的方式复制QDM_0904表,dt_cnfrm=2018-09-03 00:00:00对应的记录,都应该是连续分布的.同时也表明,执行计划中的filter算法,还与数据的物理分布有关.在这个sql遇到了严重的性能问题,应该可以说是有一定的算法缺陷.

临时解决方法,改写SQL:

SELECT count(*)

FROM

(select dense_rank() over (order by dt_cnfrm desc) as rn from QDM_0904 )

WHERE rn>4;

这样的SQL就不需要考虑数据具体的物理分布了,而且效率比原SQL要高很多.

本文描述的情况在生产与测试环境均为11gR2,在12c和18c版本表现如何不得而知.