Oracle12C TABLE ACCESS BY INDEX ROWID BATCHED引发的疑问

今天,公司同事问了我一个问题,Oracle 12c在执行计划中出现了TABLE ACCESS BY INDEX ROWID BATCHED“批量回表”特性,是否等同于我们消除了Nested Loop时被驱动表扫描多少次就需要回表多少次的现状?

答案是否定的,我们先看一下如下测试实验:

我们在12c环境下执行如下SQL:

select /*+ gather_plan_statistics use_hash(a,t2) */
a.owner,a.object_name,a.object_id from t a,t2 where a.owner=t2.username
and a.owner='SYSTEM'

因12c中TABLE ACCESS BY INDEX ROWID BATCHED特性受隐含参数控制(12C 默认为TRUE),我们分别将该参数改为FLASE,与TRUE:

(1)关闭TABLE ACCESS BY INDEX ROWID BATCHED特性,同ora11g版本:

2019203-2

(2)开启TABLE ACCESS BY INDEX ROWID BATCHED特性,12.2.0.1.0版本:

20191203-3

我们看到该SQL最终返回459行,被驱动表T,同样要回表459次,TABLE ACCESS BY INDEX ROWID BATCHED这个与驱动表被扫描后回表多少次是没有关系的。

那么什么是TABLE ACCESS BY INDEX ROWID BATCHED?

根据Oracle官方文档(Database SQL Tuning Guide)的描述:

20191203-4

TABLE ACCESS BY INDEX ROWID BATCHED表示,对于同一个BLOCK中的ROWID,回表时采用BATCH批量的方式来减少访问BLOCK的次数。举个例子来说“也就是同一个块中的ROWID,我要回表3次,原来可能的3个IO或BUFFER GET,如果批量回表,现在是一个”

我们通过10046 Event来观察启用TABLE ACCESS BY INDEX ROWID BATCHED与否对目标SQL的统计信息有何影响?是否有效的减少了逻辑读/物理读?

12.2.0.1.0环境下关闭TABLE ACCESS BY INDEX ROWID BATCHED:

20191203-5

12.2.0.1.0环境下默认开启TABLE ACCESS BY INDEX ROWID BATCHED:

20191203-6

我们看到,开启批量回表功能物理读有所下降,但是测试结果并不稳定(即使每次执行目标SQL前刷新buffer_cache),多数情况下,两者相等相差甚微,少数情况下开启TABLE ACCESS BY INDEX ROWID BATCHED 对应COST成本反而会高一点,需要具体问题具体分析,可以看到ORACLE CBO在不断进步,有任何可有优化的地方均在不断的进行演进,并且如果遇到因TABLE ACCESS BY INDEX ROWID BATCHED特性引发的SQL性能问题,我们可用”_optimizer_batch_table_access_by_rowid”隐含参数来控制进行变化。