Buffer states: How many consistent read (cr) clones in buffer cache, verified.

We all know to maintain the consistency of the buffers and its integrity oracle has to clone the current copy of the buffer to consistent read and change the latest one. There are different buffer states in x$bh column out of all our interest to this post is only to cr and xcur.

First lets create a table with one block and the issue updates

Our check would be how many cr clones it can create. Is there any maximum limit.

Insert some rows

Check the block filenum and the block address

Run this query what is our block status in the buffer cache.

select b.dbarfil, b.dbablk, b.class,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',
7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state,
cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
(select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b where dbarfil = &file_no and
dbablk = &block_no;

We have got a block with xcur state, now give few updates.

See the buffer states using above query.

You have got a CR block now. Few more update

Check again, you have got 2 CR blocks

Few more updates again,

Check again,

Now hold, Despite of giving 8-10 updates I still have 6 buffers (5 CR + 1 XCUR) buffers , this is due to the hidden parameter that controls the cr clones _db_block_max_cr_dba

Hidden parameter query:-

SELECT
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;

Now change the parameter to 8 and shut down the db and start up again.

Back to work, Update the rows (more than 8)

Now, you can see 8 rows only as "_db_block_max_cr_dba" is 8

Now issue query on test table and see

Observe the change in the SCN for xcur, and the top most row came from the undo due to the fact that one of the session need a consistent read record and has been read from undo.

Again hold the parameter "_db_block_max_cr_dba"= is not a hard limit, its just a soft limit, I tried with in the same session of updating same row, if you try opening fewer more session and updating different rows the limit of the 8 can go exceed without changing the parameter. No control over in real.

Hope this