T1表全表扫描产生逻辑读的分析
做个实验给你演示一下：以表t1为例，对段t1做dump
1、t1表就一条数据M > select * from t1;
ID NAME
---------- ----------
1 AAAAA
2、找t1段的段头块
M > select header_file,header_block from dba_segments where segment_name='T1' and owner='GYJ';
HEADER_FILE HEADER_BLOCK
----------- ------------
7 130
3、另开一个会话：dump段头块（ 7文件130号块）
[root@guoyj ~]# su - oracle
[oracle@guoyj ~]$ sqlplus / as sysdba
M > 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
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
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做一个全表扫描
M > set autot traceonly;
M > select * from t1;
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
M > 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号块
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoeQQ: 252803295
Email: oracledba_cn@hotmail.com
Blog:
ITPUB:
OCM:
_____________________________________________________________ 加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!答案在：
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036