0317TABLE ACCESS BY INDEX ROWID BATCHED

[20180317]12c TABLE ACCESS BY INDEX ROWID BATCHED.txt

--//简单探究12c TABLE ACCESS BY INDEX ROWID BATCHED特性.
--//当使用12c时,执行计划出现TABLE ACCESS BY INDEX ROWID BATCHED,做一些探究.

1.环境:

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t as select * from all_objects order by  DBMS_RANDOM.random;
Table created.

SCOTT@test01p> create index i_t_object_id on t(object_id);
Index created.

--//分析.
execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)

SCOTT@test01p> select rowid ,owner from t where object_id  between  1 and 10;
ROWID              OWNER
------------------ --------------------
AAAaKPAAJAAAAY7AAE SYS
AAAaKPAAJAAAAaWAAP SYS
AAAaKPAAJAAAARtAAd SYS
AAAaKPAAJAAAAK5AAw SYS
AAAaKPAAJAAAAbGAAV SYS
AAAaKPAAJAAAAIFAAK SYS
AAAaKPAAJAAAASIAAB SYS
AAAaKPAAJAAAAMIAAQ SYS
AAAaKPAAJAAAAOMAAp SYS
9 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  anscphj6zbgpn, child number 0
-------------------------------------
select rowid ,owner from t where object_id  between  1 and 10
Plan hash value: 2044526593
------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |        |       |    10 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T             |      8 |   184 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I_T_OBJECT_ID |      8 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=10)

--//执行计划出现 TABLE ACCESS BY INDEX ROWID BATCHED.

2.分析:

SCOTT@test01p> select data_object_id,object_id from dba_objects where owner=user and object_name='T';
DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
        107151     107151

SCOTT@test01p> alter system flush buffer_cache;
System altered.

SCOTT@test01p> select count(*) from v$bh where OBJD=107151 and STATUS<>'free';
  COUNT(*)
----------
         0

SCOTT@test01p> show array
arraysize 200        

SCOTT@test01p> @ 10046on 12
Session altered.

SCOTT@test01p> select rowid ,owner from t where object_id  between  1 and 10;
....

SCOTT@test01p> @ 10046off
Session altered.

--//转储文件:
=====================
PARSING IN CURSOR #182626000 len=61 dep=0 uid=109 oct=3 lid=109 tim=7078823447 hv=1307950772 ad='7ff1f992710' sqlid='anscphj6zbgpn'
select rowid ,owner from t where object_id  between  1 and 10
END OF STMT
PARSE #182626000:c=0,e=119471,p=8,cr=79,cu=0,mis=1,r=0,dep=0,og=1,plh=2044526593,tim=7078823446
EXEC #182626000:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2044526593,tim=7078823592
WAIT #182626000: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=188 tim=7078823674
WAIT #182626000: nam='db file sequential read' ela= 14442 file#=9 block#=363 blocks=1 obj#=107152 tim=7078838186
WAIT #182626000: nam='db file sequential read' ela= 388 file#=9 block#=364 blocks=1 obj#=107152 tim=7078838784
WAIT #182626000: nam='db file sequential read' ela= 5863 file#=9 block#=1595 blocks=1 obj#=107151 tim=7078844769
FETCH #182626000:c=0,e=21658,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2044526593,tim=7078845375
WAIT #182626000: nam='SQL*Net message from client' ela= 1843 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=7078847479
WAIT #182626000: nam='db file sequential read' ela= 5346 file#=9 block#=1686 blocks=1 obj#=107151 tim=7078853177
WAIT #182626000: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=7078853574
WAIT #182626000: nam='db file sequential read' ela= 6555 file#=9 block#=1133 blocks=1 obj#=107151 tim=7078860288
WAIT #182626000: nam='db file parallel read' ela= 36341 files=1 blocks=6 requests=6 obj#=107151 tim=7078897106
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #182626000:c=0,e=49661,p=8,cr=9,cu=0,mis=0,r=8,dep=0,og=1,plh=2044526593,tim=7078897390
STAT #182626000 id=1 cnt=9 pid=0 pos=1 obj=107151 op='TABLE ACCESS BY INDEX ROWID BATCHED T (cr=12 pr=11 pw=0 time&#