oracle 10046與select table查詢表系列(一)
測試目的
1,未建立索引前的表的掃描情況
1,掃描哪些資料塊
2,資料塊之間的關係
3,物理讀
4,邏輯讀
5,以上測試區分：全表掃描與部分表記錄掃描
6,掃描資料塊是採用單塊讀取還是多塊讀取還是先單塊讀後多塊讀取？
2,小結：
1,表掃描速度與資料塊大小的關係
2,表掃描與並行度設定的關係
3,表掃描與db cache的關係
3，示例
--建立表
SQL> create table t_detail(a int);
Table created
--共計插入的記錄為800000
SQL> select count(a),count(distinct a) from t_detail;
COUNT(A) COUNT(DISTINCTA)
---------- ----------------
800000 800000
--共計佔用資料塊個數為1213
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t_detail;
COUNT(DISTINCTDBMS_ROWID.ROWID
------------------------------
1213
--用10046 level 8分析，6級看到資訊不細
SQL> alter session set events '10046 trace name context level 8';
Session altered
SQL> select count(a) from t_detail;
COUNT(A)
----------
800000
SQL> alter session set events '10046 trace name context off';
Session altered
SQL>
--找到10046 trace檔案
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace
core_dump_dest string d:\oracle11g_64bit\diag\rdbms\orcl\orcl\cdump
max_dump_file_size string unlimited
shadow_core_dump string none
user_dump_dest string d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace
--摘錄重要資訊如下
PARSING IN CURSOR #3 len=31 dep=0 uid=61 ct=3 lid=61 tim=7616717203 hv=43331279 ad='7ff04d29798' sqlid='20221bc19abqg'
select count(a) from t_detail
END OF STMT
PARSE #3:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2369062564,tim=7616717202 --首先解析sql
EXEC #3:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2369062564,tim=7616717424 --解析完執行sql
WAIT #3: nam='SQL*Net message to client' ela= 5 driver id=1297371904 #bytes=1 p3=0 obj#=69549 tim=7616717495 --告知客戶端處理完了，開始執行sql了，讓它準備好，哈哈
WAIT #3: nam='SQL*Net message from client' ela= 513 driver id=1297371904 #bytes=1 p3=0 obj#=69549 tim=7616718107 --客戶端收到通知，說OK，開始吧，我準備接收了
WAIT #0: nam='SQL*Net message from client' ela= 3 driver id=1297371904 #bytes=1 p3=0 obj#=69549 tim=7616718237 --同上
WAIT #3: nam='db file sequential read' ela= 14330 file#=10 block#=276948 blocks=1 obj#=69549 tim=7616749526 --先單塊讀了一個69549的物件，資料塊276948（注：blocks=1也說明是單塊讀）
WAIT #3: nam='db file sequential read' ela= 743 file#=10 block#=276952 blocks=1 obj#=69549 tim=7616750606 --同上，爾後讀取另一個資料塊276952
WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1297371904 #bytes=1 p3=0 obj#=69549 tim=7616775886 --讀取這兩個塊後告知客戶端
FETCH #3:c=46801,e=57678,p=2,cr=1257,cu=0,mis=0,r=1,dep=0,og=1,plh=2369062564,tim=7616775974 --開始獲取資料了
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=1257 pr=2 pw=0 time=0 us)'
STAT #3 id=2 cnt=800000 pid=1 pos=1 bj=69549 p='TABLE ACCESS FULL T_DETAIL (cr=1257 pr=2 pw=0 time=57471 us cost=354 size=11424205 card=878785)' --全表掃描獲取資料了，2次物理讀，邏輯讀為1257
WAIT #3: nam='SQL*Net message from client' ela= 10633 driver id=1297371904 #bytes=1 p3=0 obj#=69549 tim=7616786741
WAIT #0: nam='SQL*Net message from client' ela= 2 driver id=1297371904 #bytes=1 p3=0 obj#=69549 tim=7616786828
CLOSE #3:c=0,e=17,dep=0,type=0,tim=7616786877
--哪麼上述先單塊讀取的2個資料塊是什麼呢
--讀取物件正是t_detail表
SQL> select owner,object_name from dba_objects where object_id='69549';
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------
SCOTT T_DETAIL
--detail表共佔用1280個資料塊
SQL> select segment_name,blocks from dba_segments where segment_name='T_DETAIL';
SEGMENT_NAME BLOCKS
-------------------------------------------------------------------------------- ----------
T_DETAIL 1280
--detail表已緩衝到buffer cache中
SQL> select count(block#),count(distinct block#) from v$bh where bjd=69549;
COUNT(BLOCK#) COUNT(DISTINCTBLOCK#)
------------- ---------------------
1345 1278
--t_detail表的segment block為220394,與上述trace相比較，說明不是一回事
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='T_DETAIL';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
T_DETAIL 10 220394
--根據trace檔案定位資料檔案
SQL> select name from v$datafile where file#=10;
NAME
--------------------------------------------------------------------------------
C:\TBS_HANG1.DBF
---segment head block trace內容如下
Start dump data blocks tsn: 8 file#:10 minblk 220394 maxblk 220394
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42163434
Block dump from disk:
buffer tsn: 8 rdba: 0x02835cea (10/220394)
scn: 0x0000.0057fb53 seq: 0x01 flg: 0x04 tail: 0xfb532301
frmt: 0x02 chkval: 0xa416 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001A4B8200 to 0x000000001A4BA200
01A4B8200 0000A223 02835CEA 0057FB53 04010000 [#....\..S.W.....]
01A4B8210 0000A416 00000000 00000000 00000000 [................]
01A4B8220 00000000 00000019 00000500 00000A9C [................]
01A4B8230 00000018 00000080 00000080 02843D00 [.............=..]
01A4B8240 00000000 00000018 00000000 000004E4 [................]
01A4B8250 00000000 00000000 00000000 00000018 [................]
01A4B8260 00000080 00000080 02843D00 00000000 [.........=......]
01A4B8270 00000018 00000000 00000500 02843C81 [.............<..>01A4B8280 02843C81 00000000 00000000 00000000 [.<..............>01A4B8290 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
01A4B82D0 00000001 00002000 00000000 00001434 [..... ......4...]
01A4B82E0 00000000 02835CE9 00000001 02843C81 [.....\.......<..>01A4B82F0 02835CE9 00000000 00000000 00000000 [.\..............]
01A4B8300 00000000 00000000 00000019 00000000 [................]
01A4B8310 00010FAD 10000000 02835CE8 00000008 [.........\......]
01A4B8320 02843770 00000008 02843778 00000008 [p7......x7......]
01A4B8330 02843800 00000008 02843808 00000008 [.8.......8......]
01A4B8340 02843810 00000008 02843818 00000008 [.8.......8......]
01A4B8350 02843820 00000008 02843828 00000008 [ 8......(8......]
01A4B8360 02843830 00000008 02843838 00000008 [08......88......]
01A4B8370 02843840 00000008 02843848 00000008 [@8......H8......]
01A4B8380 02843850 00000008 02843858 00000008 [P8......X8......]
01A4B8390 02843860 00000008 02843880 00000080 [`8.......8......]
01A4B83A0 02843900 00000080 02843980 00000080 [.9.......9......]
01A4B83B0 02843A00 00000080 02843A80 00000080 [.:.......:......]
01A4B83C0 02843B00 00000080 02843B80 00000080 [.;.......;......]
01A4B83D0 02843C00 00000080 02843C80 00000080 [.<.......>01A4B83E0 00000000 00000000 00000000 00000000 [................]
Repeat 140 times
01A4B8CB0 02835CE8 02835CEB 02835CE8 02843770 [.\...\...\..p7..]
01A4B8CC0 02843778 02843779 02843778 02843800 [x7..y7..x7...8..]
01A4B8CD0 02843808 02843809 02843808 02843810 [.8...8...8...8..]
01A4B8CE0 02843818 02843819 02843818 02843820 [.8...8...8.. 8..]
01A4B8CF0 02843828 02843829 02843828 02843830 [(8..)8..(8..08..]
01A4B8D00 02843838 02843839 02843838 02843840 [88..98..88..@8..]
01A4B8D10 02843848 02843849 02843848 02843850 [H8..I8..H8..P8..]
01A4B8D20 02843858 02843859 02843858 02843860 [X8..Y8..X8..`8..]
01A4B8D30 02843880 02843882 02843900 02843902 [.8...8...9...9..]
01A4B8D40 02843980 02843982 02843A00 02843A02 [.9...9...:...:..]
01A4B8D50 02843A80 02843A82 02843B00 02843B02 [.:...:...;...;..]
01A4B8D60 02843B80 02843B82 02843C00 02843C02 [.;...;...<...>01A4B8D70 02843C80 02843C82 00000000 00000000 [.<...>01A4B8D80 00000000 00000000 00000000 00000000 [................]
Repeat 139 times
01A4B9640 00000000 00000000 02835CE9 00000000 [.........\......]
01A4B9650 00000000 00000000 00000000 00000000 [................]
Repeat 185 times
01A4BA1F0 00000000 00000000 00000000 FB532301 [.............#S.]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 25 #blocks: 1280
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x02843d00 ext#: 24 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1252
mapblk 0x00000000 offset: 24
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x02843d00 ext#: 24 blk#: 128 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1280
mapblk 0x00000000 offset: 24
Level 1 BMB for High HWM block: 0x02843c81
Level 1 BMB for Low HWM block: 0x02843c81
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x02835ce9
Last Level 1 BMB: 0x02843c81
Last Level II BMB: 0x02835ce9
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 25 obj#: 69549 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02835ce8 length: 8
0x02843770 length: 8
0x02843778 length: 8
0x02843800 length: 8
0x02843808 length: 8
0x02843810 length: 8
0x02843818 length: 8
0x02843820 length: 8
0x02843828 length: 8
0x02843830 length: 8
0x02843838 length: 8
0x02843840 length: 8
0x02843848 length: 8
0x02843850 length: 8
0x02843858 length: 8
0x02843860 length: 8
0x02843880 length: 128
0x02843900 length: 128
0x02843980 length: 128
0x02843a00 length: 128
0x02843a80 length: 128
0x02843b00 length: 128
0x02843b80 length: 128
0x02843c00 length: 128
0x02843c80 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02835ce8 Data dba: 0x02835ceb
Extent 1 : L1 dba: 0x02835ce8 Data dba: 0x02843770
Extent 2 : L1 dba: 0x02843778 Data dba: 0x02843779
Extent 3 : L1 dba: 0x02843778 Data dba: 0x02843800
Extent 4 : L1 dba: 0x02843808 Data dba: 0x02843809
Extent 5 : L1 dba: 0x02843808 Data dba: 0x02843810
Extent 6 : L1 dba: 0x02843818 Data dba: 0x02843819
Extent 7 : L1 dba: 0x02843818 Data dba: 0x02843820
Extent 8 : L1 dba: 0x02843828 Data dba: 0x02843829
Extent 9 : L1 dba: 0x02843828 Data dba: 0x02843830
Extent 10 : L1 dba: 0x02843838 Data dba: 0x02843839
Extent 11 : L1 dba: 0x02843838 Data dba: 0x02843840
Extent 12 : L1 dba: 0x02843848 Data dba: 0x02843849
Extent 13 : L1 dba: 0x02843848 Data dba: 0x02843850
Extent 14 : L1 dba: 0x02843858 Data dba: 0x02843859
Extent 15 : L1 dba: 0x02843858 Data dba: 0x02843860
Extent 16 : L1 dba: 0x02843880 Data dba: 0x02843882
Extent 17 : L1 dba: 0x02843900 Data dba: 0x02843902
Extent 18 : L1 dba: 0x02843980 Data dba: 0x02843982
Extent 19 : L1 dba: 0x02843a00 Data dba: 0x02843a02
Extent 20 : L1 dba: 0x02843a80 Data dba: 0x02843a82
Extent 21 : L1 dba: 0x02843b00 Data dba: 0x02843b02
Extent 22 : L1 dba: 0x02843b80 Data dba: 0x02843b82
Extent 23 : L1 dba: 0x02843c00 Data dba: 0x02843c02
Extent 24 : L1 dba: 0x02843c80 Data dba: 0x02843c82
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x02835ce9
End dump data blocks tsn: 8 file#: 10 minblk 220394 maxblk 220394
--10046 trace中 select表首次單塊讀哪2個資料塊到底是什麼含義
SQL> select owner,segment_name,extent_id,block_id,blocks from dba_extents where file_id=10 and block_id<=276948 and block_id+blocks-1>276948;
OWNER SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ----------
SCOTT T_DETAIL 18 276864 128
SQL> select owner,segment_name,extent_id,block_id,blocks from dba_extents where file_id=10 and block_id<=276952 and block_id+blocks-1>276952;
OWNER SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ----------
SCOTT T_DETAIL 18 276864 128
--看下t_detail表的extent分配資訊
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='T_DETAIL';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 10 220392 8 --segment head block 220394位於其extent中
1 10 276336 8
2 10 276344 8
3 10 276480 8
4 10 276488 8
5 10 276496 8
6 10 276504 8
7 10 276512 8
8 10 276520 8
9 10 276528 8
10 10 276536 8
11 10 276544 8
12 10 276552 8
13 10 276560 8
14 10 276568 8
15 10 276576 8
16 10 276608 128
17 10 276736 128
18 10 276864 128 --剛好與此extent匹配，即16號的extent，共有128個資料塊
19 10 276992 128
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
20 10 277120 128
21 10 277248 128
22 10 277376 128
23 10 277504 128
24 10 277632 128
25 rows selected
---既然select detail表首先單塊讀取在226864,是否226864之前的block不是儲存真實的資料，而是儲存管理後設資料呢，我們
alter system dump datafile 10 block 220392;
--trace檔案內容如下，說明此塊為FIRST LEVEL BITMAP BLOCK,是個管理資料塊，而非真實資料
Start dump data blocks tsn: 8 file#:10 minblk 220392 maxblk 220392
*** 2013-03-15 13:40:38.510
*** SESSION ID:(8.12) 2013-03-15 13:40:38.510
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42163432
Block dump from disk:
buffer tsn: 8 rdba: 0x02835ce8 (10/220392)
scn: 0x0000.0057f9a2 seq: 0x07 flg: 0x04 tail: 0xf9a22007
frmt: 0x02 chkval: 0x5700 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001A4B8200 to 0x000000001A4BA200
01A4B8200 0000A220 02835CE8 0057F9A2 04070000 [ ....\....W.....]
01A4B8210 00005700 00000000 00000000 00000000 [.W..............]
01A4B8220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
01A4B8240 00000000 00000000 00000000 00000004 [................]
01A4B8250 FFFFFFFF 00000000 00000003 00000010 [................]
01A4B8260 00010002 00000000 00000000 00000000 [................]
01A4B8270 00000000 00000010 51428FE3 51428FE3 [..........BQ..BQ]
01A4B8280 00000000 00000000 00000000 00000000 [................]
01A4B8290 02835CE9 00000000 00000000 00000000 [.\..............]
01A4B82A0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
01A4B82C0 00010FAD 0055B51C 00000000 02835CE8 [......U......\..]
01A4B82D0 00000008 00000000 02843770 00000008 [........p7......]
01A4B82E0 00000008 00000000 00000000 00000000 [................]
01A4B82F0 00000000 00000000 00000000 00000000 [................]
Repeat 8 times
01A4B8380 00000000 00000000 00000000 11111111 [................]
01A4B8390 11111111 00000000 00000000 00000000 [................]
01A4B83A0 00000000 00000000 00000000 00000000 [................]
Repeat 484 times
01A4BA1F0 00000000 00000000 00000000 F9A22007 [............. ..]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x02835ce9 poffset: 0
unformatted: 0 total: 16 first useful block: 3
owning instance : 1
instance ownership changed at 03/15/2013 11:05:07
Last successful Search 03/15/2013 11:05:07
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 69549
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02835ce8 Length: 8 Offset: 0
0x02843770 Length: 8 Offset: 8
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 10 minblk 220392 maxblk 220392
--再轉儲下其實的資料塊，是否如猜測所想
--226336資料塊的trace---------
*** 2013-03-15 13:44:17.567
Block dump from disk:
buffer tsn: 8 rdba: 0x02843770 (10/276336)
scn: 0x0000.0057f9d0 seq: 0x01 flg: 0x06 tail: 0xf9d00601
frmt: 0x02 chkval: 0x2cc3 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001A4B8200 to 0x000000001A4BA200
01A4B8200 0000A206 02843770 0057F9D0 06010000 [....p7....W.....]
---276344資料塊的trace內容-----------為一級點陣圖塊
*** 2013-03-15 13:47:51.132
Start dump data blocks tsn: 8 file#:10 minblk 276344 maxblk 276344
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42219384
Block dump from disk:
buffer tsn: 8 rdba: 0x02843778 (10/276344)
scn: 0x0000.0057f9c8 seq: 0x06 flg: 0x04 tail: 0xf9c82006
frmt: 0x02 chkval: 0x5873 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001A4B8200 to 0x000000001A4BA200
01A4B8200 0000A220 02843778 0057F9C8 04060000 [ ...x7....W.....]
01A4B8210 00005873 00000000 00000000 00000000 [sX..............]
01A4B8220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
01A4B8240 00000000 00000000 00000000 00000004 [................]
01A4B8250 FFFFFFFF 00000000 00000001 00000010 [................]
01A4B8260 00010002 00000000 00000000 00000000 [................]
01A4B8270 00000000 00000010 51428FE3 51428FE3 [..........BQ..BQ]
01A4B8280 00000000 00000000 00000000 00000000 [................]
01A4B8290 02835CE9 00000001 00000000 00000000 [.\..............]
01A4B82A0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
01A4B82C0 00010FAD 0055B51C 00000000 02843778 [......U.....x7..]
01A4B82D0 00000008 00000000 02843800 00000008 [.........8......]
01A4B82E0 00000008 00000000 00000000 00000000 [................]
01A4B82F0 00000000 00000000 00000000 00000000 [................]
Repeat 8 times
01A4B8380 00000000 00000000 00000000 11111111 [................]
01A4B8390 11111111 00000000 00000000 00000000 [................]
01A4B83A0 00000000 00000000 00000000 00000000 [................]
Repeat 484 times
01A4BA1F0 00000000 00000000 00000000 F9C82006 [............. ..]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x02835ce9 poffset: 1
unformatted: 0 total: 16 first useful block: 1
owning instance : 1
instance ownership changed at 03/15/2013 11:05:07
Last successful Search 03/15/2013 11:05:07
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 69549
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02843778 Length: 8 Offset: 0
0x02843800 Length: 8 Offset: 8
0:Metadata 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
--------------------------------------------------------
End dump data blocks tsn: 8 file#: 10 minblk 276344 maxblk 276344
--oracle讀表單塊讀先讀到的塊為一級點陣圖資料塊
Start dump data blocks tsn: 8 file#:10 minblk 276864 maxblk 276864
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42219904
Block dump from disk:
buffer tsn: 8 rdba: 0x02843980 (10/276864)
scn: 0x0000.0057fa1a seq: 0x0c flg: 0x04 tail: 0xfa1a200c
frmt: 0x02 chkval: 0x62b2 type: 0x20=FIRST LEVEL BITMAP BLOCK
小結：1,oracle讀取表先讀取一級點陣圖資料塊，爾後才是儲存資料的資料塊
2,oracle extent management採用bitmap
--表資料量大時,採用多個bitmap block管理，如果表很小；是否就只用一個bitmap block管理或直接用segment header管理呢
--刪除表
SQL> drop table t_detail purge;
Table dropped
SQL> create table t_detail(a int);
Table created
--插入少量資料
SQL> insert into t_detail select level from dual connect by level<=3;
3 rows inserted
SQL> commit;
Commit complete
--表僅佔用一個extent
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='T_DETAIL';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 10 220392 8
--segment head block為220394剛好位於extent之中
SQL> select segment_name,header_file,header_block from dba_segments where segment_name='T_DETAIL';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
T_DETAIL 10 220394
--說明表資料佔用一個資料塊,而oracle一次性分配了8個block還有6個block未用,還用了一個段頭塊220394
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t_detail;
COUNT(DISTINCTDBMS_ROWID.ROWID
------------------------------
1
--表的資料塊為220395
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from t_detail;
DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------
220395
--我們來分析下220392到220399資料塊的內容
--語法為alter system dump datafile x block min a1 block max b-----
SQL> alter system dump datafile 10 block min 220392 block max 220399;
System altered
--第一個資料塊的十六進位制
SQL> select to_char(220392,'xxxxxxxxxxxxx') from dual;
TO_CHAR(220392,'XXXXXXXXXXXXX'
------------------------------
35ce8
-----------------------trace檔案如下，已作刪減,僅存關鍵內容-------------------
Start dump data blocks tsn: 8 file#:10 minblk 220392 maxblk 220399
*** 2013-03-15 14:36:28.605
*** SESSION ID:(8.12) 2013-03-15 14:36:28.605
------------------------第一個資料塊220392(十六進位制:35ce8)
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42163432
BH (0x000007FF01BF6F48) file#: 10 rdba: 0x02835ce8 (10/220392) class: 8 ba: 0x000007FF01B44000 --自rdba可知即第一個資料塊220392
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 105,19
dbwrid: 0 obj: 69554 objn: 69554 tsn: 8 afn: 10 hint: f
hash: [0x000007FF0AEEF9F8,0x000007FF0AEEF9F8] lru: [0x000007FF007DEE40,0x000007FEFFFED5D0]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF0443BC90,0x000007FEFFBF8F38]
st: XCURRENT md: NULL tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 8 rdba: 0x02835ce8 (10/220392)
scn: 0x0000.00581044 seq: 0x01 flg: 0x04 tail: 0x10442001
frmt: 0x02 chkval: 0xd9e2 type: 0x20=FIRST LEVEL BITMAP BLOCK --可知第一個資料塊為一級點陣圖塊
-------------------------第二個資料塊220393---------
Dump of buffer cache at level 4 for tsn=8, rdba=42163433
BH (0x000007FF01BF06C8) file#: 10 rdba: 0x02835ce9 (10/220393) class: 9 ba: 0x000007FF01A94000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 119,28
dbwrid: 0 obj: 69554 objn: 69554 tsn: 8 afn: 10 hint: f
hash: [0x000007FF0AF36098,0x000007FF0AF36098] lru: [0x000007FEFFFE4620,0x000007FF023DB410]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF0403D930,0x000007FEFFFE76F8]
st: XCURRENT md: NULL tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 8 rdba: 0x02835ce9 (10/220393)
scn: 0x0000.0058103c seq: 0x01 flg: 0x04 tail: 0x103c2101
frmt: 0x02 chkval: 0xd6a7 type: 0x21=SECOND LEVEL BITMAP BLOCK --可知第二個資料塊為一級點陣圖塊
----------------------第三個資料塊220394-----------------------------------
Dump of buffer cache at level 4 for tsn=8, rdba=42163434
BH (0x000007FF003F3C38) file#: 10 rdba: 0x02835cea (10/220394) class: 4 ba: 0x000007FF002EE000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 105,28
dbwrid: 0 obj: 69554 objn: 69554 tsn: 8 afn: 10 hint: f
hash: [0x000007FF00BEC888,0x000007FF0AF0A738] lru: [0x000007FF00BE3B70,0x000007FF017EEB30]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF0403B898,0x000007FF023F72B8]
st: XCURRENT md: NULL tch: 5
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF00BEC7D8) file#: 10 rdba: 0x02835cea (10/220394) class: 4 ba: 0x000007FF00A2A000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 119,28
dbwrid: 0 obj: 69549 objn: 0 tsn: 8 afn: 10 hint: f
hash: [0x000007FF00FF6A08,0x000007FF003F3CE8] lru: [0x000007FF00FE2610,0x000007FF0AFE2BC0]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF00FF6958) file#: 10 rdba: 0x02835cea (10/220394) class: 4 ba: 0x000007FF00F3A000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 113,28
dbwrid: 0 obj: 69549 objn: 69549 tsn: 8 afn: 10 hint: f
hash: [0x000007FEFFFF6678,0x000007FF00BEC888] lru: [0x000007FF00FF9C20,0x000007FEFFFF66B0]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FEFFFF65C8) file#: 10 rdba: 0x02835cea (10/220394) class: 4 ba: 0x000007FEFFF34000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 113,28
dbwrid: 0 obj: 69549 objn: -1 tsn: 8 afn: 10 hint: f
hash: [0x000007FF0AF0A738,0x000007FF00FF6A08] lru: [0x000007FF00FF6A40,0x000007FF0AFE1E20]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 8 rdba: 0x02835cea (10/220394)
scn: 0x0000.00581044 seq: 0x01 flg: 0x04 tail: 0x10442301
frmt: 0x02 chkval: 0xa460 type: 0x23=PAGETABLE SEGMENT HEADER ---可知第三個資料塊為段頭塊
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001A4B8200 to 0x000000001A4BA200
----第四個資料塊即是真正的資料了-----------------------------------
BH (0x000007FEFFBF8E28) file#: 10 rdba: 0x02835ceb (10/220395) class: 1 ba: 0x000007FEFFB78000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 105,19
dbwrid: 0 obj: 69554 objn: 69554 tsn: 8 afn: 10 hint: f
hash: [0x000007FEFF7DDE98,0x000007FF0AEDF0F8] lru: [0x000007FEFFFED5D0,0x000007FEFFFEDBC0]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF01BF7058,0x000007FEFFFEDBE8]
st: XCURRENT md: NULL tch: 4
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
---中間略------------
frmt: 0x02 chkval: 0x356c type: 0x06=trans data
小結：1，表資料量很小時，還有使用了3個管理資料塊，各為2個點陣圖塊和一個段頭塊；其它塊為資料塊
2，並非如之前猜的，僅一個段頭塊就可以了
3，表未建索引,讀表先是單塊讀取點陣圖管理塊，而後多塊讀資料塊
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756263/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- create table 使用select查詢語句建立表的方法分享
- select查詢之一：普通查詢
- Oracle table selectOracle
- oracle x$bh及v$bh與table cache表快取系列(一)Oracle快取
- MongoDB入門系列（三）：查詢（SELECT）MongoDB
- LINQ系列：LINQ to SQL Select查詢SQL
- Oracle Create Table as SelectOracle
- oracle undo segment header 事務表transaction table系列一OracleHeader
- 【問題】 table 和column 查詢表定義
- oracle-hr表查詢命令練習（超完整的select命令大全）Oracle
- select查詢之三：子查詢
- oracle dbms_sql執行查詢select_dml_ddl(一）OracleSQL
- select子查詢
- select查詢之六：別名與拼接
- select查詢之二：分組與排序排序
- oracle表複雜查詢Oracle
- oracle 例項表查詢Oracle
- oracle表空間查詢Oracle
- oracle系統表查詢Oracle
- oracle x$bh及v$bh與table cache表快取系列(三)Oracle快取
- oracle x$bh及v$bh與table cache表快取系列(二)Oracle快取
- select查詢之四：連線查詢
- oracle 查詢所有表的表名Oracle
- 查詢使用表空間的TABLE,INDEX,INDEX SUBPARTITIONIndex
- Oracle 查詢表與表之間的 主外來鍵關係Oracle
- 一條select的查詢的過程
- Elasticsearch系列---聚合查詢(一)Elasticsearch
- 【ORA】ORACLE錯誤一覽表，方便大家查詢！Oracle
- Oracle OCP(22)：查詢表資訊Oracle
- ORACLE全表掃描查詢Oracle
- 從一條select語句看Oracle資料庫查詢工作原理Oracle資料庫
- Oracle 基礎溫習2 查詢預設表空間及其下面的tableOracle
- oracle 雜湊查詢與巢狀查詢跟表的先後關係測試Oracle巢狀
- sqlserver查詢table,columns資訊SQLServer
- sql查詢更新update selectSQL
- Select from subquery 子查詢
- [MySQL] - 聯表查詢，查詢一個不在另一個表的記錄MySql
- yii2接入pgSQL(查詢不到表The table does not exist: {{%user}})SQL