Re: DWBR and dirty buffers

From: Radoslav Rusinov <radoslav.rusinov_at_gmail.com>
Date: 24 Feb 2005 12:08:00 -0800
Message-ID: <58aa0927.0502241208.1cb4e64f@posting.google.com>

wagen123_at_yahoo.com wrote in message news:<1109212005.567929.44620_at_g14g2000cwa.googlegroups.com>...
> posting again...
>
> It appears that dirty buffers for a specific object exists in the
> buffer cache even after
> - switching through all online redo logfiles (twice)
> - alter system checkpoint
> - select * from t1
>
> is done. See example...
>
>
> Oracle9i 9.2.0.6 (64 bit)
> Solaris 2.9
> 8k block size
> fast_start_mttr_target = 15
> db_cache_size= 218103808
> 3 online redo logfiles (100 MB each)
> db in archivelog mode
> only the following session active (no other users).
>
>
> 1. create table t1 (c1 number, c2 varchar2(50), c3 date);
> -- objd = 6708 (from dba_objects)
> 2. insert 500,000 rows, commit after every 1000 rows.
> 3. SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=6708;
> DIRTYBLKS
> ----------
> 1303
> 4. alter system switch logfile;
> -- executed 6 times consecutively to force DBWR to flush the dirty
> buffers
> -- culd have also done alter system checkpoint;
> 5. SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=6708;
> DIRTYBLKS
> ----------
> 0
>
>
> SO FAR, SO GOOD.
> 6. update t1 set c3=sysdate;
> 7. commit;
> 8. SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=6708;
> DIRTYBLKS
> ----------
> 3449
> 9. alter system checkpoint;
> -- to force the DBWR to flush the dirty buffers from the cache to disk
> 10. SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=6708;
>
>
> DIRTYBLKS
> ----------
> 2410
> 11. select * from t1;
> -- due to delayed block cleanout (?)
> 12. SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=6708;
> DIRTYBLKS
> ----------
> 3239
> 13. alter system checkpoint;
> 14. alter system switch logfile; (6 times)
> 14. SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=6708;
> DIRTYBLKS
> ----------
> 2410
>
>
> Why are the dirty blocks not flushed to disk?
>
>
> Thanks for any pointers.

This is very interesting issue.
I think that this behavior is related to "delayed block clean out" or "commit clean out".
I will try to explain my opinion about results that you reach with above queries.
To do that I was using one of the best document that explains the "delayed block cleanout":
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=40689.1 I am using the explanation of the "block cleanout" from Tom's Kyte book "Expert-One-on-One Oracle" and results from queries that I have run on my test database.
The Metalink Note says:

---

2. DELAYED BLOCK CLEANOUT:

---

 

This is best illustrated with an example: Consider a transaction that updates
a million row table. This obviously visits a large number of database blocks
to make the change to the data. When the user commits the transaction Oracle
does NOT go back and revisit these blocks to make the change permanent. It is
left for the next transaction that visits any block affected by the update to
'tidy up' the block (hence the term 'delayed block cleanout').   

Whenever Oracle changes a database block (index, table, cluster) it stores
a pointer in the header of the data block which identifies the rollback
segment used to hold the rollback information for the changes made by the
transaction. (This is required if the user later elects to not commit the
changes and wishes to 'undo' the changes made.)  

Upon commit, the database simply marks the relevant rollback segment header
entry as committed. Now, when one of the changed blocks is revisited Oracle
examines the header of the data block which indicates that it has been changed
at some point. The database needs to confirm whether the change has been
committed or whether it is currently uncommitted. To do this, Oracle determines
the rollback segment used for the previous transaction (from the block's
header) and then determines whether the rollback header indicates whether it
has been committed or not.  

If it is found that the block is committed then the header of the data block
is updated so that subsequent accesses to the block do not incur this processing.  

This behaviour is illustrated in a very simplified way below. Here we walk
through the stages involved in updating a data block.  

 STAGE 1 - No changes made  

 Description: This is the starting point. At the top of the data block we

              have an area used to link active transactions to a rollback

              segment (the 'tx' part), and the rollback segment header has a

              table that stores information upon all the latest transactions

              that have used that rollback segment.  

              In our example, we have two active transaction slots (01 and 02)

              and the next free slot is slot 03. (Since we are free to
              overwrite committed transactions.) 
 
      Data Block 500                   Rollback Segment Header 5 
      +----+--------------+           
+----------------------+---------+
      | tx | None         |            | transaction entry 01 |ACTIVE 
 |
      +----+--------------+            | transaction entry 02 |ACTIVE 
 |
      | row 1             |            | transaction entry 03
|COMMITTED|
      | row 2             |            | transaction entry 04
|COMMITTED|
      | ... ..            |            |     ...     ...   .. |  ...  
 |
      | row n             |            | transaction entry nn
|COMMITTED|
      +------------------+            
+--------------------------------+
 

 STAGE 2 - Row 2 is updated  

 Description: We have now updated row 2 of block 500. Note that the data block

              header is updated to point to the rollback segment 5, transaction

              slot 3 (5.3) and that it is marked uncommitted (Active).  

      Data Block 500                   Rollback Segment Header 5 
      +----+--------------+           
+----------------------+---------+
      | tx |5.3uncommitted|-----+      | transaction entry 01 |ACTIVE 
 |
      +----+--------------+     |      | transaction entry 02 |ACTIVE 
 |
      | row 1             |     +----->| transaction entry 03 |ACTIVE 
 |
      | row 2 *changed*   |            | transaction entry 04
|COMMITTED|
      | ... ..            |            |     ...     ...   .. |  ...  
 |
      | row n             |            | transaction entry nn
|COMMITTED|
      +------------------+            
+--------------------------------+
 

 STAGE 3 - The user issues a commit  

 Description: Next the user hits commit. Note that all that this does is it

              updates the rollback segment header's corresponding transaction

              slot as committed. It does *nothing* to the data block.  

      Data Block 500                   Rollback Segment Header 5 
      +----+--------------+           
+----------------------+---------+
      | tx |5.3uncommitted|-----+      | transaction entry 01 |ACTIVE 
 |
      +----+--------------+     |      | transaction entry 02 |ACTIVE 
 |
      | row 1             |     +----->| transaction entry 03
|COMMITTED|
      | row 2 *changed*   |            | transaction entry 04
|COMMITTED|
      | ... ..            |            |     ...     ...   .. |  ...  
 |
      | row n             |            | transaction entry nn
|COMMITTED|
      +------------------+            
+--------------------------------+
 

 STAGE 4 - Another user selects data block 500  

 Description: Some time later another user (or the same user) revisits data

              block 500. We can see that there is an uncommitted change in the

              data block according to the data block's header.  

              Oracle then uses the data block header to look up the  
              corresponding rollback segment transaction table slot,
sees that
              it has been committed, and changes data block 500 to
reflect the
              true state of the datablock. (i.e. it performs delayed

cleanout).  

      Data Block 500                   Rollback Segment Header 5 
      +----+--------------+           
+----------------------+---------+
      | tx | None         |            | transaction entry 01 |ACTIVE 
 |
      +----+--------------+            | transaction entry 02 |ACTIVE 
 |
      | row 1             |            | transaction entry 03
|COMMITTED|
      | row 2             |            | transaction entry 04
|COMMITTED|
      | ... ..            |            |     ...     ...   .. |  ...  
 |
      | row n             |            | transaction entry nn
|COMMITTED|
      +------------------+            
+--------------------------------+

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

What information is stored in column "dirty" from V$BH. If some block is "dirty" this column have value "yes". That is all blocks from database buffer cache, which are modified and should be written to database files. When LGWR is flushing redo log buffer in online redo log files it guarantees that the database will be able to recover itself. All needed information is stored in redo logs. The modified data blocks are flushed to database files from DBWn and we usually do not have control over this action. DBWn is flushing "dirty" blocks on every log switch - this is the behavior for which we have read in manuals. On every log switch the flushing of "dirty" blocks is issued. It can be initiated with forcing a checkpoint. So, we know that all "dirty" blocks are flushed to database files at least when log switch occurs. The most important action here is not that the "dirty" blocks are flushed but that the redo log buffer is flushed to online redo log file, so the database can recover itself at any time. Any other information which user can need can be delivered from the database buffer cache, database files and rollback segments. When some user performs some big transaction there will be a lot of "dirty" blocks. These blocks are not "cleaned-out" from this transaction. When commit is issued all of these blocks should be revisited and cleaned out and this can be done fast if the transaction is small. Otherwise, not all of these blocks are "cleaned out". In time of "COMMIT" the most important thing for Oracle is that the redo log buffer to be flushed in redo log files, not "dirty" blocks to be flushed. May be DBWn is flushed already some part of modified blocks but not all of them. As we see from Metalink note - the information for the status of the "not cleaned out" blocks can be received from rollback segments. This "delayed clean out" helps to Oracle to do "Fast-Commit".
Some quotation from "Expert One-on-One Oracle"

---

"cleaning out" action generates redo and causes the block to become "dirty" if it wasn't already. What this means is that a simple SELECT may generate redo, and may cause lots of blocks to be written to disk with the next checkpoint. Under most normal circumstances however, this will not happen.
…………….
one of the steps of COMMIT-time processing is to revisit our blocks if they are still in the SGA, if they are accessible (no one else is modifying them), and then clean them out. This activity is known as "commit clean out". Our transaction cleans out the block enough so that a SELECT (read) will not have to clean it out. Only an UPDATE of this block would truly clean out our residual transaction information, and since this is already generating redo, the cleanout is not noticeable.
………….
So, as long as the number of blocks we modify does not exceed 10 percent of the number of the blocks in the cache and our blocks are still in the cache and available to us, Oracle will clean them out upon COMMIT. Otherwise, it just skips them (does not clean them out). …………….

---

I make few tests on my local database:
-- 1. create view to watch generated redo for current session CREATE OR REPLACE VIEW redo_size
AS
SELECT VALUE FROM v$mystat,v$statname
WHERE v$mystat.statistic#=v$statname.statistic# AND v$statname.NAME='redo size';

• 2. create table T1 CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(50), c3 DATE);
  • 3. filling the table with 608 150 rows from DBA_OBJECTS
• 4. View generated redo SQL> SELECT * FROM redo_size;

     VALUE

---

  30854192
Executed in 0.015 seconds
-- 5. view dirty blocks for this table
SQL> SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=39409;

 DIRTYBLKS

---

      6925
Executed in 0.078 seconds

• 6. Forcing checkpoint SQL> alter system checkpoint;

System altered
Executed in 2.266 seconds

• 7. view dirty blocks SQL> SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=39409;

 DIRTYBLKS

---

      3400
Executed in 0.078 seconds

---

I force the checkpoint, so all dirty blocks should be flushed and result should be zero.
The suggestion is that the dirty blocks for this table are these blocks that are still not "cleaned out"

---

• 8. update SQL> update t1 set c3=sysdate;

608150 rows updated
Executed in 40.734 seconds

SQL> COMMIT; Commit complete

Executed in 0.016 seconds

• 9. dirty SQL> SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and objd=39409;

 DIRTYBLKS

---

       726
Executed in 0.062 seconds

---

With update all block should be cleaned out but after commit of UPDATE statement we can see that there are still dirty blocks. The suggestion is that these blocks are different from previous ones. All older are cleaned out and redo but there are some other new blocks that are not "cleaned out".
After some tests I have found that the operation that's makes this operation slow is the UPDATE itself, not the "cleaning out", which is take a little percent of the whole time, may be few seconds.

---

• 10. check generated redo SQL> SELECT * FROM redo_size;

     VALUE

---

 181664136
Executed in 0 seconds

• 11. full access to table SQL> SELECT /*+ FULL */ COUNT(*) FROM t1;

  COUNT(*)

---

    608150
Executed in 7.25 seconds

---

Suggestion: The first access to all blocks for this table makes them to be "cleaned out" and because that this operation takes so long time

---

-- 12. check redo
SQL> SELECT * FROM redo_size;

     VALUE
----------
 182393676

---
As we can see there are generated lots of redo for this simple SELECT
statement. So, we can suggest that really Oracle is "cleaned out"
these blocks. But are they are "zero" right now.
------

-- 13. dirty
SQL> SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and
objd=39409;

 DIRTYBLKS
----------
      4745
Executed in 0.063 seconds

-------
But they are still other dirty blocks and the count is bigger than
right after the UPDATE statement.
-------

-- 14. start second SELECT
SQL> SELECT /*+ FULL */ COUNT(*) FROM t1;

  COUNT(*)
----------
    608150
Executed in 0.328 seconds

-- 15. check again redo after the second SELECT
SQL> SELECT * FROM redo_size;

     VALUE
----------
 182393676

---------
As we can see this time the redo size is not changed and full access
to table is very fast
Because I execute the same statement again I wanted be sure that it is
not so fast because the whole data is cached. After restarting of
database when there is no dirty blocks (that could be cleaned out) I
run count and it is executed for 0.843.
It seems that the "cleaning out" makes the first count to be so slow.
----------

-- 16. Lets try to force another checkpoint
SQL> alter system checkpoint;

System altered
Executed in 2.344 seconds

-- 17. and dirty
SQL> SELECT COUNT(*) DIRTYBLKS FROM v$bh WHERE dirty='Y' and
objd=39409;

 DIRTYBLKS
----------
         0
Executed in 0.063 seconds

-----
So, after whole reading and testing I am not absolutely sure what is
going on but I hope that's start some discussion about this.
-	Is it possible that these dirty blocks that reside in buffer cache
to be destined for rollback segments in UNDO tablespace. Because in
buffer cache are kept not only the data blocks but the rollback data
blocks too.
-	Is it the terms "dirty" block and "not cleaned out" block the same
and if not what is the difference
-	If they are different what exactly information is recorded in
"dirty" column of V$BH view
-	Why forcing checkpoint can flush dirty blocks only in "some" cases.
If we suggest that the dirty blocks (475 after the first and second
SELECT on the table) are not directly related to data blocks then
could we suggest that: Is the executing of "SELECT count(*) "
statements (that makes all related data blocks to be "cleaned out") is
the action that makes the following "force checkpoint" statement to
succeed with flushing of all dirty blocks. What exactly is the
difference between the first and the second "force checkpoint", which
makes them to flush all or partial of dirty blocks.
---------

If I am wrong with mixing of the terms "dirty" and "cleaned out"
blocks, please correct me. But results from test and sources related
to this issue provokes my suggestions.

Best Regards,
Radoslav Rusinov

Received on Thu Feb 24 2005 - 14:08:00 CST