Oracle DBA · Lesson 8B
Dirty Buffers Are Not the Commit Record
After Lesson 8A, a block can sit in the buffer cache as a dirty buffer. Dirty means modified in memory — not that the change is durable, and not that DBWR has written the datafile.
Watch on YouTube
Oracle DBA Lesson 8B — Dirty Buffers Are Not the Commit Record
AI Ops Pro
From Lesson 8A: the block is already in memory
Lesson 8A followed a block into the buffer cache. After DML, that same buffer can become dirty: the in-memory copy no longer matches the on-disk datafile image. Dirty means modified in the cache and not yet written to the datafile by DBWR. It does not name durability, and it does not mean the transaction committed.
Dirty ≠ committed
Keep these two clocks separate:
- A dirty buffer can exist for uncommitted changes. The modifying session works against the current image in memory.
- COMMIT does not wait for DBWR to flush that dirty buffer to the datafile. Returning from commit is not a claim that the datafile already has the new bytes.
Dirty answers “has this buffer changed since it was read from disk?” Commit answers “is this transaction durable?”
Commit durability rides on redo
When commit returns, Oracle has already protected the change through the redo path: LGWR has written the redo for that transaction — including the commit record — to the online redo log. That is the durability guarantee. Crash recovery can re-apply committed work from redo even if dirty buffers were never written by DBWR before the instance failed.
Mental model: commit record → LGWR → online redo log. Datafile writes are DBWR’s job on a different schedule (checkpoints, aging, space pressure). Do not fuse the two.
Consistent read still applies
Other sessions may still need an older version. While your dirty buffer holds the modifying session’s current image, a concurrent query at an earlier SCN can use consistent-read (CR) reconstruction from undo. Dirty does not mean “everyone sees the new row now.” Visibility still follows read consistency; durability follows redo.
A simple mental walkthrough
In one session, change a row and leave it uncommitted. Your next SELECT in that same session sees the dirty current image. Another session (or a CR snapshot) may still see the old value. After COMMIT, durability is on redo — the buffer can still be dirty until DBWR writes it. After a checkpoint or DBWR write, the datafile catches up; dirty clears for that buffer.
-- Session A: modify, do not commit yet
UPDATE hr.employees
SET salary = salary + 100
WHERE employee_id = 100;
-- Same session: current (dirty) image
SELECT employee_id, salary
FROM hr.employees
WHERE employee_id = 100;
-- COMMIT; -- durability via redo (LGWR), not via DBWR datafile write
-- Checkpoint / DBWR write later clears dirty for that bufferTeaching walkthrough only — use a lab schema you own. Never invent live salary numbers; read what your instance returns.
Inspect dirty in the cache (teaching peek)
V$BH (and underlying buffer headers) expose whether a cached buffer is dirty. Status classes such as current vs CR tell you which image you are looking at; a dirty flag means the current buffer has unwritten changes relative to the datafile. Use this as a concept check, not as a production monitoring script.
SELECT status, dirty, COUNT(*) AS buffers
FROM v$bh
GROUP BY status, dirty
ORDER BY status, dirty;Counts move constantly. Read them on your instance; do not treat a sample as a fixed “healthy” ratio.
Gotchas
- Dirty = modified in memory, not yet written to the datafile by DBWR.
- Dirty ≠ committed. Uncommitted DML can leave dirty buffers.
- Commit durability is redo (LGWR → online redo log), not a forced datafile flush.
- Other sessions may still see older CR images from undo while your session sees the dirty current buffer.
Quick quiz
Choose one answer, then submit. You’ll see the correct answer and a short why.
No comments:
Post a Comment