X$BH分析（一）

1.X$BH 相关字段的说明
CLASS      NUMBER
           class:表示buffer header对应block的类型:
           1=datablock,                  9=2nd levelbmb,     
           2=sortblock,                  10=3rd levelbmb,    
           3=save undoblock,             11=bitmapblock,     
           4=segmentheader,              12=bitmap index block,
           5=save undoheader,            13=unused,           
           6=freelist,                   14=undoheader,      
           7=extentmap,                  15=undoblock   

STATE      NUMBER
          KCBBHFREE        0      buffer free
          KCBBHEXLCUR      1      buffer current (and if DFS locked X)
          KCBBHSHRCUR      2      buffer current (and if DFS locked S)
          KCBBHCR          3      buffer consistant read
          KCBBHREADING     4      Being read
          KCBBHMRECOVERY   5      media recovery (current & special)
          KCBBHIRECOVERY   6      Instance recovery (somewhat special)
                           7      WRITE: writing to disk
                           8      PI: past image block involved in cache fusion block transfer
                     
OBJ       NUMBER    Data object number
TCH       NUMBER    8.1 Touch Count

--数据准备
SQL> desc test
 Name                                     Null?   Type
 ------------------------------------------------- ----------------------------
 ID1                                               NUMBER
 ID2                                               NUMBER
 TEXT
 
SQL> insert into test
  2  select * from (selectobject_id,data_object_id,object_name from dba_objects order byobject_id) where rownum <= 1000;
 

SQL> commit;

Commit complete.

--重启数据库
SQL> startup force
ORACLE instance started.

--查看TEST对象的状态
select o.object_name,
      b.dbarfil,b.dbablk,
      decode(state,0,'free',1,'xcur',2,'scur',3,'cr',
             4,'read',5,'mrec',6,'irec',7,'write', 8,'pi') STATE,
 tch,class,ba
  from x$bh b, dba_objects o
 where b.obj = o.data_object_id
   and o.object_name like'TEST%'
   and o.owner='SJ'
order by object_name,dbarfil,dbablk;

no rows selected

说明TEST 相关的BLOCK还是在DATAFILE中

--做一个全表扫描
select * From test;
...

/
OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8   2296889xcur          1         4 000000008C026000
TEST                         8    2296890xcur          1         1 000000008B7A8000
TEST                         8    2296891xcur          1         1 000000008C024000
TEST                         8    2296892xcur          1         1 000000008B7A6000
TEST                         8    2296893xcur          1         1 000000008C022000

CLASS=4 表示是segment header
CALSS=1 表示是data block

 

SQL> selectsegment_name,segment_type,header_file,header_block,blocks fromdba_segments where segment_name='TEST';

SEGMENT_NAME        SEGMENT_TYPE      HEADER_FILEHEADER_BLOCK    BLOCKS
-------------------- ------------------ ----------- ----------------------
TEST                TABLE                       8     2296889         8

 

可以查到TEST表HEADER的FILE和BLOCK都X$BH中CLASS=4的信息一致

select distinct dbms_rowid.rowid_relative_fno(rowid) fno,
      dbms_rowid.rowid_block_number(rowid) block
From sj.test
order by block;

      FNO     BLOCK
---------- ----------
        8   2296890
        8   2296891
        8   2296892
        8    2296893

通过ROWID查询出来的FILE和BLOCK 也很X$BH中CLASS=1的信息一致

 

--再次执行全表扫描几次
select * From test;
...

SQL> /

OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur          5         4 000000008C026000
TEST                         8    2296890xcur          5         1 000000008B7A8000
TEST                         8    2296891xcur          5         1 000000008C024000
TEST                         8    2296892xcur          5         1 000000008B7A6000
TEST                         8    2296893xcur          5         1 000000008C022000

就会发现TCH相应增加，当然，如果你只对一部分数据执行，只有响应的块会增加

select a.* dbms_rowid.rowid_relative_fno(rowid) fno,
      dbms_rowid.rowid_block_number(rowid) block
From sj.test a where rownum =1;

FNO  BLOCK
---- ----------
8   2296890

 

预计BLOCK=2296890的TCH 会增加

SQL> /

OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur          6         4 000000008C026000
TEST                         8   2296890xcur          6         1 000000008B7A8000
TEST                         8    2296891xcur          5         1 000000008C024000
TEST                         8    2296892xcur          5         1 000000008B7A6000
TEST                         8    2296893xcur          5         1 000000008C022000

发现除了响应的块增加外，CLASS=4(segment header)也增加

--更新一行
SQL> update test set text='sj' where id1=2;

1 row updated.

SQL> /

OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur          7         4 000000008C026000
TEST                         8    2296890xcur          1         1 000000008B794000
TEST                         8    2296890cr            2         1 000000008B7A8000
TEST                         8    2296891xcur          6         1 000000008C024000
TEST                         8    2296892xcur          6         1 000000008B7A6000
TEST                         8    2296893xcur          6         1 000000008C022000

6 rows selected.

对于BA=000000008B7A8000 由原来TCH=6 STATE=xcur 变换为TCH=2STATE=cr，
对于TCH的变化不是很明白，状态变为CR，可以猜想应该是存有原来的值
而同时多出来一行

OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296890xcur          1         1 000000008B794000
猜想应该是存有TEXT='sj'的值

其他块TCH都增加是因为UPDATE走的是全表扫描，每个块都需要查找

--在原来会话执行
select * from test where id1=2;

SQL> /

OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur          8         4 000000008C026000
TEST                         8    2296890xcur          2         1 000000008B794000
TEST                         8    2296890cr            2         1 000000008B7A8000
TEST                         8    2296891xcur          7         1 000000008C024000
TEST                         8    2296892xcur          7         1 000000008B7A6000
TEST                         8    2296893xcur          7         1 000000008C022000

可以看到BA=000000008B794000 STATE=xcur TCH 增加了 而
BA=000000008B7A8000 STATE=cr的未增加

--其他会话执行
select * from test where id1=2;

/
OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur          9         4 000000008C026000
TEST                         8    2296890xcur          2         1 000000008B794000
TEST                         8    2296890cr            2         1 000000008B7A8000
TEST                         8    2296890cr            1         1 000000008B752000
TEST                         8    2296891xcur          8         1 000000008C024000
TEST                         8    2296892xcur          8         1 000000008B7A6000
TEST                         8    2296893xcur          8         1 000000008C022000

发现其他块TCH 继续增加
并多出一行：
OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296890cr            1         1 000000008B752000

这就是所谓的一致性读

 

--再开其他会话执行
select * from test where id1=2;

/
OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur         10         4 000000008C026000
TEST                         8    2296890cr            1         1 000000008B752000
TEST                         8    2296890cr            2         1 000000008B7A8000
TEST                         8    2296890xcur          2         1 000000008B794000
TEST                         8    2296890cr            1         1 000000008B750000
TEST                         8    2296891xcur          9         1 000000008C024000
TEST                         8    2296892xcur          9         1 000000008B7A6000
TEST                         8    2296893xcur          9         1 000000008C022000

8 rows selected.

你会发现又多出一行：
OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296890cr            1         1 000000008B750000

继续执行
select * from test where id1=2;

OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296889xcur         11         4 000000008C026000
TEST                         8    2296890cr            1         1 000000008B752000
TEST                         8    2296890cr            2         1 000000008B7A8000
TEST                         8    2296890xcur          2         1 000000008B794000
TEST                         8    2296890cr            1         1 000000008BF36000
TEST                         8    2296890cr            1         1 000000008B750000
TEST                         8    2296891xcur         10         1 000000008C024000
TEST                         8    2296892xcur         10         1 000000008B7A6000
TEST                         8    2296893xcur         10         1 000000008C022000

又多出一行，而并不是TCH + 1
OBJECT_NAME            DBARFIL    DBABLKSTATE       TCH     CLASS BA
-------------------- ---------- ---------- ----- -------------------- ----------------
TEST                         8    2296890cr            1         1 000000008BF36000

总结：
更新操作会生成CR块，且其他会话来查询 都会再生成STATE=CR的块
下一次会引入INDEX块...