12 October, 2008

Delayed Block Cleanout -- through Instance Restart

Continuing my previous post on Delayed Block Cleanout, in this post, I show how a database shutdown doesn't guarantee that all the modified blocks in a table are "cleaned out" before the shutdown.

I run a large update and commit it. Next, I shutdown the database. The first query against the table after the subsequent restart still has to examine modified blocks, confirm if the updates to rows in the block have been committed and then execute a cleanout of these modified blocks. Thus, we see "consistent gets - examination" and "cleanout%" statistics being incremented for the first query after the restart :


SQL> select blocks,num_rows from user_tables where table_name = upper('test_d_b_c');

BLOCKS NUM_ROWS
---------- ----------
47069 3242752

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 47,513
consistent gets - examination 207
db block changes 0
db block gets 0
redo entries 0
redo size 0
undo change vector size 0
SQL>
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,4,11) WHERE MOD(COL_4,10) > 3 ;

1945344 rows updated.

SQL> COMMIT;

Commit complete.

SQL>
SQL> REM We will now shutdown and restart the database
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 2023688 bytes
Variable Size 264244984 bytes
Database Buffers 461373440 bytes
Redo Buffers 6361088 bytes
Database mounted.
Database opened.
SQL>
SQL> connect hemant/hemant
Connected.
SQL> REM Now let's query the table
SQL> REM Will the first execution 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 94,910
consistent gets - examination 47,367
db block changes 46,973
db block gets 3
redo entries 46,971
redo size 3,383,012
undo change vector size 204
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 141,993
consistent gets - examination 47,367
db block changes 46,973
db block gets 3
redo entries 46,971
redo size 3,383,012
undo change vector size 204


It is the first full table scan query that shows "consistent gets - examination" and "cleanout%" statistics. It did 46,969 examinations and cleanouts and generated 3.383MB of redo (for the correspondng 46,971 redo entries).

18 comments:

Marcio Nobre said...

Hi there
If the query start at t1, during the execution of the query, if oracle finds out the
the block is modified after t1, it reads the before image from undo.


Does oracle only check buffer cache to see if the block is modified ?

Whatif someone did the modifications and all dirty blocks are written to disk.
How does oracle understand if the block is modified after T1?

Hemant K Chitale said...

When Oracle reads a block, it reads the header and the ITL (interested transactions list). The header contains the block level SCN. The ITL identifies rows and SCNs that have been updated.
Oracle uses this information to "understand" that the block and the particular row it is interested in have been updated since t1. The ITL entry also specifies which undo segment and undo entries it should read to get the pre-update image of that particular row.

Marcio Nobre said...

Thanks for the info;
" When Oracle reads a block, it reads the header and the ITL (interested transactions list)."


So,inorder to understand wheter block is modified or not.
It doesnt matter wheter block is on disk or in cache, right?

Marcio Nobre said...

"The header contains the block level SCN"

Assume I insert a row into block1.
It`s block level SCN is 400.
Later I insert another row into block1.
Now,its block level SCN is 500.

There are two different scn for two diffrent rows in the same block.
How does oracle undertand which row has which scn?

Hemant K Chitale said...

There's also a row-level SCN.

Scofield said...

Mr Hemant;

Suppose I have dirty blocks,I havent committed yet,checkpoint occured these dirty blocks written to datafiles.
Now these dirty blocks become clean but itl entry of these blocks are not clean.
Is that right?

Hemant K Chitale said...

Do NOT confuse "dirty blocks" with "delayed block cleanout". A "dirty block" is one that has been modified. It is written by DBWR.
Whether a COMMIT has been issued and the ITL entry cleaned out is where "block cleanout" or "delayed block cleanout" occurs. "delayed" is when the cleanout has to be done by a session / query / DML other than the one that did the initial DML.

Scofield said...

Mr Hemant;

What I meant to say is:

Suppose I issue insert, now block1 is dirty and itl entry of block1 is open.

After dbwr writes block1 to disk, block1 becomes clean in cache.
How about the ITL entry in block1?
I havent committed yet,but it has been written to disk.

Even the block is cleaned from the cache, the ITL entry is not cleared.The block is empty in cache but itl entry is open.

Is that correct?

Hemant K Chitale said...

If you had committed, the commit would have done the cleanout. A commit will write a limited a number of blocks (ie signal DBWR to write them).

If you had the unfortunate experience of a single block transaction being written out by DBWR (probably because the database cache was under pressure to release buffers), then, yes, the next access would do the cleanout.

Scofield said...

Mr Hemant;
Assume pctfree of the blocks is 20.
I now that I can insert upto %80 of the blocks.

How about ITL entries? If the block is full, where does the itl transaction slots stored?

Hemant K Chitale said...

The initial allocation for ITLs is specified by MINTRANS -- the number of ITL entries to start with.
If there are multiple concurrent DMLs in the same block and there is free space, the number of ITL entries can grow.
PCTFREE just determines the minimum free space.
However, that doesn't stop Oracle from using more space if the block has more free space (but, then again, consider that to have many concurrent transactions in the block you'd have to have many existant rows as well !)

Scofield said...

Mr Hemant;
Assume pctfree is %20.
Initrans is 1.
MAxtrans is 255.

When the block is %80 full, Does it mean that there will be only
1 ITL entry, therefore there will be only one transaction?
In this case,only one row can be modified at a time.
All other have to wait...

Hemant K Chitale said...

No, not necessarily. If you can explain why you think so, definitively, post it on your own blog or on forums.oracle.com

Scofield said...

Mr Hemant;
How come oracle allocate more itl slots then?
Block is already %80 full and %20 is alloted for pctfree.

Hemant K Chitale said...

Why not ? What has PCTFREE to do with ITL entries ?
The number of ITL entries can grow as required (10.2 automatically imposes a limit of 255, unlike earlier versions MAXTRANS limit). That doesn't mean that it can only use PCTFREE space. There's nothing "magical" about the space.

Unknown said...

Hi Hemant,

Any idea how to force the block cleanout?
Did you make any observations regarding 11.2?

Thanks,

Nenad

Anonymous said...

Hi Hemant,

Any idea on how to:
- force the block cleanout?
- query the number of blocks, that are subject to the cleanout?
Any observations with 11.2 on this topic?

Regards,
Nenad

Hemant K Chitale said...

Nenad,
Do a FullTableScan (without using Parallel Query) on the table to force block cleanout.

I haven't tested 11.2 but delayed block cleanout would still be present on 11.2 -- after all, it is a result of a performance requirement for COMMITs !

Hemant K Chitale