ITPub博客

首页 > 数据库 > Oracle > [20140318]隐含参数_db_block_max_cr_dba

[20140318]隐含参数_db_block_max_cr_dba

原创 Oracle 作者:lfree 时间:2014-03-18 11:47:39 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2627
  • 访问量
    6390630