[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.非常的不明确.
https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#REFRN00531
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资源.
--//实际上整体的逻辑读并不因为这样减少,仅仅避免单块读取数据,单块读再取数据.变成为读取多块按顺序,然后取在结果集的情况.
--//写的有一些长,另外在写一篇那些参数可以改变执行计划,变成原来的方式.
