从Oracle 隐含参数_db_block_max_cr_dba引伸开去
今天偶尔看到一个隐含参数_db_block_max_cr_dba，Oracle对它的解释是 Maximum Allowed Number of CR buffers per dba其默认值是6。
尽管是周末，一时手痒对其做一把测试，看看究竟是干嘛用的。
数据库版本为
SQL> select * from v$version where rownum<2;
BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
创建一张测试表
SQL> conn zhou/zhou
Connected.
SQL> create table testcr as select
SQL> create table testcr (id number);
Table created.
获得测试表testcr的file和block
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_id,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block_no from testcr;
FILE_ID BLOCK_NO
---------- ----------
7 39213
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
现在会话一执行update不提交
SQL> update testcr set id=2;
1 row updated.
会话二执行
SQL> select * from testcr;
ID
----------
1
会话一查看内存中该block的情况
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
cr 3
在会话二多次查询该表格，继续在会话一查看内存中该block的情况，发现xcur+cr刚好等于6即隐含参数_db_block_max_cr_dba值
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
cr 5
如果在会话一查询
SQL> select * from testcr;
ID
----------
2
可以发现cr从5变为4，将会话二的cr版本置换出去
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
cr 4
先将事务回滚
SQL> rollback;
Rollback complete.
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
cr 4
如果将内存刷出会发生什么事情呢
SQL> alter system flush buffer_cache;
System altered.
可以看到cr 状态变为free。
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
free 4
再次在会话一中查询，可以看到产生一个xcur块。
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
free 4
SQL> select * from testcr;
ID
----------
1
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
free 4
SQL> update testcr set id=5;
1 row updated.
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
free 4
cr 1
如果在会话二执行数次查询testcr表，再在会话一中查询
SQL> /
STATUS COUNT(*)
------- ----------
xcur 1
free 4
cr 5
经过以上测试我们可以得到以下结论：
1、 隐含参数 _db_block_max_cr_dba为xcur+cr的值，并不是Oracle所说的cr在buffer cache的最大数量。
2、数据块第一次进入buffer cache的模式为xcur模式
3、update一张表格，即使这张表格在内存中，也会触发cr读，这个结论可以从10046中得到再次验证。
实验如下：
SQL> alter system flush buffer_cache;
System altered.
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
free 11
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Session altered.
SQL> update testcr set id=8;
1 row updated.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> select status,count(*) from v$bh where file#=7 and block#=39213 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
free 11
cr 1
[ora10g@mcprod udump]$ tkprof mcstar_ora_21794.trc testcr.trc sys=no
********************************************************************************
update testcr set id=8
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.00 0.00 1 7 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 2 8 2 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE TESTCR (cr=7 pr=1 pw=0 time=208 us)
1 TABLE ACCESS FULL TESTCR (cr=7 pr=0 pw=0 time=29 us)
这里我们不禁有个疑问为什么会产生7个cr读呢？注意到执行计划TABLE ACCESS FULL TESTCR，它会扫描高水位以下的所有block（本例中segment 头除外）
SQL> select BLOCK_ID,BLOCKS from dba_extents where OWNER='ZHOU' and SEGMENT_NAME='TESTCR';
BLOCK_ID BLOCKS
---------- ----------
39209 8
SQL> select status,count(*) from v$bh where file#=7 and block#=39212 group by status;
STATUS COUNT(*)
------- ----------
xcur 1
free 1
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Session altered.
SQL> update testcr set id=9;
1 row updated.
[ora10g@mcprod udump]$ cat mcstar_ora_21794.trc|grep "Consistent read started"
Consistent read started for block 7 : 01c0992c
Consistent read started for block 7 : 01c0992d
Consistent read started for block 7 : 01c0992e
Consistent read started for block 7 : 01c0992f
Consistent read started for block 7 : 01c09930
Consistent read started for block 7 : 01c0992c
Consistent read started for block 7 : 01c0992d
Consistent read started for block 7 : 01c0992e
Consistent read started for block 7 : 01c0992f
Consistent read started for block 7 : 01c09930
注意到业务块从39212开始，因为前面几个块是segment头和位图块。
SQL> /
Enter value for 1: 01c0992c
old 1: select getbfno('&1') BFNO from dual
new 1: select getbfno('01c0992c') BFNO from dual
BFNO
------------------------
datafile# is:7
datablock is:39212
dump command:alter system dump datafile 7 block 39212;
