[20130909]12C執行計劃的TABLE ACCESS BY INDEX ROWID BATCHED.txt
[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> 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 <=97;
commit ;
--這樣操作可以導致id=1在一個資料塊id=2,3在另外的資料塊。
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.開始測試:
--執行計劃使用TABLE ACCESS BY INDEX ROWID。
看看consistent gets:
--很明顯，索引很小，level=0,就是一個索引的根節點。select * from t where id=42;
--讀索引的根節點，讀資料塊，執行完成（如果建立的索引非唯一，這樣會多讀1塊，大家可另行測試).
--如果按照以前操作，讀索引的根節點，讀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.在做一個極端的測試就很清楚了：
--讀取索引根節點，確定id between 4 and 100的要讀取的資料塊，僅僅在1個塊中，應該是2而不是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次。
--視乎這種方式索引讀取2次，即使是訪問1個資料塊也要讀取2次，許多東西概念還是不是很清楚。但是執行計劃的TABLE ACCESS BY
--INDEX ROWID BATCHED的大概意思應該差不多。
總結：
注意12c這種執行計劃的變化，即使出現掃描很大的範圍，如果CF因子很小，執行計劃選擇的是 INDEX RANGE SCAN ，而不是全表掃描。
在寫[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 <=97;
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"<=3)--執行計劃使用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"<=100)
--注意我沒有使用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"<=100)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"<=100)
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 ，而不是全表掃描。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-772371/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20130910]12C執行計劃的TABLE ACCESS BY INDEX ROWID BATCHED(補充).txtIndexBAT
- Oracle 12c新特性之——TABLE ACCESS BY INDEX ROWID BATCHEDOracleIndexBAT
- 0317TABLE ACCESS BY INDEX ROWID BATCHEDIndexBAT
- 317TABLE ACCESS BY INDEX ROWID BATCHED2IndexBAT
- 317TABLE ACCESS BY INDEX ROWID BATCHED3IndexBAT
- Oracle Exadata的TABLE ACCESS STORAGE FULL執行計劃Oracle
- bitmap to rowid執行計劃下的基數計算疑問
- Oracle 執行計劃中access 和 filter的區別OracleFilter
- 【sql調優之執行計劃】temp table transformationSQLORM
- 分割槽索引（Partition Index）與SQL執行計劃（中）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（下）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（上）索引IndexSQL
- Oracle的執行計劃及資料存取方式 (ROWID,掃描方式等)Oracle
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 執行計劃-1：獲取執行計劃
- 執行計劃中Note部分顯示'PLAN TABLE' is old version
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 引數Optimizer_index_cost_adj 對執行計劃的影響Index
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 執行計劃
- SQL的執行計劃SQL
- 執行計劃的理解.
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- [20181201]奇怪的INDEX SKIP SCAN執行計劃.txtIndex
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- sql 執行計劃SQL
- ORACLE執行計劃Oracle
- 生成執行計劃的方法
- Oracle中檢視已執行sql的執行計劃OracleSQL
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- sqm執行計劃的繫結
- SqlServer的執行計劃如何分析？SQLServer