A query that reads datablocks that have been updated by a large DML (INSERT/UPDATE/DELETE) may well incur "delayed block cleanout". The commit operation of the DML does not involve clearing the ITL entries in each modified datablock but only marking the transaction in the undo slot entry as "COMMITTED" (plus, possibly, a few blocks being updated). Therefore, the next query (whether from the same database session OR from another database session) to read the datablock has to verify the ITL entries against the transaction in the undo segment and then "clean out" the ITL entry and the row marker in each datablock. This process of updating the datablock also generates redo.
The presence of the "Penalty" of delayed block cleanout is visible in the "cleanout %" statistics and also in the "consistent gets - examination" count [which is a subset of the "consistent gets" count].
This has implications in that large batch jobs that update very many datablocks and are able to exeute a COMMIT quickly actually cause subsequent jobs or queries against the same table to suffer this overhead.
You may not see "delayed block cleanout" when only a small set of datablocks are updated by a transaction.
This set of SQL commands and outputs below show how the *first* query against the table undergoing DML suffers this Penalty and the "cleanout %" and "consistent gets - examination" and "redo size" statistics are incremented (ie incurred) for this query but not the next query on the same datablocks.
I use with a table with 3.24million rows and run UPDATE, DELETE and INSERT operations against the table and identify the changes to these statistics which indicate delayed block cleanout.
SQL> select blocks,num_rows from user_tables where table_name = upper('test_d_b_c');
BLOCKS NUM_ROWS
---------- ----------
47197 3242752
SQL> REM Get the number of consistent gets -- the query has been run once ahead to parse it
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
3242752
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 0
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 0
consistent changes 0
consistent gets 48,174
consistent gets - examination 498
db block changes 4
db block gets 3
redo entries 2
redo size 672
undo change vector size 204
SQL>
SQL> REM REM ########## Test for an UPDATE ################ REM REM ####################
SQL> REM Run an update on 60% (not all) of the rows
SQL> UPDATE TEST_D_B_C SET COL_1 = 'HKC_'||SUBSTR(COL_1,1,15) WHERE MOD(COL_4,10) > 3 ;
1945344 rows updated.
SQL> COMMIT;
Commit complete.
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 0
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 0
consistent changes 0
consistent gets 95,395
consistent gets - examination 503
db block changes 1,471,008
db block gets 732,521
redo entries 726,323
redo size 340,420,260
undo change vector size 141,809,380
SQL>
SQL>
SQL> REM Now let's query the table
SQL> REM The first execution will suffer delayed block cleanout
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
3242752
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 46,969
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 46,969
consistent changes 0
consistent gets 189,575
consistent gets - examination 47,472
db block changes 1,517,977
db block gets 732,521
redo entries 773,293
redo size 343,803,772
undo change vector size 141,809,380
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
3242752
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 46,969
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 46,969
consistent changes 0
consistent gets 236,786
consistent gets - examination 47,472
db block changes 1,517,977
db block gets 732,521
redo entries 773,293
redo size 343,803,772
undo change vector size 141,809,380
SQL>
SQL>
SQL> REM REM ########## Test for a DELETE ################ REM REM ####################
SQL> REM Test for a DELETE
SQL> DELETE TEST_D_B_C WHERE MOD(COL_4,5) > 3;
648640 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 46,969
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 46,969
consistent changes 0
consistent gets 284,451
consistent gets - examination 47,486
db block changes 2,870,813
db block gets 1,465,003
redo entries 1,458,221
redo size 586,670,260
undo change vector size 292,754,836
SQL>
SQL> REM Now let's query the table
SQL> REM The first execution will suffer delayed block cleanout
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
2594112
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 56,180
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 56,180
consistent changes 0
consistent gets 340,873
consistent gets - examination 56,697
db block changes 2,880,024
db block gets 1,465,003
redo entries 1,467,432
redo size 587,334,024
undo change vector size 292,754,836
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
2594112
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 56,180
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 56,180
consistent changes 0
consistent gets 388,084
consistent gets - examination 56,697
db block changes 2,880,024
db block gets 1,465,003
redo entries 1,467,432
redo size 587,334,024
undo change vector size 292,754,836
SQL>
SQL>
SQL> REM REM ########## Test for a INSERT ################ REM REM ####################
SQL> REM Test for an INSERT
SQL> INSERT INTO TEST_D_B_C SELECT * FROM TEST_D_B_C_BAK WHERE MOD(COL_4,5) > 3;
648640 rows created.
SQL> COMMIT;
Commit complete.
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 65,255
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 56,180
consistent changes 0
consistent gets 453,419
consistent gets - examination 65,810
db block changes 2,936,433
db block gets 1,532,712
redo entries 1,506,633
redo size 659,399,228
undo change vector size 295,378,592
SQL>
SQL> REM Now let's query the table
SQL> REM The first execution will suffer delayed block cleanout
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
3242752
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 72,839
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 63,764
consistent changes 0
consistent gets 508,214
consistent gets - examination 73,394
db block changes 2,944,017
db block gets 1,532,712
redo entries 1,514,217
redo size 659,945,320
undo change vector size 295,378,592
SQL>
SQL> SELECT COUNT(*) FROM TEST_D_B_C;
COUNT(*)
----------
3242752
SQL> @Report_select_Statistics
SQL> set echo off
Statistic Value
---------------------------------------------------------- ----------------
cleanout - number of ktugct calls 72,839
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 63,764
consistent changes 0
consistent gets 555,425
consistent gets - examination 73,394
db block changes 2,944,017
db block gets 1,532,712
redo entries 1,514,217
redo size 659,945,320
undo change vector size 295,378,592
SQL>
As you can see "cleanout - number of ktugct calls" and "cleanouts only - consistent read gets" are incremented as with "consistent gets - examination". [Remember that "consistent gets - examination" count is *included* in "consistent gets" count]. Also, "redo entries" and "redo size" are incremented. These occur for the first query against the modified datablocks.
NOTE : This table [TEST_D_B_C] does NOT have any indexes so "consistent gets - examination" does NOT reflect reads on index blocks [root blocks or otherwise]. {google might show a few references indicating the "consistent gets - examination" occurs for index blocks but that isn't the only case for this statistic}.
The above test was on 10.2.0.1 I have had similar results on 10.2.0.2
However, on 10.2.0.4 I have had a very surprisingly large number of "consistent gets - examination" {exceeding 1.9million} for the UPDATE itself [testing with both ASSM and MSSM], which I cannot explain yet. The "consistent gets - examination" count for the "delayed block cleanout" effect on the first query is still consistent. However, I haven't published the 10.2.0.4 numbers because of the 1.9million "consistent gets - examination" in the UPDATE. That is something I have to investigate.
