ORACLE内核管理-全表扫描

全表扫描步骤

1 确定段头位置。

2 按照extent map和auxiliary map读取 extent位置，读取高水位线一下的所有数据块。例如对于2个存储数据的数据段，全表扫描药扫描4次，2次块头（extent map和auxiliary map） 2次 数据块。

select * from v$event_name where name like '%buffer busy waits%';
select * From v$segment_statistics t where t.STATISTIC_NAME='buffer busy waits';

1 段头是第一个L3块也是EXTENT MAP块。
select segment_name,header_block,blocks,bytes/1024/1024,tablespace_name from dba_segments where tablespace_name='EXT'

alter system dump datafile 27 block 131;


 Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 128  
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x06c000ff  ext#: 0      blk#: 127    ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 127  
  mapblk  0x00000000  offset: 0    
      Disk Lock:: Locked by xid:  0x0001.00c.000002d1
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x06c000ff  ext#: 0      blk#: 127    ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 127  
  mapblk  0x00000000  offset: 0    
  Level 1 BMB for High HWM block: 0x06c00081
  Level 1 BMB for Low HWM block: 0x06c00081
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x06c00082
  Last Level 1 BMB:  0x06c00081
  Last Level II BMB:  0x06c00082
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 81299  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x06c00080  length: 128  
 
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x06c00080 Data dba:  0x06c00084
  --------------------------------------------------------
 
   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x06c00082






drop tablespace ext including contents ;
create tablespace ext datafile '+data01' size 20M reuse uniform size 40k;  --此时第一个区只有5个数据块，L3，L2，L1，DATA BLOCK
drop table t;
create table t (id number,name varchar2(100)) tablespace ext;
insert into t values(1,'ABC');
COMMIT;

SQL> select * from t;

 ID    NAME
 ----  -----
 1     ABC



执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    65 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    65 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        384  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

全表扫描跳过L2，L1块的读取，只读取段头和高水位点以下的所有数据块，也就是段头L3和数据4,5块，索引逻辑读取为4，则至少读取2次段头。