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有值,它要写入到数据文件中
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-758890/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-758890/