[20140318]隐含参数_db_block_max_cr_dba .txt
许多人都知道隐含参数_db_block_max_cr_dba缺省6,可以做一些简单的测试:
1.建立测试环境：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t (id number,name varchar2(20));
Table created.
SCOTT@test> @hide _db_block_max_cr_dba
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------- --------------------------------------------- -------------- -------------- ----------------------
_db_block_max_cr_dba Maximum Allowed Number of CR buffers per dba TRUE 6 6
insert into t values (1,'a');
insert into t values (2,'b');
commit ;
SCOTT@test> select rowid,t.* from t;
ROWID ID NAME
------------------ ---------- --------------------
AABFifAAEAAAACnAAA 1 a
AABFifAAEAAAACnAAB 2 b
SCOTT@test> @lookup_rowid AABFifAAEAAAACnAAB
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
284831 4 167 1 4,167 alter system dump datafile 4 block 167 ;
SCOTT@test> host cat bh.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b#
--------------------------------------------------------------------------------
col object_name format a20
col state format a10
select
b.dbarfil,
b.dbablk,
b.class,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state,
cr_scn_bas,
cr_scn_wrp,
cr_uba_fil,
cr_uba_blk,
cr_uba_seq,
(select object_name from dba_objects where object_id = b.obj) as object_name
from x$bh b
where
dbarfil = &1 and
dbablk = &2
;
SCOTT@test> @bh 4 167
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
4 167 1 xcur 0 0 0 0 0 T
-- STATE=xcur
2.现在打开会话1，修改记录不commit:
SCOTT@test> update t set name='A' where id=1;
1 row updated.
SCOTT@test> host cat xid.sql
select dbms_transaction.local_transaction_id() x from dual ;
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR from v$transaction;
SCOTT@test> @xid
X
------------------------------
7.20.14788
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ----------------
7 20 14788 3 39664 15 6551 ACTIVE 1 1 07001400C4390000 00000000BCF20208
--打开会话2：
SCOTT@test> @bh 4 167
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
4 167 1 xcur 0 0 0 0 0 T
4 167 1 cr 3268179349 0 0 0 0 T
--可以发现加入1行，state=cr.再执行如下：
select * from t where rowid='AABFifAAEAAAACnAAB';
SCOTT@test> select * from t where rowid='AABFifAAEAAAACnAAB';
ID NAME
---------- --------------------
2 b
SCOTT@test> @bh 4 167
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
4 167 1 cr 3268179408 0 3 39664 6551 T
4 167 1 xcur 0 0 0 0 0 T
4 167 1 cr 3268179349 0 0 0 0 T
--可以发现有构造了新的块，注意CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ= 3,39664,6551与前面的@xid的查询结果一直，说明查询多读了undo段来构造新的块。
--执行如下4次。
select * from t where rowid='AABFifAAEAAAACnAAB';
/
/
/
SCOTT@test> @bh 4 167
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
4 167 1 cr 3268179594 0 3 39664 6551 T
4 167 1 cr 3268179535 0 3 39664 6551 T
4 167 1 cr 3268179534 0 3 39664 6551 T
4 167 1 cr 3268179532 0 3 39664 6551 T
4 167 1 cr 3268179408 0 3 39664 6551 T
4 167 1 xcur 0 0 0 0 0 T
4 167 1 cr 3268179349 0 0 0 0 T
7 rows selected.
--可以发现如果我不提交，即使查询rowid='AABFifAAEAAAACnAAB';也通过构造新的块，这样已经达到了6块。
--再次执行。
SCOTT@test> select * from t where rowid='AABFifAAEAAAACnAAB';
ID NAME
---------- --------------------
2 b
SCOTT@test> @bh 4 167
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
4 167 1 cr 3268179675 0 3 39664 6551 T
4 167 1 cr 3268179594 0 3 39664 6551 T
4 167 1 cr 3268179535 0 3 39664 6551 T
4 167 1 cr 3268179534 0 3 39664 6551 T
4 167 1 cr 3268179532 0 3 39664 6551 T
4 167 1 cr 3268179408 0 3 39664 6551 T
4 167 1 xcur 0 0 0 0 0 T
7 rows selected.
--可以发现CR_SCN_BAS=3268179349已经不存在。再来看看CR_SCN_BAS=3268179675对应块那个位置。
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> alter system dump datafile 4 block 167 ;
System altered.
SCOTT@test> @10to16 3268179675
10 to 16 HEX REVERSE16
-------------- ------------------
00000c2cc76db 0xdb76ccc2
Block dump from disk:
buffer tsn: 4 rdba: 0x010000a7 (4/167)
scn: 0x0000.c2cc76db seq: 0x01 flg: 0x04 tail: 0x76db0601
frmt: 0x02 chkval: 0x7a35 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000002A97255A00 to 0x0000002A97257A00
2A97255A00 0000A206 010000A7 C2CC76DB 04010000 [.........v......]
2A97255A10 00007A35 00000001 0004589F C2CC76DB [5z.......X...v..]
2A97255A20 00000000 00320002 010000A0 00000002 [......2.........]
2A97255A30 00003A0F 00C09937 00301A46 00008000 [.:..7...F.0.....]
2A97255A40 C2CC7402 00140007 000039C4 00C09AF0 [.t.......9......]
2A97255A50 000F1997 00000001 00000000 00000000 [................]
2A97255A60 00000000 00020100 0016FFFF 1F701F88 [..............p.]
2A97255A70 00001F70 1F900002 00001F88 00000000 [p...............]
2A97255A80 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
2A972579E0 00000000 00000000 00000000 0202002C [............,...]
2A972579F0 620103C1 0202022C 410102C1 76DB0601 [...b,......A...v]
Block header dump: 0x010000a7
Object id on Block? Y
seg/obj: 0x4589f csc: 0x00.c2cc76db itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.000.00003a0f 0x00c09937.1a46.30 C--- 0 scn 0x0000.c2cc7402
0x02 0x0007.014.000039c4 0x00c09af0.1997.0f ---- 1 fsc 0x0000.00000000
bdba: 0x010000a7
data_block_dump,data header at 0x2a97255a64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2a97255a64
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f70
tosp=0x1f70
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f90
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 41
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 167 maxblk 167
-- CR_SCN_BAS=3268179675(0xc2cc76db) 对应的就是csc: 0x00.c2cc76db.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.000.00003a0f 0x00c09937.1a46.30 C--- 0 scn 0x0000.c2cc7402
0x02 0x0007.014.000039c4 0x00c09af0.1997.0f ---- 1 fsc 0x0000.00000000
SCOTT@test> @xid
X
------------------------------
7.20.14788
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ----------------
7 20 14788 3 39664 15 6551 ACTIVE 1 1 07001400C4390000 00000000BCF20208
-- 做一些转换
14788 = 0x39c4 ,
UBAREC＝15 (0xf)
UBASQN= 6551 (0x1997)
SCOTT@test> host cat dfb.sql
select
dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
from dual;
select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx'))||' block '||
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) ||' ;' text
from dual;
SCOTT@test> @dfb c09af0
RFILE# BLOCK#
---------- ----------
3 39664
TEXT
-------------------------------------------
alter system dump datafile 3 block 39664 ;
--很明显这些相关信息都是对上的。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1123941/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1123941/
2101
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
