全表掃描讀高水位以下的塊blocks in seg. hdr's freelists: 0blocks below: 5

做個實驗給你示範一下：以表t1為例，對段t1做dump

1.t1表就一條資料

select * from t1;

ID NAME

1 AAAAA

2.找t1段的段頭塊

select header_file,header_block from dba_segments where segment_name='T1' and wner='GYJ';

HEADER_FILE HEADER_BLOCK

7 130

3另開一個會話：dump段頭塊（ 7檔案130号塊）

[root@guoyj ~]# su - oracle

[oracle@guoyj ~]$ sqlplus / as sysdba

alter system dump datafile 7 block 130;

4.dump的trace内容

Extent Control Header

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8 --紅色的就為高水位位址

blocks in seg. hdr's freelists: 0

blocks below: 5

mapblk 0x00000000 offset: 0

Unlocked

Low HighWater Mark :

Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8

Level 1 BMB for High HWM block: 0x01c00080

Level 1 BMB for Low HWM block: 0x01c00080

Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x01c00081

Last Level 1 BMB: 0x01c00080

Last Level II BMB: 0x01c00081

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 1 obj#: 78183 flag: 0x10000000

Inc # 0

Extent Map

0x01c00080 length: 8

Auxillary Map

Extent 0 : L1 dba: 0x01c00080 Data dba: 0x01c00083

Second Level Bitmap block DBAs

DBA 1: 0x01c00081

End dump data blocks tsn: 7 file#: 7 minblk 130 maxblk 130

5.對表t1做一個全表掃描

set autot traceonly;

Execution Plan

Plan hash value: 3617692013

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 999K| 14M| 759 (2)| 00:00:10 |

| 1 | TABLE ACCESS FULL| T1 | 999K| 14M| 759 (2)| 00:00:10 |

Statistics

0 recursive calls

0 db block gets

6 consistent gets －－全表掃描讀了6個塊

5 physical reads

0 redo size

596 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

6.怎麼算出來的呢這個6

select file_id,block_id,blocks from dba_extents where segment_name='T1';

FILE_ID BLOCK_ID BLOCKS

7 128 8

這個t1段一共用了8個塊分别是128 129 130 131 132 133 134 135

高水位： 0x01c00088 即7号檔案的136号塊

讀了一次段頭塊：7檔案130号塊

讀了高水位之下的131号塊 132号塊 133号塊 134号塊 135号塊五個塊

這樣一共就讀了6個塊

注：全表掃描不坊128，129号塊，對應的如下關系

Last Level 1 BMB: 0x01c00080 -->128号塊

Last Level II BMB: 0x01c00081 -->129号塊