[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 levelcommit ;
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定义的数量。