[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 Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t1 (id int, name varchar2(20));
insert into t1 select rownum,'x' from dual connect by level<=11 ;
commit ;

SCOTT@test> select rowid,t1.id from t1;
ROWID                      ID
------------------ ----------
AABFikAAEAAAAIfAAA          1
AABFikAAEAAAAIfAAB          2
AABFikAAEAAAAIfAAC          3
AABFikAAEAAAAIfAAD          4
AABFikAAEAAAAIfAAE          5
AABFikAAEAAAAIfAAF          6
AABFikAAEAAAAIfAAG          7
AABFikAAEAAAAIfAAH          8
AABFikAAEAAAAIfAAI          9
AABFikAAEAAAAIfAAJ         10
AABFikAAEAAAAIfAAK         11

11 rows selected.

SCOTT@test> @lookup_rowid AABFikAAEAAAAIfAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    284836          4        543          0 4,543                alter system dump datafile 4 block 543 ;

--可以确定信息在块4,543中。

SCOTT@test> @bh 4 543
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
         4        543          1 xcur                0          0          0          0          0 T1

2.建立脚本每个会话修改1条记录。
$ cat a1.sql
update t1 set name='y' where id=&1;
exec dbms_lock.sleep(300);
quit

$ cat b.sh
#! /bin/bash
for i in `seq 10`
do
        sqlplus scott/xxxx @a1.sql $i &
done

执行如下：
$ sourde b.sh

SCOTT@test> @bh 4 543
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
         4        543          1 cr         3268181308          0          3       1037       7921 T1
         4        543          1 cr         3268181307          0          3       1037       7921 T1
         4        543          1 cr         3268181306          0          3       1037       7921 T1
         4        543          1 cr         3268181305          0          3       1037       7921 T1
         4        543          1 cr         3268181304          0          3       1037       7921 T1
         4        543          1 cr         3268181303          0          3       1037       7921 T1
         4        543          1 xcur                0          0          0          0          0 T1

7 rows selected.

--可以发现state=cr仅仅6个。
--如果修改参数_db_block_max_cr_dba=8呢？
SCOTT@test> alter system set "_db_block_max_cr_dba"=8 scope=spfile ;
System altered.

--重启数据库重新测试:
SYS@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  FALSE          8              8

SCOTT@test> select rowid,t1.* from t1 where rowid='AABFikAAEAAAAIfAAK';

ROWID                      ID NAME
------------------ ---------- --------------------
AABFikAAEAAAAIfAAK         11 x

SCOTT@test> @bh 4 543
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
         4        543          1 xcur                0          0          0          0          0 T1

执行如下：
$ sourde b.sh

   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
         4        543          1 cr         3268191300          0          3       1617        739 T1
         4        543          1 cr         3268191299          0          3       1617        739 T1
         4        543          1 cr         3268191298          0          3       1617        739 T1
         4        543          1 cr         3268191297          0          3       1617        739 T1
         4        543          1 cr         3268191296          0          3       1617        739 T1
         4        543          1 cr         3268191295          0          3       1617        739 T1
         4        543          1 cr         3268191292          0          3       1617        739 T1
         4        543          1 cr         3268191291          0          3       1617        739 T1
         4        543          1 cr         3268191291          0          3       1617        739 T1
         4        543          1 cr         3268191290          0          3       1617        739 T1
         4        543          1 cr         3268191289          0          3       1617        739 T1
         4        543          1 cr         3268191286          0          3       1617        739 T1
         4        543          1 cr         3268191280          0          3       1617        739 T1
         4        543          1 cr         3268191279          0          3       1617        739 T1
         4        543          1 cr         3268191278          0          3       1617        739 T1
         4        543          1 xcur                0          0          0          0          0 T1

16 rows selected.

--可以发现state=cr的块有15行.

select rowid,t1.* from t1 where rowid='AABFikAAEAAAAIfAAK';

SCOTT@test> @bh 4 543
old  14:   dbarfil = &1 and
new  14:   dbarfil = 4 and
old  15:   dbablk = &2
new  15:   dbablk = 543
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------
         4        543          1 cr         3268191333          0          3       1617        739 T1
         4        543          1 cr         3268191300          0          3       1617        739 T1
         4        543          1 cr         3268191299          0          3       1617        739 T1
         4        543          1 cr         3268191298          0          3       1617        739 T1
         4        543          1 cr         3268191297          0          3       1617        739 T1
         4        543          1 cr         3268191296          0          3       1617        739 T1
         4        543          1 cr         3268191295          0          3       1617        739 T1
         4        543          1 cr         3268191292          0          3       1617        739 T1
         4        543          1 cr         3268191291          0          3       1617        739 T1
         4        543          1 cr         3268191291          0          3       1617        739 T1
         4        543          1 cr         3268191290          0          3       1617        739 T1
         4        543          1 cr         3268191289          0          3       1617        739 T1
         4        543          1 cr         3268191286          0          3       1617        739 T1
         4        543          1 cr         3268191280          0          3       1617        739 T1
         4        543          1 cr         3268191279          0          3       1617        739 T1
         4        543          1 cr         3268191278          0          3       1617        739 T1
         4        543          1 xcur                0          0          0          0          0 T1

17 rows selected.

--可以发现state = cr 有16行。并不是隐含参数_db_block_max_cr_dba定义的数量。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1124211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1124211/