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=21634 us cost=10 size=184 card=8)'
STAT #182626000 id=2 cnt=9 pid=1 pos=1 obj=107152 op='INDEX RANGE SCAN I_T_OBJECT_ID (cr=3 pr=2 pw=0 time=15477 us cost=2 size=0 card=8)'
*** 2018-03-16 21:52:34.376
WAIT #182626000: nam='SQL*Net message from client' ela= 4206852 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=7083104486
CLOSE #182626000:c=0,e=32,dep=0,type=0,tim=7083104759
=====================
--//注意看下劃線出現'db file parallel read'.
SCOTT@book> @ &r/ev_name 'db file parallel read'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME CON_ID
------ ---------- --------------------- ---------- ---------- ---------- ------------- ----------- ---------- ---------------------- ------
156 834992820 db file parallel read files blocks requests 1740759767 8 User I/O db list of blocks read 0
--//引數1,2,3對應的files,blocks,requests.非常的不明確.
db file parallel read
This happens during recovery. It can also happen during buffer prefetching, as an optimization (rather than performing
multiple single-block reads). Database blocks that need to be changed as part of recovery are read in parallel from the
database.
Wait Time: Wait until all of the I/Os are completed
------------------------------------------------------------------------------------------------
Parameter Description
------------------------------------------------------------------------------------------------
files This indicates the number of files to which the session is reading
blocks This indicates the total number of blocks to be read
requests This indicates the total number of I/O requests, which will be the same as blocks
------------------------------------------------------------------------------------------------
--//這裡實際上引數P1是files值讀檔案的數量.而不是檔案號.
--//P2,讀取的資料塊數量.注意讀取的塊可以不連續.
--//P3.requests .理論應該等於P2的數值.
3.進一步分析:
--//抽取'db file sequential read'以及'db file parallel read',因為在執行我清空資料快取.
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
WAIT #182626000: nam='db file sequential read' ela= 5346 file#=9 block#=1686 blocks=1 obj#=107151 tim=7078853177
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SCOTT@test01p> select rowid ,owner,object_id from t where object_id between
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
AAAaKPAAJAAAAaWAAP SYS 3
AAAaKPAAJAAAARtAAd SYS 4
AAAaKPAAJAAAAK5AAw SYS 5
AAAaKPAAJAAAAbGAAV SYS 6
AAAaKPAAJAAAAIFAAK SYS 7
AAAaKPAAJAAAASIAAB SYS 8
AAAaKPAAJAAAAMIAAQ SYS 9
AAAaKPAAJAAAAOMAAp SYS 10
9 rows selected.
--//對應一個一個分析:
SCOTT@test01p> select header_file,header_block from dba_segments where owner=user and segment_name='I_T_OBJECT_ID';
HEADER_FILE HEADER_BLOCK
----------- ------------
9 362
WAIT #182626000: nam='db file sequential read' ela= 14442 file#=9 block#=363 blocks=1 obj#=107152 tim=7078838186
--//對於的索引段I_T_OBJECT_ID的HEADER_BLOCK的下一塊,也就是索引的root節點.
WAIT #182626000: nam='db file sequential read' ela= 388 file#=9 block#=364 blocks=1 obj#=107152 tim=7078838784
--//應該對應的葉子節點.這個塊應該記錄鍵值OBJECT_ID 1-10對應的rowid.
WAIT #182626000: nam='db file sequential read' ela= 5863 file#=9 block#=1595 blocks=1 obj#=107151 tim=7078844769
COTT@test01p> @ rowid AAAaKPAAJAAAAY7AAE
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
--------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107151 9 1595 4 0x240063B 9,1595 alter system dump datafile 9 block 1595
--//訪問OBJECT_ID=2的記錄對應的資料塊.
WAIT #182626000: nam='db file sequential read' ela= 5346 file#=9 block#=1686 blocks=1 obj#=107151 tim=7078853177
SCOTT@test01p> @ rowid AAAaKPAAJAAAAaWAAP
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107151 9 1686 15 0x2400696 9,1686 alter system dump datafile 9 block 1686
--//訪問OBJECT_ID=3的記錄對應的資料塊.
WAIT #182626000: nam='db file sequential read' ela= 6555 file#=9 block#=1133 blocks=1 obj#=107151 tim=7078860288
SCOTT@test01p> @ rowid AAAaKPAAJAAAARtAAd
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107151 9 1133 29 0x240046D 9,1133 alter system dump datafile 9 block 1133
--//訪問OBJECT_ID=4的記錄對應的資料塊.
WAIT #182626000: nam='db file parallel read' ela= 36341 files=1 blocks=6 requests=6 obj#=107151 tim=7078897106
--//這裡的files=1表示讀1個資料檔案.
--//blocks=6 表示訪問了6個塊.
--//requests=6,表示requests數量.
AAAaKPAAJAAAAK5AAw SYS 5
AAAaKPAAJAAAAbGAAV SYS 6
AAAaKPAAJAAAAIFAAK SYS 7
AAAaKPAAJAAAASIAAB SYS 8
AAAaKPAAJAAAAMIAAQ SYS 9
AAAaKPAAJAAAAOMAAp SYS 10
--//正好對應6個資料塊.如果不是12c,是看不到這個等待時間的,實際上TABLE ACCESS BY INDEX ROWID BATCHED含義就在這裡.
--//先單塊讀3條記錄,等待事件'db file sequential read' ,然後剩下的6條記錄在不同的塊中,使用db file parallel read讀取.
--//還可以發現這些塊還不是連續的.如何證明呢?
P1,P2,P3引數的含義:
------------------------------------------------------------------------------------------------
Parameter Description
------------------------------------------------------------------------------------------------
files This indicates the number of files to which the session is reading
blocks This indicates the total number of blocks to be read
requests This indicates the total number of I/O requests, which will be the same as blocks
------------------------------------------------------------------------------------------------
4.繼續測試:
SCOTT@test01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
242 7 5400:5352 DEDICATED 6360 22 3 alter system kill session '242,7' immediate;
--//在windows下spid對應tid(執行緒ID).
--//使用systeminternals包的Procmon.exe跟蹤 tid=6360.
SCOTT@test01p> select rowid ,owner,object_id from t where object_id between 1 and 10;
...
--//Procmon.exe跟蹤資訊儲存為cvs檔案.擷取訪問資料檔案9內容:
"22:29:21.9216918","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 2,973,696, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9373237","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 2,981,888, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9376931","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 13,066,240, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9447705","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 13,811,712, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9524128","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 9,281,536, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9595423","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 4,235,264, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9595879","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 5,709,824, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9596174","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 6,356,992, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9596449","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 7,438,336, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9596716","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 9,502,720, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
"22:29:21.9596991","ORACLE.EXE","2700","ReadFile","D:\app\oracle\oradata\test\test01p\SAMPLE_SCHEMA_USERS01.DBF","SUCCESS","Offset: 14,204,928, Length: 8,192, I/O Flags: Non-cached, Priority: Normal","6360"
--//正好讀11次.前面2次對應索引root節點,葉子節點.擷取offset計算塊位置.
2973696/8192 = 363
2981888/8192 = 364
--//以上2塊對應索引root節點,葉子節點.
13066240/8192 = 1595
13811712/8192 = 1686
9281536 /8192 = 1133
--//以上3條是db file sequential read.
4235264 /8192 = 517
5709824 /8192 = 697
6356992 /8192 = 776
7438336 /8192 = 908
9502720 /8192 = 1160
14204928/8192 = 1734
SCOTT@test01p> select rowid ,owner,object_id,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) "BLOCK" from t where object_id between 1 and 10;
ROWID OWNER OBJECT_ID BLOCK
------------------ -------------------- ---------- ----------
AAAaKPAAJAAAAY7AAE SYS 2 1595
AAAaKPAAJAAAAaWAAP SYS 3 1686
AAAaKPAAJAAAARtAAd SYS 4 1133
--//以上3條是db file sequential read.
AAAaKPAAJAAAAK5AAw SYS 5 697
AAAaKPAAJAAAAbGAAV SYS 6 1734
AAAaKPAAJAAAAIFAAK SYS 7 517
AAAaKPAAJAAAASIAAB SYS 8 1160
AAAaKPAAJAAAAMIAAQ SYS 9 776
AAAaKPAAJAAAAOMAAp SYS 10 908
--//剩下6條記錄是db file parallel read,注意看前面的讀取塊的順序,從小到大排序的,而且讀取的塊不相連.
--//而輸出依舊按照原來的順序.
9 rows selected.
--//我想這個應該是12c TABLE ACCESS BY INDEX ROWID BATCHED的本質.
--//windows下探究太麻煩了,如果有linux使用strace估計簡單一些.
--//至於為什麼不是一開始採用db file parallel read,我就不知道了.
5.最後轉儲葉子節點驗證看看:
SCOTT@test01p> alter system dump datafile 9 block 364;
System altered.
*** 2018-03-16 22:45:41.587
Block header dump: 0x0240016c
Object id on Block? Y
seg/obj: 0x1a290 csc: 0x00.19ad5ad itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2400168 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.019ad5ad
Leaf block dump
===============
header address 769261668=0x2dda0064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1834=0x72a
kdxcoavs 828
kdxlespl 0
kdxlende 0
kdxlenxt 37749101=0x240016d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 02 40 06 3b 00 04
row#1[8012] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 02 40 06 96 00 0f
row#2[8000] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 02 40 04 6d 00 1d
row#3[7988] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 02 40 02 b9 00 30
row#4[7976] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 02 40 06 c6 00 15
row#5[7964] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 02 40 02 05 00 0a
row#6[7952] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 02 40 04 88 00 01
row#7[7940] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 02 40 03 08 00 10
row#8[7928] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 02 40 03 8c 00 29
.....
row#483[1847] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 06 1f
col 1; len 6; (6): 02 40 04 0a 00 11
row#484[1834] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 06 20
col 1; len 6; (6): 02 40 03 8a 00 02
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 3 file#: 9 minblk 364 maxblk 364
SCOTT@test01p> @ conv_n.sql c2061f
N20
----------
530
SCOTT@test01p> @ conv_n.sql c20620
N20
----------
531
--//這個索引塊最大鍵值531.
SCOTT@test01p> alter system flush buffer_cache;
System altered.
@ 10046on
select /*+ index(t) */ rowid ,owner,object_id,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) "BLOCK" from t where object_id between 1 and 530;
484 rows selected.
@ 10046off
=====================
PARSING IN CURSOR #182855360 len=134 dep=0 uid=109 oct=3 lid=109 tim=10741325373 hv=579510468 ad='7ff1314dae0' sqlid='9fxv0qcj8p864'
select /*+ index(t) */ rowid ,owner,object_id,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) "BLOCK" from t where object_id between 1 and 530
END OF STMT
PARSE #182855360:c=0,e=5016,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2044526593,tim=10741325372
EXEC #182855360:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2044526593,tim=10741325621
WAIT #182855360: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=440 tim=10741325726
WAIT #182855360: nam='Disk file operations I/O' ela= 279 FileOperation=2 fileno=9 filetype=2 obj#=107152 tim=10741326112
WAIT #182855360: nam='db file sequential read' ela= 30577 file#=9 block#=363 blocks=1 obj#=107152 tim=10741356740
WAIT #182855360: nam='db file sequential read' ela= 404 file#=9 block#=364 blocks=1 obj#=107152 tim=10741357398
WAIT #182855360: nam='db file sequential read' ela= 5691 file#=9 block#=1595 blocks=1 obj#=107151 tim=10741363215
FETCH #182855360:c=0,e=37802,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2044526593,tim=10741363587
WAIT #182855360: nam='SQL*Net message from client' ela= 1760 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10741365446
WAIT #182855360: nam='db file sequential read' ela= 6024 file#=9 block#=1686 blocks=1 obj#=107151 tim=10741371747
WAIT #182855360: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10741372050
WAIT #182855360: nam='db file sequential read' ela= 6714 file#=9 block#=1133 blocks=1 obj#=107151 tim=10741378841
WAIT #182855360: nam='db file parallel read' ela= 179837 files=1 blocks=127 requests=127 obj#=107151 tim=10741559430
*** 2018-03-16 22:53:32.996
WAIT #182855360: nam='db file parallel read' ela= 79702 files=1 blocks=58 requests=58 obj#=107151 tim=10741643140
FETCH #182855360:c=0,e=278882,p=187,cr=192,cu=0,mis=0,r=200,dep=0,og=1,plh=2044526593,tim=10741644548
WAIT #182855360: nam='SQL*Net message from client' ela= 6712 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10741651369
WAIT #182855360: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10741651751
WAIT #182855360: nam='db file parallel read' ela= 175750 files=1 blocks=127 requests=127 obj#=107151 tim=10741828462
WAIT #182855360: nam='db file parallel read' ela= 76287 files=1 blocks=35 requests=35 obj#=107151 tim=10741910436
FETCH #182855360:c=0,e=260942,p=162,cr=198,cu=0,mis=0,r=200,dep=0,og=1,plh=2044526593,tim=10741912485
WAIT #182855360: nam='SQL*Net message from client' ela= 8507 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10741921501
WAIT #182855360: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10741947172
WAIT #182855360: nam='db file parallel read' ela= 99203 files=1 blocks=55 requests=55 obj#=107151 tim=10742048374
FETCH #182855360:c=0,e=105391,p=55,cr=83,cu=0,mis=0,r=83,dep=0,og=1,plh=2044526593,tim=10742052032
STAT #182855360 id=1 cnt=484 pid=0 pos=1 obj=107151 op='TABLE ACCESS BY INDEX ROWID BATCHED T (cr=476 pr=407 pw=0 time=41747 us cost=446 size=10212 card=444)'
STAT #182855360 id=2 cnt=484 pid=1 pos=1 obj=107152 op='INDEX RANGE SCAN I_T_OBJECT_ID (cr=5 pr=2 pw=0 time=34492 us cost=2 size=0 card=444)'
*** 2018-03-16 22:53:38.983
WAIT #182855360: nam='SQL*Net message from client' ela= 5579709 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=10747632459
CLOSE #182855360:c=0,e=14,dep=0,type=0,tim=10747632639
=====================
--//單獨抽取db file sequential read,db file parallel read.以及前面fetch行
WAIT #182855360: nam='db file sequential read' ela= 30577 file#=9 block#=363 blocks=1 obj#=107152 tim=10741356740
WAIT #182855360: nam='db file sequential read' ela= 404 file#=9 block#=364 blocks=1 obj#=107152 tim=10741357398
WAIT #182855360: nam='db file sequential read' ela= 5691 file#=9 block#=1595 blocks=1 obj#=107151 tim=10741363215
FETCH #182855360:c=0,e=37802,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2044526593,tim=10741363587
--//索引root與葉子節點.//object_id=2,注意fetch行中r=1,表示返回1行.
WAIT #182855360: nam='db file sequential read' ela= 6024 file#=9 block#=1686 blocks=1 obj#=107151 tim=10741371747
WAIT #182855360: nam='db file sequential read' ela= 6714 file#=9 block#=1133 blocks=1 obj#=107151 tim=10741378841
--//object_id=3,4對應的塊.
WAIT #182855360: nam='db file parallel read' ela= 179837 files=1 blocks=127 requests=127 obj#=107151 tim=10741559430
WAIT #182855360: nam='db file parallel read' ela= 79702 files=1 blocks=58 requests=58 obj#=107151 tim=10741643140
FETCH #182855360:c=0,e=278882,p=187,cr=192,cu=0,mis=0,r=200,dep=0,og=1,plh=2044526593,tim=10741644548
--//注意fetch行中r=200,表示返回200行.
--//1+1+127+58 = 187,可以推斷有13條記錄已經在快取中.
--//而且最大blocks=127,也就是對應db file parallel read事件,最大讀取塊數量127.
WAIT #182855360: nam='db file parallel read' ela= 175750 files=1 blocks=127 requests=127 obj#=107151 tim=10741828462
WAIT #182855360: nam='db file parallel read' ela= 76287 files=1 blocks=35 requests=35 obj#=107151 tim=10741910436
FETCH #182855360:c=0,e=260942,p=162,cr=198,cu=0,mis=0,r=200,dep=0,og=1,plh=2044526593,tim=10741912485
WAIT #182855360: nam='db file parallel read' ela= 99203 files=1 blocks=55 requests=55 obj#=107151 tim=10742048374
FETCH #182855360:c=0,e=105391,p=55,cr=83,cu=0,mis=0,r=83,dep=0,og=1,plh=2044526593,tim=10742052032
--// 1+200+200+83 = 484 與測試返回結果集一致.
5.看看前面201條讀取的塊數量:
SCOTT@test01p> SELECT block,count(*)
FROM (SELECT * FROM (SELECT /*+ index(t) */ rowid,
owner,object_id,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) "BLOCK"
FROM t
WHERE object_id BETWEEN 1 and 530)
WHERE rownum <= 201)
GROUP BY block
HAVING count(*) > 2;
BLOCK COUNT(*)
---------- ----------
1009 2
1219 2
1207 2
776 2
475 2
1249 2
419 2
1389 2
1686 2
332 2
391 2
1518 2
237 2
13 rows selected.
--//驗證一致.有一些塊存在多條記錄,這樣在讀取後進入快取,不需要讀取.
SCOTT@test01p> select rowid,object_id from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=1009 and object_id between 1 and 530;
ROWID OBJECT_ID
------------------ ----------
AAAaKPAAJAAAAPxAAt 13
AAAaKPAAJAAAAPxAAm 41
--//從以上測試看12c TABLE ACCESS BY INDEX ROWID BATCHED的本質是db file parallel read.也就是進行預讀取.
--//如果資料塊已經在快取不讀取,如果不在快取,變原來的單塊讀(等待事件db file sequential read)為一次讀取多塊(等待事件db
--//file parallel read),而且這些資料塊並不連續.
--//但是如果資料塊已經在快取.這樣的操作與判斷要讀取那些塊有點浪費cpu資源.
--//實際上整體的邏輯讀並不因為這樣減少,僅僅避免單塊讀取資料,單塊讀再取資料.變成為讀取多塊按順序,然後取在結果集的情況.
--//寫的有一些長,另外在寫一篇那些引數可以改變執行計劃,變成原來的方式.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2151974/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 317TABLE ACCESS BY INDEX ROWID BATCHED2IndexBAT
- 317TABLE ACCESS BY INDEX ROWID BATCHED3IndexBAT
- Oracle 12c新特性之——TABLE ACCESS BY INDEX ROWID BATCHEDOracleIndexBAT
- [20130909]12C執行計劃的TABLE ACCESS BY INDEX ROWID BATCHED.txtIndexBAT
- [20130910]12C執行計劃的TABLE ACCESS BY INDEX ROWID BATCHED(補充).txtIndexBAT
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- 【MySQL】MySQL5.6新特性之Batched Key AccessMySqlBAT
- MySQL Batched Key Access (BKA)原理和設定使用方法舉例MySqlBAT
- index和rowid的一點關係！Index
- Index key值(索引列上的值)以及rowidIndex索引
- use the function_based index to access the null valueFunctionIndexNull
- 唯一index和非唯一index中leaf node裡rowid的一點區別。Index
- 【ROWID】Oracle rowid說明Oracle
- oracle rowidOracle
- dbms_rowid之dbms_rowid.rowid_info儲存過程使用(in out)儲存過程
- ROWID簡介
- ROWID詳解
- ORACLE ROWID (zt)Oracle
- 轉---DBMS_ROWID.ROWID_CREATE來解決壞塊
- oracle常用包之dbms_rowid oracle rowid含義分析Oracle
- MySQL中的_rowidMySql
- oracle rowid詳解Oracle
- Oracle rowid 詳解Oracle
- Oracle ROWID-1Oracle
- Oracle ROWID-2Oracle
- oracle rowid (轉載)Oracle
- oracle rowid 的含義Oracle
- Oracle中rowid的用法Oracle
- 【oracle中rowid的用法】Oracle
- rowid一點總結
- Datatypes (1) : ROWID and UROWID
- KEEP INDEX | DROP INDEXIndex
- 索引ROWID轉換函式索引函式
- 關於rowid的應用
- rowid和rownum的區別
- rowid 以及資料儲存
- 從rowid操作相同記錄
- rowid的一點總結！