Interview Question 16 : Why LGWR writes before DBWR writes?

 

Why does LGWR write before DBWR in Oracle?

Short answer (core principle)

LGWR must write redo first to guarantee that every committed change can be recovered, even if dirty data blocks have not yet been written by DBWR.

This rule is called Write‑Ahead Logging (WAL) and it is non‑negotiable for any database that promises transactional durability.

LGWR (Log Writer): Writes redo entries from redo log buffer to online redo logs

DBWR (DB Writer): Writes dirty data blocks from buffer cache to datafiles

---

The problem Oracle must solve

Oracle must ensure ACID durability:

Once a user sees COMMIT successful, the change must survive:

• Instance crash
• Power failure
• OS crash
• Datafile not written yet

But Oracle uses:

• Deferred writes to datafiles (for performance)
• Memory caching of data blocks

So inevitably:

• Changed data blocks (dirty buffers) may sit in memory
• DBWR may write them minutes later
• Yet COMMIT must return immediately

This is where LGWR before DBWR becomes mandatory.

---

The WAL rule (the law of databases)

Redo describing a change must reach disk before the data blocks containing that change are allowed to be written.

Oracle enforces:

Redo on disk  ⇒  Data block may be written
Redo not on disk ⇒ Data block must NOT be written

This means:

• LGWR always precedes DBWR in the timeline of durability

---

Internal sequence (step by step)

1. User modifies data

UPDATE emp SET sal = 5000 WHERE empno = 7788;

Internally:

• Data block modified in buffer cache
• Undo generated in undo segment
• Redo generated for:
  • Data change
  • Undo change

💡 Redo goes to redo log buffer (SGA)

---

2. User issues COMMIT

On COMMIT:

• LGWR is signaled
• LGWR writes:
  • All redo entries up to the commit SCN
  • Including the COMMIT record itself

Only after redo is safely written to disk:

Commit complete

✅ Durability now guaranteed
❌ Data block may still be only in memory

---

3. DBWR writes later (asynchronously)

DBWR writes dirty blocks due to:

• Checkpoints
• Buffer pressure
• Tablespace going offline
• Clean shutdown

Before DBWR can write a block:

• Oracle checks: Has corresponding redo been flushed?
• If not → LGWR is forced first

This is called a redo write barrier.

---

Why Oracle does NOT let DBWR write first

Scenario if DBWR wrote first (dangerous)

1. DBWR writes dirty block to datafile
2. LGWR has not flushed redo yet
3. Instance crashes

After crash:

• Datafile shows new data
• Redo logs do not contain the change

➡️ Database corruption ➡️ Inconsistent datafiles ➡️ Recovery impossible

💥 This violates atomicity and durability

---

How Oracle enforces LGWR-first internally

1. Redo SCN tracking

Each dirty buffer tracks:

• Lowest redo SCN required
• DBWR checks redo availability before writing

2. Checkpoint mechanism

During checkpoint:

• CKPT signals:
  • LGWR to flush redo up to checkpoint SCN
  • DBWR to write all buffers <= that SCN

Sequence:

LGWR → Redo

DBWR → Data blocks

CKPT → Datafile headers

---

Crash recovery depends on this ordering

After instance crash

Oracle performs:

1. Roll Forward
   • Reads redo logs
   • Reapplies changes to data blocks
2. Roll Back
   • Uses undo for uncommitted transactions

This only works if:

• Redo always exists on disk
• Even if data blocks were never written

Thus:

Redo is the source of truth after a crash, not datafiles

---

Performance benefit (secondary but important)

LGWR:

• Writes sequentially
• Very fast
• Small I/O

DBWR:

• Writes random blocks
• Expensive I/O

Letting LGWR commit fast while DBWR is deferred:

• Improves throughput
• Reduces IO contention
• Scales better on busy systems

---

Analogy (sticky note vs filing cabinet)

• Redo log = sticky note journal
• Datafiles = filing cabinet

Rule:

Write the sticky note first, then reorganize the cabinet later

If the office burns:

• Sticky notes let you reconstruct
• Cabinets alone mean lost intent

---

Key guarantees achieved

Guarantee	Achieved by LGWR-before-DBWR
Durability	✅
Atomicity	✅
Crash recovery	✅
Fast commits	✅
Deferred writes	✅

---

One-line takeaway (exam / interview gold)

LGWR writes before DBWR because redo must be safely on disk before any data block changes can be permanently stored, ensuring crash recovery and transactional durability under Oracle’s Write-Ahead Logging protocol.

Interview Question 15 : How INSERT and DELETE Statements Are Executed in Oracle ?

 

1️⃣ How an INSERT Statement Is Executed in Oracle

Example

INSERT INTO emp (empno, ename, sal)

VALUES (101, 'ANURAG', 50000);

---

Step‑by‑Step Execution Flow

1️⃣ Parsing Phase

• Syntax and object validation
• Privilege check (INSERT on table)
• Hard or soft parse
• Execution plan preparation

✅ Uses Shared Pool (SGA)

---

2️⃣ Space Identification

Oracle determines:

• Which data block has free space
• Uses freelists / bitmap / ASSM
• No row locking needed (row doesn’t exist yet)

---

3️⃣ Undo Generation (Yes, Even for INSERT)

Oracle creates undo for the insert:

• Contains information to delete the inserted row if rollback happens

📌 Undo is mandatory for:

• Rollback
• Read consistency
• Flashback

---

4️⃣ Redo Generation

Redo is generated for:

• Undo records
• Inserted row data

Redo is placed in:

• Redo Log Buffer
• Later flushed to redo logs by LGWR

---

5️⃣ Data Written to Buffer Cache

• New row inserted in memory
• Data block marked DIRTY
• Not yet written to disk

---

6️⃣ Commit or Rollback

✅ COMMIT

• LGWR flushes redo
• Transaction becomes permanent
• Space becomes visible to others

❌ ROLLBACK

• Undo used to remove inserted row
• Block returns to original state

---

Key INSERT Characteristics

Aspect	Behavior
Locks	Only table TM lock (RX)
Row locks	❌ None
Undo generated	✅ Yes
Redo generated	✅ Yes
Disk write	❌ Deferred

---

2️⃣ How a DELETE Statement Is Executed in Oracle

Example

DELETE FROM emp WHERE empno = 100;

---

Step‑by‑Step Execution Flow

1️⃣ Parsing & Optimization

Same as UPDATE:

• Parse SQL
• Optimizer selects index or scan

---

2️⃣ Row Identification

• Oracle finds target ROWIDs
• Rows are identified, not yet removed

---

3️⃣ Acquire Locks

• Row‑level exclusive (RX) locks on rows
• TM lock on table

✅ Prevents concurrent UPDATE/DELETE on same rows
✅ SELECTs are NOT blocked

---

4️⃣ Undo Generation (BEFORE DELETE)

Undo stores:

• Complete old row image
• Required for:
  • Rollback
  • Consistent reads
  • Flashback

📌 DELETE generates a lot of undo

---

5️⃣ Redo Generation

Redo recorded for:

• Undo
• Delete operation

---

6️⃣ Row Is Marked Deleted (Not Removed)

Important concept:

DELETE does not physically remove the row immediately

Instead:

• Row is logically removed
• Space reusable later
• Cleaned by subsequent inserts or segment shrink

✅ This enables rollback

---

7️⃣ Commit or Rollback

✅ COMMIT

• Redo flushed
• Locks released
• Row permanently deleted

❌ ROLLBACK

• Row restored completely from undo

---

Key DELETE Characteristics

Aspect	Behavior
Row locks	✅ Yes
Undo volume	🔥 High
Redo	✅ Yes
Space freed	❌ Logical first
SELECT blocked	❌ No

---

3️⃣ UPDATE vs INSERT vs DELETE (Internal Comparison)

Feature	INSERT	UPDATE	DELETE
Row exists before	❌	✅	✅
Row‑level lock	❌	✅	✅
Undo usage	Medium	Medium	High
Redo generation	✅	✅	✅
Affects indexes	✅	✅	✅
Can rollback	✅	✅	✅

---

4️⃣ Why DELETE Is Slower Than TRUNCATE (Interview Favorite)

DELETE	TRUNCATE
DML	DDL
Row‑by‑row	Metadata only
Undo generated	✅
Redo	✅
Rollback	✅
Triggers fire	✅

🔑 TRUNCATE is fast because it doesn’t generate undo for each row

---

5️⃣ RAC‑Specific Behavior (Bonus DBA Knowledge)

In Oracle RAC:

• Row locks are tracked globally via GES
• Cache Fusion ensures consistency across instances
• UPDATE/DELETE on same row from two instances:
  • One waits for the other

✅ No data corruption
✅ Distributed locking handled automatically

---

6️⃣ Common Interview Traps (Correct Answers)

Q: Does DELETE free disk space immediately?

❌ No
✅ Row is logically removed; space reused later

---

Q: Does INSERT generate undo?

✅ Yes — for rollback

---

Q: Which DML generates most undo?

✅ DELETE

---

Q: Can SELECT run while DELETE is happening?

✅ Yes (read consistency via undo)

---

Q: When is data written to disk?

✅ Later by DBWR, not during DML

---

7️⃣ One‑Line Interview Answers ⭐

• INSERT:

  Adds new rows by generating undo and redo and modifying memory blocks, making changes permanent only after commit.

• DELETE:

  Logically removes rows by locking them, generating undo and redo, and permanently deleting them upon commit.

• UPDATE (recap):

  Modifies existing rows by generating undo and redo while maintaining read consistency.

---

✅ Final DBA Mental Model

DML Statement
   ↓
Parse & Optimize
   ↓
Row identification
   ↓
Locks
   ↓
Undo
   ↓
Redo
   ↓
Buffer Cache change
   ↓
COMMIT / ROLLBACK

Interview Question 14 : Can you explain how update statement is executed in oracle database ?

 

How an UPDATE Statement Is Executed in Oracle Database

High‑Level Overview (One‑Line)

When an UPDATE statement is issued, Oracle parses the SQL, locates the target rows, locks them, creates undo, generates redo, modifies data in memory, and finally commits or rolls back the changes.

---

Example Statement

UPDATE emp

SET salary = salary + 1000

WHERE empno = 100;

---

Step‑by‑Step Execution Flow

---

1️⃣ SQL Parsing Phase

a) Syntax & Semantic Check

• Oracle verifies:
  • SQL syntax
  • Table and column existence
  • User privileges (UPDATE privilege on table)

b) Hard Parse or Soft Parse

• Soft parse: Execution plan already in shared pool ✅
• Hard parse: Plan must be generated ❌ (CPU expensive)

📌 Uses SGA → Shared Pool

---

2️⃣ Optimizer Chooses Execution Plan

Oracle Optimizer decides:

• Full table scan vs index access
• Join order (if joins exist)
• Row filtering strategy

for :

WHERE empno = 100

✅ Optimizer will usually choose an index unique scan on primary key

---

3️⃣ Row Source Generation

• Oracle identifies ROWIDs of rows to be updated
• Row filtering happens here

✅ No actual data change yet

---

4️⃣ Acquire Required Locks

Row‑Level Locks (Important)

• Oracle automatically places:
  • Row Exclusive (RX) lock on affected rows
  • Table‑level TM lock (Row Share mode)

✅ Locks prevent other sessions from modifying the same rows
✅ Readers are NOT blocked (Oracle multi‑version concurrency control)

📌 Locking happens BEFORE data change

---

5️⃣ Undo Data Creation

Before modifying data, Oracle:

• Copies old version of the row into an Undo segment
• Registers undo information for rollback and read consistency

📌 Undo is generated first, not redo.

✅ Enables:

• Rollback
• Consistent reads for other queries

---

6️⃣ Redo Generation

Oracle generates redo entries describing:

• Undo creation
• New data changes

Redo is written:

• First to Redo Log Buffer (SGA)
• Later flushed to disk by LGWR

✅ Ensures durability and crash recovery

---

7️⃣ Data Block Changes in Buffer Cache

• Data blocks are:
  • Read into DB Buffer Cache (if not already present)
  • Modified in memory only
• Blocks are marked DIRTY

📌 Data is NOT immediately written to disk

---

8️⃣ Transaction Remains Open

At this point:

• UPDATE is executed
• Locks are held
• Undo and redo exist
• Data blocks are dirty in memory

❗ Changes are NOT permanent yet

---

9️⃣ Commit or Rollback

---

✅ If COMMIT Is Issued

1. LGWR writes redo to disk
2. Commit SCN generated
3. Locks released
4. Undo marked reusable
5. Commit acknowledged to user

✅ Data becomes permanent

---

❌ If ROLLBACK Is Issued

1. Oracle uses undo data
2. Restores old row versions
3. Locks released
4. Redo written for rollback

✅ Database returns to original state

---

10️⃣ Background Processes Involved

Process	Role
Server Process	Executes UPDATE
DBWR	Writes dirty blocks later
LGWR	Writes redo on commit
CKPT	Checkpoint coordination
SMON	Crash recovery (if needed)

---

Key Oracle Concepts During UPDATE

---

Read Consistency (MVCC)

• Other sessions querying the same row:
  • See old data until commit
• Achieved using Undo

✅ Readers are not blocked by writers

---

Concurrency Behavior

Operation	Allowed
Concurrent SELECT	✅
Concurrent UPDATE same row	❌ (waits)
UPDATE different rows	✅

---

Isolation Levels

• Default: READ COMMITTED
• Oracle allows:
  • READ COMMITTED
  • SERIALIZABLE

Isolation level affects:

• Consistent read versions
• Undo usage

---

What Happens on Disk?

Area	Written When
Datafiles	Later by DBWR
Redo Logs	On commit
Undo Tablespace	Immediately logged
TEMP	Not used (normally)

---

Common Interview Questions (With Answers)

Q: Does UPDATE write data directly to disk?

❌ No
✅ Modifies memory blocks first

---

Q: Is redo generated for UPDATE?

✅ Yes (for both undo and redo)

---

Q: Why is undo generated before redo?

✅ To ensure rollback and read consistency

---

Q: Are SELECTs blocked by UPDATE?

❌ No
✅ Oracle uses MVCC

---

Q: When is commit actually safe?

✅ When LGWR writes redo to disk

---

Simple Real‑Life Analogy

🏦 Bank Transaction

• Undo = Previous balance image
• Redo = Transaction log
• Buffer cache = Working counter
• Datafile = Bank vault
• Commit = Receipt printed

---

One‑Line Interview Answer ⭐

Oracle executes an UPDATE by parsing the SQL, locating target rows, locking them, generating undo and redo, modifying data in the buffer cache, and making the changes permanent only after a commit.