从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 ([color=red]cr=7 [/color]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;