DWBR and dirty buffers

From: <wagen123_at_yahoo.com>
Date: 23 Feb 2005 18:26:45 -0800
Message-ID: <1109212005.567929.44620@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. Received on Wed Feb 23 2005 - 20:26:45 CST