从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;