[20180317]12c TABLE ACCESS BY INDEX ROWID BATCHED3.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> show array
arraysize 200

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,object_id from t where object_id between 1 and 10;
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 data_object_id,object_id from dba_objects where owner=user and object_name='T';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
107151 107151

2.測試:
--//假設OBJECT_ID=9的資料塊已經在資料快取.

SCOTT@test01p> @ rowid AAAaKPAAJAAAAMIAAQ
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107151 9 776 16 0x2400308 9,776 alter system dump datafile 9 block 776 ;

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> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAMIAAQ' and 'AAAaKPAAJAAAAMIAAQ';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAMIAAQ SYS 9

--//這裡使用between,沒有出現直接路徑讀.參考:http://blog.itpub.net/267265/viewspace-2134894/
--//http://blog.itpub.net/267265/viewspace-2143334/
--//注:使用rowid = 'AAAaKPAAJAAAAMIAAQ'.

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

@ 10046on 12
select rowid ,owner,object_id from t where object_id between 1 and 10;
@ 10046off

=====================
PARSING IN CURSOR #170466336 len=71 dep=0 uid=109 oct=3 lid=109 tim=3545974080 hv=765780707 ad='7ff12b56150' sqlid='9r1s0dnqu9sr3'
select rowid ,owner,object_id from t where object_id between 1 and 10
END OF STMT
PARSE #170466336:c=0,e=3413,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2044526593,tim=3545974079
EXEC #170466336:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2044526593,tim=3545974395
WAIT #170466336: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=662 tim=3545974498
WAIT #170466336: nam='db file sequential read' ela= 25771 file#=9 block#=363 blocks=1 obj#=107152 tim=3546000450
WAIT #170466336: nam='db file sequential read' ela= 323 file#=9 block#=364 blocks=1 obj#=107152 tim=3546001013
WAIT #170466336: nam='db file sequential read' ela= 5931 file#=9 block#=1595 blocks=1 obj#=107151 tim=3546007013
FETCH #170466336:c=0,e=32562,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2044526593,tim=3546007208
WAIT #170466336: nam='SQL*Net message from client' ela= 752 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=3546008047
WAIT #170466336: nam='db file sequential read' ela= 7292 file#=9 block#=1686 blocks=1 obj#=107151 tim=3546015454
WAIT #170466336: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=3546015674
WAIT #170466336: nam='db file sequential read' ela= 6864 file#=9 block#=1133 blocks=1 obj#=107151 tim=3546022650
WAIT #170466336: nam='db file parallel read' ela= 25048 files=1 blocks=5 requests=5 obj#=107151 tim=3546048262
FETCH #170466336:c=0,e=40355,p=7,cr=9,cu=0,mis=0,r=8,dep=0,og=1,plh=2044526593,tim=3546048481
STAT #170466336 id=1 cnt=9 pid=0 pos=1 obj=107151 op='TABLE ACCESS BY INDEX ROWID BATCHED T (cr=12 pr=10 pw=0 time=72789 us cost=10 size=184 card=8)'
STAT #170466336 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=26451 us cost=2 size=0 card=8)'

*** 2018-03-17 20:25:32.065
WAIT #170466336: nam='SQL*Net message from client' ela= 7854746 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=3553903476
CLOSE #170466336:c=0,e=20,dep=0,type=0,tim=3553903660
=====================

--//過濾出nam='db file行,以及前面FETCH行:
WAIT #170466336: nam='db file sequential read' ela= 25771 file#=9 block#=363 blocks=1 obj#=107152 tim=3546000450
WAIT #170466336: nam='db file sequential read' ela= 323 file#=9 block#=364 blocks=1 obj#=107152 tim=3546001013
--//索引的root以及葉子節點.
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 #170466336: nam='db file sequential read' ela= 5931 file#=9 block#=1595 blocks=1 obj#=107151 tim=3546007013
--//訪問: AAAaKPAAJAAAAY7AAE SYS object_id=2
SCOTT@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

FETCH #170466336:c=0,e=32562,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2044526593,tim=3546007208

WAIT #170466336: nam='db file sequential read' ela= 7292 file#=9 block#=1686 blocks=1 obj#=107151 tim=3546015454
WAIT #170466336: nam='db file sequential read' ela= 6864 file#=9 block#=1133 blocks=1 obj#=107151 tim=3546022650
--//訪問object_id=3,4的塊.
AAAaKPAAJAAAAaWAAP SYS                           3
AAAaKPAAJAAAARtAAd SYS                           4
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

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

WAIT #170466336: nam='db file parallel read' ela= 25048 files=1 blocks=5 requests=5 obj#=107151 tim=3546048262
--//提取5個資料塊,透過db file parallel read. 而不是6塊,因為有1塊已經進入資料快取了.
FETCH #170466336:c=0,e=40355,p=7,cr=9,cu=0,mis=0,r=8,dep=0,og=1,plh=2044526593,tim=3546048481

3.透過systeminternals包的Procmon.exe也可以看到讀取那些塊:

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

SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAMIAAQ' and 'AAAaKPAAJAAAAMIAAQ';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAMIAAQ SYS 9

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

SCOTT@test01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
259 11 6228:6692 DEDICATED 6652 22 6 alter system kill session '259,11' immediate;
--//執行緒號=6652(對於windows系統).

select rowid ,owner,object_id from t where object_id between 1 and 10;

--//Procmon.exe輸出儲存為cvs格式.擷取讀取資料檔案9的內容.
"20:35:05.7989878","ORACLE.EXE","2716","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","6652"
"20:35:05.9285280","ORACLE.EXE","2716","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","6652"
"20:35:05.9298902","ORACLE.EXE","2716","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","6652"
"20:35:05.9469323","ORACLE.EXE","2716","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","6652"
"20:35:05.9545142","ORACLE.EXE","2716","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","6652"
"20:35:05.9840630","ORACLE.EXE","2716","ReadFile","D:\app\oracle\product\12.1.0\dbhome_1\BIN\oracle.exe","SUCCESS","Offset: 181,030,400, Length: 12,288, I/O Flags: Non-cached, Paging I/O, Synchronous Paging I/O, Priority: Normal","6652"
"20:35:06.0192096","ORACLE.EXE","2716","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","6652"
"20:35:06.0192811","ORACLE.EXE","2716","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","6652"
"20:35:06.0193073","ORACLE.EXE","2716","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","6652"
"20:35:06.0193299","ORACLE.EXE","2716","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","6652"
"20:35:06.0193509","ORACLE.EXE","2716","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","6652"

2973696 /8192 = 363
2981888 /8192 = 364
--//索引的root以及葉子節點.
13066240/8192 = 1595
13811712/8192 = 1686
9281536 /8192 = 1133
--//--//訪問object_id=2,3,4的塊.
4235264 /8192 = 517
5709824 /8192 = 697
7438336 /8192 = 908
9502720 /8192 = 1160
14204928/8192 = 1734

--//依舊是順序讀取5塊,塊776沒有讀取,已經進入資料快取了.
--//對比如下,就很容易明白,dba=9,776資料塊沒有讀取,因為前面已經進入資料快取了.

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
AAAaKPAAJAAAAK5AAw SYS                           5        697
AAAaKPAAJAAAAbGAAV SYS                           6       1734
AAAaKPAAJAAAAIFAAK SYS                           7        517
AAAaKPAAJAAAASIAAB SYS                           8       1160
AAAaKPAAJAAAAMIAAQ SYS                           9        776
AAAaKPAAJAAAAOMAAp SYS                          10        908
9 rows selected.

317TABLE ACCESS BY INDEX ROWID BATCHED3

相關文章