ITPub博客

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

[20140318]隐含参数_db_block_max_cr_dba 2

原创 Oracle 作者:lfree 时间:2014-03-18 15:58:43 0 删除 编辑

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

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

注册时间:2008-01-03

  • 博文量
    2634
  • 访问量
    6394712