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博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-756263/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
