oracle dml與block xcurrent_cr及x$bh小記

SQL> --測試表dml操作產生xcurrent與cr資料塊的關係
SQL> ---構建測試表
SQL> create table t_cr(a int);
 
Table created
 
SQL> insert into t_cr values(1);
 
1 row inserted
 
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t_cr;
 
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                             5                          48222
 
SQL> ---未提交dump表的資料塊
SQL> alter system dump datafile 5 block 48222;
 
System altered
 
SQL> select * from v$diag_info where name='Default Trace File';
 
   INST_ID NAME                                                             VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
         1 Default Trace File                                               d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_13544.trc
 

/******未提交insert前的dump,僅一個緩衝條目*********/
BH (0x000007FF41F86708) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF4136C000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 988,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF604870E8,0x000007FF604870E8] lru: [0x000007FF41F86920,0x000007FF41F866C0]
  obj-flags: object_ckpt_list
  ckptq: [0x000007FF607C4908,0x000007FF41F86968] fileq: [0x000007FF607C49A8,0x000007FF41F86978] objq: [0x000007FF41F86948,0x000007FF41F866E8]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xc2.6fb4.0] LSCN: [0x0.5b3c40] HSCN: [0x0.5b3c40] HSUB: [1]
 
/**********上述結果即如下檢視的儲存結果，二者符合***********/ 
SQL> select * from x$bh where bj=72972 AND dbarfil=5 and dbablk=48222;
 
ADDR                   INDX    INST_ID HLADDR                BLSIZ NXT_HASH         PRV_HASH         NXT_REPL         PRV_REPL               FLAG      FLAG2      LOBID      RFLAG      SFLAG   LRU_FLAG        TS#      FILE#    DBARFIL     DBABLK      CLASS      STATE  MODE_HELD    CHANGES     CSTATE LE_ADDR          DIRTY_QUEUE SET_DS                  OBJ BA               CR_SCN_BAS CR_SCN_WRP CR_XID_USN CR_XID_SLT CR_XID_SQN CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC     CR_SFL CR_CLS_BAS CR_CLS_WRP   LRBA_SEQ   LRBA_BNO   HSCN_BAS   HSCN_WRP   HSUB_SCN US_NXT           US_PRV           WA_NXT           WA_PRV           OQ_NXT           OQ_PRV           AQ_NXT           AQ_PRV             OBJ_FLAG        TCH        TIM   CR_RFCNT  SHR_RFCNT
---------------- ---------- ---------- ---------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------- ---------------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ----------
00000000216C60E8      10505          1 000007FF60484480       8192 000007FF604870E8 000007FF604870E8 000007FF41F86920 000007FF41F866C0   33554433          0          0          0          0          0          5          5          5      48222          1          1          0          1          0 00                         0 000007FF607C4238      72972 000007FF4136C000          0          0          0          0          0          0          0          0          0          0          0          0        194      28596    5979200          0          1 000007FF41F86728 000007FF41F86728 000007FF41F86738 000007FF41F86738 000007FF41F86948 000007FF41F866E8 000007FF41F86958 000007FF41F866F8        242          2 1366354120          0          0
  

/********新開一會話查詢測試表***********/  
SQL> select * from t_cr;
 
                                      A
---------------------------------------  

/*************查詢後的dump發現緩衝條目從原1條增加到3條************/

BH (0x000007FF41F67B68) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF41030000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF41F67D48,0x000007FF604870E8] lru: [0x000007FF41F67D80,0x000007FF607C46F0]
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.5b3e34],[xid: 0x1.1d.47c],[uba: 0xc000f8.134.37],[cls: 0x0.5b3e34],[sfl: 0x0],[lc: 0x0.5b3e34]
  flags: only_sequential_access
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF41F67C98) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF41032000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF41F867B8,0x000007FF41F67C18] lru: [0x000007FF41F67530,0x000007FF41F67C50]
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.5b3e33],[xid: 0x1.1d.47c],[uba: 0xc000f8.134.37],[cls: 0x0.5b3e33],[sfl: 0x0],[lc: 0x0.5b3e33]
  flags: only_sequential_access
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF41F86708) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF4136C000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF604870E8,0x000007FF41F67D48] lru: [0x000007FF41F86920,0x000007FF41F866C0]
  obj-flags: object_ckpt_list
  ckptq: [0x000007FF41F71CE8,0x000007FF41F86968] fileq: [0x000007FF41FA5648,0x000007FF41F86978] objq: [0x000007FF41F86948,0x000007FF41F866E8]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read
  LRBA: [0xc2.6fb4.0] LSCN: [0x0.5b3c40] HSCN: [0x0.5b3e34] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
 
自上可知，由原來的1個xcurrent當前塊變成2個cr塊及一個xcurrent塊

/***********再開一個select後的dump************/

BH (0x000007FF41F67318) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF41022000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF41F67C18,0x000007FF604870E8] lru: [0x000007FF41F9EC40,0x000007FF607C46F0]
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.5b4029],[xid: 0x1.1d.47c],[uba: 0xc000f8.134.37],[cls: 0x0.5b4029],[sfl: 0x0],[lc: 0x0.5b4029]
  flags: only_sequential_access
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF41F67B68) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF41030000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF41F67D48,0x000007FF41F673C8] lru: [0x000007FF41F67D80,0x000007FF41F67B20]
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.5b3e34],[xid: 0x1.1d.47c],[uba: 0xc000f8.134.37],[cls: 0x0.5b3e34],[sfl: 0x0],[lc: 0x0.5b3e34]
  flags: only_sequential_access
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF41F67C98) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF41032000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF41F867B8,0x000007FF41F67C18] lru: [0x000007FF41F67530,0x000007FF41F67C50]
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.5b3e33],[xid: 0x1.1d.47c],[uba: 0xc000f8.134.37],[cls: 0x0.5b3e33],[sfl: 0x0],[lc: 0x0.5b3e33]
  flags: only_sequential_access
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF41F86708) file#: 5 rdba: 0x0140bc5e (5/48222) class: 1 ba: 0x000007FF4136C000
  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 982,28
  dbwrid: 0 obj: 72972 objn: 72972 tsn: 5 afn: 5 hint: f
  hash: [0x000007FF604870E8,0x000007FF41F67D48] lru: [0x000007FF41F86920,0x000007FF41F866C0]
  obj-flags: object_ckpt_list
  ckptq: [0x000007FF607C4908,0x000007FF41FA47F8] fileq: [0x000007FF607C49A8,0x000007FF41FA5648] objq: [0x000007FF5C2A33A0,0x000007FF5C2A33A0]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty block_written_once redo_since_read
  LRBA: [0xc2.7ab2.0] LSCN: [0x0.5b4029] HSCN: [0x0.5b4029] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
 
 
由上可知，由原來的3個變成4個緩衝條目

小結：
     1,如果dml操作不提交，oracle查詢會構建cr資料塊
     2,不同會話的select會構造不同的cr塊
     3,但是xcurrent塊僅一個
     4,單個會話內部的select多次運地不會產生多個cr資料塊
     5,cr塊的資料結構相關的file checkpoint及objq和ckptq全是空的，也就是它們不會寫到disk datafile中
     6,xcurrent塊的資料結構fileq,objq,ckpttq有值，它要寫入到資料檔案中