[20130909]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED.txt
在写[20130809]12c Clustering Factor.txt，链接
提到执行计划里面存在TABLE ACCESS BY INDEX ROWID BATCHED，这里的BATCHED表示什么? 自己不是很清楚。
既然多了一个batched，一定与原来的不同，具体含义是什么呢？做一些简单的探究：
1.建立测试环境:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t (id number,name varchar2(20));
Table created.
--打开3个session，分别执行如下：
-session 1:
insert into t values (1,lpad('a',20,'a'));
commit ;
-session 2:
insert into t values (2,lpad('b',20,'b'));
commit ;
-session 3:
insert into t values (3,lpad('c',20,'c'));
commit ;
insert into t select rownum+3 id ,lpad('x',20,'x') name from dual connect by level commit ;
--这样操作可以导致id=1在一个数据块id=2,3在另外的数据块。
SCOTT@test01p> select rowid ,t.* from t where id between 1 and 3;
ROWID ID NAME
------------------ ---------- --------------------
AAAWxnAAJAAAAC1AAA 1 aaaaaaaaaaaaaaaaaaaa
AAAWxnAAJAAAAC3AAA 2 bbbbbbbbbbbbbbbbbbbb
AAAWxnAAJAAAAC3AAB 3 cccccccccccccccccccc
COTT@test01p> @lookup_rowid AAAWxnAAJAAAAC1AAA
OBJECT FILE BLOCK ROW DBA
--------- ---------- ---------- ---------- --------------------
93287 9 181 0 9,181
COTT@test01p> @lookup_rowid AAAWxnAAJAAAAC3AAA
OBJECT FILE BLOCK ROW DBA
--------- ---------- ---------- ---------- --------------------
93287 9 183 0 9,183
SCOTT@test01p> create unique index i_t_id on t(id);
Index created.
--分析表
SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t');
PL/SQL procedure successfully completed.
2.开始测试:
SCOTT@test01p> select * from t where id=42;
ID NAME
---------- --------------------
42 xxxxxxxxxxxxxxxxxxxx
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dvv86knmasnpw, child number 0
-------------------------------------
select * from t where id=42
Plan hash value: 1149237570
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 (0)|
|* 2 | INDEX UNIQUE SCAN | I_T_ID | 1 | 0 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
--执行计划使用TABLE ACCESS BY INDEX ROWID。
SCOTT@test01p> select * from t where id between 1 and 3 ;
ID NAME
---------- --------------------
1 aaaaaaaaaaaaaaaaaaaa
2 bbbbbbbbbbbbbbbbbbbb
3 cccccccccccccccccccc
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 38raj6puvxubp, child number 0
-------------------------------------
select * from t where id between 1 and 3
Plan hash value: 3446268138
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 3 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 3 | 1 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"--执行计划使用TABLE ACCESS BY INDEX ROWID BATCHED。 对比以上看看两者的差别到底在那里呢？视乎做范围扫描时，会出现BATCHED。
看看consistent gets:
set autot traceonly
select * from t where id=42;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--consistent gets=2，如果存在recursive calls，可以执行多次消除影响。
--很明显，索引很小，level=0,就是一个索引的根节点。select * from t where id=42;
--读索引的根节点，读数据块，执行完成（如果建立的索引非唯一，这样会多读1块，大家可另行测试).
set autot traceonly
select * from t where id between 1 and 3 ;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
735 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
--如果按照以前操作，读索引的根节点，读id=1的数据块，再读索引的根节点，读id=2的数据块，
--再读索引的根节点，读id=3的数据块，这样应该至少5个逻辑读。
--而实际的consistent gets是多少呢？4个。
--oracle实际上是如何操作的呢？可以猜测一下，oracle先读索引的根节点，确定id=1,id=2,id=3的数据块，然后再
--读取相应的数据块.由于id=1,2,3分别在2个块中，这样要读取3次，这样加起来就是4个逻辑读。好像有点问题???
--注意id=2,3在一个块中。
--这个就是batched的含义，我以前学习oracle的时候，一直以为oracle就应该这样操作的，实际上不是，这个我以前
--无法很好理解oracle的consistent gets。
3.在做一个极端的测试就很清楚了：
SCOTT@test01p> show array
arraysize 200
SCOTT@test01p> Select * from t where id between 4 and 100 ;
ID NAME
---------- --------------------
4 xxxxxxxxxxxxxxxxxxxx
....
100 xxxxxxxxxxxxxxxxxxxx
97 rows selected.
SCOTT@test01p> @dpc '' ' '
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0z6wka791vxz2, child number 0
-------------------------------------
Select * from t where id between 4 and 100
Plan hash value: 3446268138
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 98 | 3 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 98 | 1 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4 AND "ID"
--注意我没有使用hint，执行计划扫描大部分数据，因为id between 4 and 100的一个数据块中。
SCOTT@test01p> select distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) from t where id between 4 and 100;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
183
--按照以前执行计划正常应该选择全表扫描，而现在执行计划选择的是INDEX RANGE SCAN。
SCOTT@test01p> set autot traceonly
SCOTT@test01p> Select * from t where id between 4 and 100 ;
97 rows selected.
Execution Plan
---------------------------
Plan hash value: 3446268138
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 2352 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 98 | 2352 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 98 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4 AND "ID"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
3461 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
97 rows processed
--可以发现consistent gets＝4，这个4如何得到的呢？
--读取索引根节点，确定id between 4 and 100的要读取的数据块，仅仅在1个块中，应该是2而不是4啊？
--实际上再再极端的测试：
SCOTT@test01p> Select * from t where id between 1 and 100 ;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3446268138
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 100 | 2400 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 100 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
3548 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
--执行计划扫描全部数据，选择的还是索引扫描，consistent gets=4,注意这个是颠覆性的，如果数据的聚集因子很小，
--在12c下即使扫描很大的范围，选择的执行计划可能是INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID BATCHED.
3.做一个10046 跟踪看看:
SCOTT@test01p> alter system flush BUFFER_CACHE;
System altered.
SCOTT@test01p> @10046on 12
Session altered.
SCOTT@test01p> Select * from t where id between 1 and 100 ;
...
SCOTT@test01p> @10046off
Session altered.
=====================
PARSING IN CURSOR #213308544 len=43 dep=0 uid=109 ct=3 lid=109 tim=3272937076 hv=510903843 ad='7ff577ab250' sqlid='cc80t2cg77jj3'
Select * from t where id between 1 and 100
END OF STMT
PARSE #213308544:c=0,e=3840,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3446268138,tim=3272937074
EXEC #213308544:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3446268138,tim=3272937332
WAIT #213308544: nam='SQL*Net message to client' ela= 11 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3272937493
WAIT #213308544: nam='db file sequential read' ela= 30835 file#=9 block#=187 blocks=1 obj#=93288 tim=3272968472
WAIT #213308544: nam='db file sequential read' ela= 8764 file#=9 block#=181 blocks=1 obj#=93287 tim=3272977579
FETCH #213308544:c=0,e=40152,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=3446268138,tim=3272977730
WAIT #213308544: nam='SQL*Net message from client' ela= 618 driver id=1413697536 #bytes=1 p3=0 obj#=93287 tim=3272978490
WAIT #213308544: nam='db file sequential read' ela= 473 file#=9 block#=183 blocks=1 obj#=93287 tim=3272979246
WAIT #213308544: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=93287 tim=3272979385
FETCH #213308544:c=0,e=893,p=1,cr=2,cu=0,mis=0,r=99,dep=0,og=1,plh=3446268138,tim=3272979596
STAT #213308544 id=1 cnt=100 pid=0 pos=1 bj=93287 p='TABLE ACCESS BY INDEX ROWID BATCHED T (cr=4 pr=3 pw=0 time=40138 us cost=3 size=2400 card=100)'
STAT #213308544 id=2 cnt=100 pid=1 pos=1 bj=93288 p='INDEX RANGE SCAN I_T_ID (cr=2 pr=1 pw=0 time=33856 us cost=1 size=0 card=100)'
*** 2013-09-08 21:09:30.900
WAIT #213308544: nam='SQL*Net message from client' ela= 25387691 driver id=1413697536 #bytes=1 p3=0 obj#=93287 tim=3298367575
CLOSE #213308544:c=0,e=37,dep=0,type=0,tim=3298367930
=====================
--如果看db file sequential read读取的块3次，
--而STAT #213308544 id=2 cnt=100 pid=1 pos=1 bj=93288 p='INDEX RANGE SCAN I_T_ID (cr=2 pr=1 pw=0 time=33856 us cost=1 size=0 card=100)'
--指示cr=2,也就是讲索引读取2次。
SCOTT@test01p> select object_id,data_object_id,object_name from dba_objects where object_name in('I_T_ID','T') and wner=user;
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------
93287 93287 T
93288 93288 I_T_ID
SCOTT@test01p> select segment_name,segment_type,header_file,header_block,bytes,blocks from dba_segments where segment_name in('I_T_ID','T') and wner=user;
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS
-------------------- ------------------ ----------- ------------ ---------- ----------
I_T_ID INDEX 9 186 65536 8
T TABLE 9 178 65536 8
--视乎这种方式索引读取2次，即使是访问1个数据块也要读取2次，许多东西概念还是不是很清楚。但是执行计划的TABLE ACCESS BY
--INDEX ROWID BATCHED的大概意思应该差不多。
总结：
注意12c这种执行计划的变化，即使出现扫描很大的范围，如果CF因子很小，执行计划选择的是 INDEX RANGE SCAN ，而不是全表扫描。