Delayed Block Cleanout not working in 12c

   

This is likely not going to cause any ones application to fail and get you called in the middle of the night but it’s not really a good thing from my point of view.

Personally I think this is a bug, I’ve logged an SR on it and I’ll see what happens.  I will update this once I have something from support to update this with. 

For those of you who may not know what this process is I’ll briefly describe it then show how it’s not working in 12c. 

Delayed Block Cleanout (DBC) is a technique used by oracle to do work “later”.  I like to say the oracle works like your teenage son, it doesn’t do work until it has to. (I know when I was a teenaged son, I sure did that, and my own son appears to be carrying on the tradition.)  When we do DML (insert, update and delete) commands, oracle has to LOCK the rows being worked on.  

In the block header are things called ITL entries, Interested Transaction List.  Without going into a lot of detail here, that is the locking mechanism of oracle.  When you do a commit, we are told that we “release all our locks”.  

But not really.  

Again more detail then I want to go into, but the bottom line is these ITL entries may still be in the block after a large DML operation.  Like if you did an INSERT of a few million rows into a table.  What has happened up to 12 is that the next time the block is touched those ITL entries are cleaned out, even with a SELECT statement.  

But no more in 12.

OK, so now the proof.  I have a table called AHWM which is nothing more than a repeated copy of ALL_OBJECTS it has some 2 million rows.  The table has no indexes, so any query will do a full table scan.  The table is a different size in the two databases; in 12 the table ALL_OBJECT table is larger than in 11 so as a result it ends up being a bigger table in 12. That isn’t the issue here. 

To keep this short I’ll just give a summary of what happens.  I run a query on the table and using the Hotsos Test Harness capture stats on its run.  I then DELETE all the rows, and commit that DELETE.  I then run the same select on the table 3 more times. 

The scenarios you will see listed are:

ROWS – this is the SELECT with all the rows in the table, about 2 million rows
DEL1 – First run after all the rows were deleted from the table.
DEL2 and DEL3 are two more runs after row were deleted. 

Here are the relevant stats to the discussion (there are many others of course but these are the important ones). First a look at the consistent gets (LIOS) in 11:  

SCENARIO                       STATISTIC                                     VALUE

------------------------------ ---------------------------------------- ----------

DEL2                           consistent gets                               33540

DEL3                                                                         33540

ROWS                                                                         34231

DEL1                                                                         45198

The key point here is that the LIOs went UP after the delete, then down and stayed down.  Here is the same  stats in 12, notice they go up after the delete and stay up:

SCENARIO                       STATISTIC                                     VALUE

------------------------------ ---------------------------------------- ----------

ROWS                           consistent gets                               48916

DEL1                                                                         96036

DEL2                                                                         96036

DEL3                                                                         96036

Here is the “smoking gun”, these are stats with the name cleanout in them.  Notice what happens in 12, the first set of stats are from 11 the next from 12. 

SCENARIO                       STATISTIC                                     VALUE

------------------------------ ---------------------------------------- ----------

DEL2                           cleanouts only - consistent read gets             0

DEL3                                                                             0

ROWS                                                                             0

DEL1                                                                         11658

SCENARIO                       STATISTIC                                     VALUE

------------------------------ ---------------------------------------- ----------

ROWS                           cleanouts only - consistent read gets             0

DEL1                                                                         47919

DEL2                                                                         47919

DEL3                                                                         47919

In 12 the clean out activity has to happen every time.  Yes I’ve exaggerated the case here to illustrate point, but maybe not much really.  How many of us have this type of active going on?  Large DML operations then mostly selects going on for days after. I assume nearly all of you.  

BTW - Some simple tests did show that the cleanout does appear to happen only once if later DML is performed on the table.  I did an insert into the table and that did clean up about 1000 blocks in 12 and they stayed cleaned out.