全表扫描下的逻辑读

来源：互联网发布：淘宝qq号卖家不见了编辑：程序博客网时间：2024/05/16 16:09

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

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号块

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe

QQ: 252803295

Email: oracledba_cn@hotmail.com

Blog: http://blog.csdn.net/guoyJoe

ITPUB: http://www.itpub.net/space-uid-28460966.html

OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036