[20140318]隐含参数_db_block_max_cr_dba

[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 ;

--很明显这些相关信息都是对上的。

时间： 2024-07-31 13:16:39

[20140318]隐含参数_db_block_max_cr_dba的相关文章

[20140318]隐含参数_db_block_max_cr_dba 2

[20140318]隐含参数_db_block_max_cr_dba 2.txt 许多人都知道隐含参数_db_block_max_cr_dba缺省6,如果多个会话修改同一块呢?会发生什么情况: 参考链接做一次测试: 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterpr

Oracle 隐含参数

Oracle 隐含参数 点击(此处)折叠或打开 set pagesize 9999 set line 9999 col NAME format a40 col KSPPDESC format a50 col KSPPSTVL format a20 SELECT a.INDX,        a.KSPPINM NAME,        a.KSPPDESC,        b.KSPPSTVL FROM x$ksppi a,        x$ksppcv b WHERE a.INDX = b.

关于oracle隐含参数 总结

1.回顾 之前整理了两篇关于x$表的介绍,连接如下: http://blog.csdn.net/cymm_liu/article/details/7611675 http://blog.csdn.net/cymm_liu/article/details/7611758 数据字典系统表主要由4部分组成:1.内部RDBMS表:x$--2.数据字典表:--$(比如user$)3.动态性能视图:gv$--,v$--4.数据字典视图:user_--,all_--,dba_--数据库启动时,动态创建x$,在

javascript arguments 传递给函数的隐含参数_javascript技巧

本人第一次看到这个东东的时候,以为就是"变相"的普通参数,只不过不用在定义函数的时候明确声明而已. 不过,代码是廉价的(Code is cheap.)看代码: function funcTest(a, b) { alert(a); alert(b); for ( var i = 0 ; i < arguments.length; i ++ ) { alert(arguments[i]); } } function test() { funcTest( 1 , 2 , 3 ); /

js的隐含参数(arguments,callee,caller)使用方法_javascript技巧

在提到上述的概念之前,首先想说说javascript中函数的隐含参数: arguments arguments 该对象代表正在执行的函数和调用它的函数的参数.[function.]arguments[n]参数function:选项.当前正在执行的 Function 对象的名字. n :选项.要传递给 Function 对象的从0开始的参数值索引.说明Arguments是进行函数调用时,除了指定的参数外,还另外创建的一个隐藏对象.Arguments是一个类似数组但不是数组的对象,说它类似数组是因为

[20171109]查看隐含参数脚本.txt

[20171109]查看隐含参数脚本.txt --//查看隐含参数的脚本,今天没事修改一下增加查询description字段的内容.也有网友要求提供这个脚本,实际上这些脚本都是自己工作 --//中不断收集整理.网上许多地方都能找到. $ cat hide.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_va

[20151208]隐含参数_trace_pin_time.txt

[20151208]隐含参数_trace_pin_time.txt --使用隐含参数可以trace how long a current pin is held,作为测试与学习了解oracle内部相关知识. --自己测试看看. 1.测试环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------

[20170502]11G查询隐含参数视图.txt

[20170502]11G查询隐含参数视图GV$SYSTEM_PARAMETER3.txt --//oracle 存在许多隐含参数,一直以为oracle没有提供正常的视图查询该内容,实际上oracle 11G已经提供这方面的功能,只不过oracle并不公开. --//自己也是偶然发现: 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER -------------------------

[20120106]使用bash shell显示隐含参数.txt

我个人喜欢使用putty打开两个窗口,一个执行sql语句,另外一个查看目录user_dump_dest下的trc文件.如果要查询隐含参数,往往要切换sys用户,执行一个脚本: $ cat hide.sqlcol name format a36col description format a66col session_value format a22col default_value format a22col system_value format a22 select   a.ksppinm