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.