浅析Oracle全表扫描下的逻辑读

T1表全表扫描产生逻辑读的分析

做个实验给你演示一下：以表t1为例，对段t1做dump

1、t1表就一条数据

gyj@OCM> select * from t1;

     ID NAME

---------- ----------

      1 AAAAA

2、找t1段的段头块

gyj@OCM> 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

sys@OCM> 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

查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

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

5、对表t1做一个全表扫描

gyj@OCM> set autot traceonly;

gyj@OCM> 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

gyj@OCM> 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号块