CR block generation - Index & Table blocks --> Insert statements.

I came across a very strange scenario.. and this can be reproducible. I am not sure whether it is a bug or some other mechanism kicking in but could not come to a valid conclusion as to why a CR block fabrication is even required in this case.. I have clearly ruled out most others that can cause this...

During inserts its very common to see the CR block generation for BMB blocks during normal operations, CR block generation for selects/updates/deletes can be easily explained but for inserts it doesn't appear good...

the extent to which oracle creates CR blocks varies significantly and depends on whether the checkpoint is basically incremental or full (during log switches or alter system checkpoint), it can generate 1000s of CR blocks or sometimes only 4 or 5 (also depends on number of dirty blocks in the buffer cache).

during this process (when the checkpoint is happening).. the sessions do wait on buffer busy waits and write complete waits... which is a valid thing.. Once the buffer is marked as clean and multiple sessions trying to modify the a block at the same time (again the block has to be marked dirty which doesn't happen immediately after the block is modified and happens precisely post the point rollback or commit or transactions fails is issued) should come up as buffer busy waits and comes up precisely as expected.

but I cannot come to a conclusion what warrants a the CR block fabrication during this time, and again the side effect of this is CBC latches...

if we check the touch counts... touch counts are either 0 or 1 or 2 for state 3 buffers..
OBJECT_NAME DBARFIL DBABLK STATE TCH CR_SCN_BAS
--------------- ---------- ---------- ---------- ---------- ----------

NAME_IDX 7     116634       3  1   67535676
NAME_IDX 7     116670       3  2   67539464
NAME_IDX 7     116738       3  0   67532660
NAME_IDX 7     116838       3  0   67539676
NAME_IDX 7     116896       3  1   67536400
NAME_IDX 7     116918       3  0   67534615
NAME_IDX 7     116938       3  0   67538423
NAME_IDX 7     116955       3  0   67539246

Insert (basically DML) works only XCUR or state 1 blocks...

 proper sizing of log files, buffer cache, extent sizes etc doesn't even help anything...

One suspicion can be index leaf block splits or branch block or root block splits... when making sure that an incremental or full checkpoint doesn't occur, the CR blocks are not generated at all indicating that block splits are not the reason for these CR blocks to be generated...

CR blocks are also generated for table blocks, but they are very small compared to the index blocks.... drop all the indexes we will notice the same behavior for the table blocks as well

can someone please tell me whether did anyone observe this phenomenon or explain this CR block fabrication for inserts and what purpose does it even serve post a checkpoint...

Thanks,
Vishnu

--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 03 2020 - 16:22:25 CET