12c比10g索引回表消耗增多的问题

问题是这样的:
在12c中,我们测试了2种情况:
第一种是加了hint,使得12c的执行计划和10g类似,只是由于12c的nlj_batching,多了一次nestloop。但是执行计划本质是相同的,都是索引S_CONTACT_X_U1返回表查询。
第二种是使用了10g的outline hint,OFE=10g的,执行计划完全一样。

但是我们发现,无论是在12c中的哪一种情况,驱动表S_SRV_REQ的索引PA_S_SRV_REQ_1_X的full index scan返回结果差异这么大?

文章最后附在10g和12c环境中的测试经过,以及10g环境中的10046 trace和12c第一种情况的10046 trace。

12c-第一种情况:

12c-第二种情况:

10g数据库的情况:

###################################

另外,根据过滤条件,10g和12c返回结果也差不多。且消耗也差不多!

12c:

10g:

在同事的Fred Liu的帮助下,我们终于找到了答案。(Fred Liu在他近期的微信公共账号也写了这个问题。大家可以关注:『老虎刘谈SQL优化』 )

(1)首先,这个SQL逻辑有问题。可以见我附件10g.txt中的附件,有完整的sql文本

SQL的主要功能是分页,客户这里把rownum和order by写在一层关系中了。所以这个是错误的。

(2)就算是在逻辑错误,为什么10g跑快,A-rows小?
这是因为SQL语句中rownum<=10, 而10g的返回记录是10行,12c的返回记录只有4行。
在rownum<=10的情况下,10g扫描索引的时候,扫描了264行之后,已经够10行了,所以可以返回;而12c中扫描索引,只有4行,也就是说,从开头扫到结尾,扫描全部还没找齐10行。所以需要扫描整个索引。因此消耗就高了。
其实我将SQL语句改写成rownum<=4,这样也可以在3秒多返回记录了。或者我将SQL改成rownum<=8000,那么在10g中运行也很慢了。

(3)修正SQL逻辑后,客户的SQL还是跑的慢,15分钟,发现因为客户写的hint用到的索引不是最优执行计划,使用acct_open_dt这个字段的索引之后(或者不用指定,oracle自己的CBO会选择这个索引),还要把1/3移动到右边,结果不到1秒就出来了。(注,如果1/3不移动到右边,还是会很慢。)

在这个案例中,我们可以学到的知识点:
1. 要分析SQL语句的逻辑,不能光看执行计划
2. 要注意count stopkey和a-rows的值。不然会想不明白为什么在10g扫描了264行就停止了。
3. 对方说数据源是一样的,不能完全相信。上述例子就可以看到,同样语句实际数据源还是有差异的,一个返回10行一个返回4行,而这个原因导致执行效率差别很大。
4. 索引字段有运算关系,需要把运算放到右边。才能正常使用索引。

附件:完整的SQL信息和执行计划 sql_compare_in_10g_and_12c.zip

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注