Oracle UPDATE Statement: Behind the Scenes

W3Buddy

February 15, 2026 · 15 min read · 0

Add W3Buddy as a preferred source on Google

Article Summary

Learn how Oracle executes UPDATE statements internally. Concise guide covering row location, undo segments, locks, constraints, and performance optimization for database administration interviews.

When you execute UPDATE employees SET salary = 60000 WHERE employee_id = 101;, Oracle performs a sophisticated series of operations involving undo segments, redo logs, locks, and constraints. Understanding this process is essential for database administrators, especially for performance tuning and troubleshooting.

In this guide, we’ll explore the complete execution flow of an UPDATE statement in Oracle Database.

The Complete UPDATE Statement Execution Flow

┌─────────────────────────────────────────────────────────────────┐
│                USER EXECUTES UPDATE STATEMENT                   │
│   UPDATE employees SET salary=60000 WHERE employee_id=101;      │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 1: SYNTAX CHECK                          │
│  • Validate UPDATE syntax                                       │
│  • Check SET clause format                                      |
│  • Verify WHERE clause structure                                │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Valid Syntax?]
                           │
                    Yes ───┼─── No → Error: ORA-00936
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 2: SEMANTIC CHECK                         │
│  • Does table exist?                                             │
│  • Do columns exist?                                             │
│  • Does user have UPDATE privilege?                              │
│  • Are data types compatible?                                    │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Objects Valid?]
                           │
                    Yes ───┼─── No → Error: ORA-00904/ORA-01031
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 3: SHARED POOL CHECK                      │
│  • Generate SQL hash                                             │
│  • Search Library Cache                                          │
│  • Hard parse vs Soft parse                                      │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Plan Found?]
                           │
            ┌──────────────┴──────────────┐
            │                             │
           Yes                           No
       (Soft Parse)                 (Hard Parse)
            │                             │
            └──────────────┬──────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 4: LOCATE ROWS (WHERE Clause)             │
│  • Execute WHERE clause predicate                                │
│  • Use index if available                                        │
│  • Identify ROWIDs of rows to update                             │
│  • May use Full Table Scan or Index Scan                         │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Rows Found?]
                           │
                    Yes ───┼─── No → 0 rows updated
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 5: ACQUIRE LOCKS                          │
│  • Get TX (Transaction) lock on each row                         │
│  • Lock table in Row Exclusive (RX) mode                         │
│  • Wait if rows locked by another session                        │
│  • Potential deadlock situation                                  │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 6: CREATE UNDO RECORDS                    │
│  • Save "before image" of each row                               │
│  • Store in UNDO tablespace                                      │
│  • Record original values for ROLLBACK                           │
│  • Critical for read consistency                                 │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 7: CHECK CONSTRAINTS                      │
│  • Validate NOT NULL constraints                                │
│  • Check CHECK constraints on new values                         │
│  • Verify UNIQUE constraints                                     │
│  • Validate FOREIGN KEY constraints                              │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                    [Constraints Valid?]
                           │
                    Yes ───┼─── No → Error, Rollback changes
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 8: GENERATE REDO RECORDS                  │
│  • Create redo change vectors                                    │
│  • Log both old and new values                                   │
│  • Write to Redo Log Buffer                                      │
│  • Ensure recoverability                                         │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 9: UPDATE IN BUFFER CACHE                 │
│  • Read data blocks into Buffer Cache (if not already)           │
│  • Modify column values in memory                                │
│  • Mark blocks as "dirty"                                        │
│  • Update block header with new SCN                              │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 10: UPDATE INDEXES                        │
│  • Update affected index entries                                 │
│  • Only indexes on modified columns                              │
│  • Delete old entry, insert new entry                            │
│  • Generate redo for index changes                               │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 11: FIRE TRIGGERS (if any)                │
│  • Execute BEFORE UPDATE triggers                                │
│  • Can modify :NEW values                                        │
│  • Execute AFTER UPDATE triggers                                 │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 12: RETURN SUCCESS                        │
│  • Return "N rows updated" message                               │
│  • Changes in memory, NOT on disk yet                            │
│  • Visible only to current session                               │
│  • Locks held until COMMIT/ROLLBACK                              │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
                ┌──────────┴──────────┐
                │                     │
             COMMIT              ROLLBACK
                │                     │
                ▼                     ▼
    ┌──────────────────┐  ┌──────────────────────┐
    │ Make Permanent   │  │ Restore Old Values   │
    │ Release Locks    │  │ Release Locks        │
    └──────────────────┘  └──────────────────────┘

Key Differences: UPDATE vs INSERT

Aspect	INSERT	UPDATE
Row Location	Must find free space	Must locate existing rows
Undo Data	Minimal (no before image)	Full before image stored
Index Impact	Add entries to ALL indexes	Only indexes on changed columns
Lock Acquisition	Lock new row location	Must lock existing rows (wait if locked)
Performance	Generally faster	Slower due to undo/row location
WHERE Clause	Not applicable	Critical for performance

Detailed Step-by-Step Breakdown

Step 1-3: Parse and Validate (Same as INSERT)

The initial parsing steps are identical to INSERT operations.

-- Syntax check
UPDATE employees SET salary = 60000 WHERE employee_id = 101;  -- Valid

UPDATE employees salary = 60000 WHERE employee_id = 101;  -- Invalid
-- ORA-00971: missing SET keyword

Step 4: Locate Rows to Update (Critical Step)

Oracle must first find which rows match the WHERE clause.

Access Path Selection:

WHERE Clause Analysis:
    │
    ▼
┌─────────────────────────────────────┐
│  Is there an index on WHERE column? │
└──────────────┬──────────────────────┘
               │
        ┌──────┴──────┐
        │             │
       Yes           No
        │             │
        ▼             ▼
 ┌─────────────┐  ┌──────────────┐
 │ INDEX SCAN  │  │ FULL TABLE   │
 │  (Fast)     │  │ SCAN (Slow)  │
 └─────────────┘  └──────────────┘

Example – Index Scan (Fast):

-- Assuming index on employee_id (primary key)
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 101;

-- Execution Plan:
-- UPDATE STATEMENT
--   UPDATE EMPLOYEES
--     TABLE ACCESS BY INDEX ROWID (EMPLOYEES)
--       INDEX UNIQUE SCAN (EMP_PK)
-- Fast: Directly locates the row

Example – Full Table Scan (Slow):

-- No index on last_name
UPDATE employees 
SET salary = 60000 
WHERE last_name = 'Smith';

-- Execution Plan:
-- UPDATE STATEMENT
--   UPDATE EMPLOYEES
--     TABLE ACCESS FULL (EMPLOYEES)
-- Slow: Scans entire table to find matching rows

Check Execution Plan:

SET LINESIZE 200
SET PAGESIZE 100

EXPLAIN PLAN FOR
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 101;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Monitor Row Location Performance:

SET LINESIZE 180
COLUMN sql_text FORMAT A60
COLUMN rows_processed FORMAT 999,999
COLUMN buffer_gets FORMAT 999,999,999

SELECT sql_id,
       sql_text,
       executions,
       rows_processed,
       buffer_gets,
       ROUND(buffer_gets/NULLIF(rows_processed,0)) gets_per_row
FROM v$sql
WHERE UPPER(sql_text) LIKE '%UPDATE EMPLOYEES%'
  AND sql_text NOT LIKE '%v$sql%'
ORDER BY buffer_gets DESC
FETCH FIRST 10 ROWS ONLY;

Step 5: Acquire Locks (More Complex than INSERT)

UPDATE must lock existing rows, which may already be locked by other sessions.

Lock Wait Scenario:

-- Session 1
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 101;
-- Lock acquired, transaction open

-- Session 2 (tries to update same row)
UPDATE employees 
SET department_id = 20 
WHERE employee_id = 101;
-- WAITS for Session 1 to COMMIT or ROLLBACK
-- May timeout if wait is too long

Check Locked Rows:

SET LINESIZE 200
COLUMN username FORMAT A15
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       o.object_name,
       DECODE(l.lmode,
              0, 'None',
              1, 'Null',
              2, 'Row Share',
              3, 'Row Exclusive',
              4, 'Share',
              5, 'Share Row Excl',
              6, 'Exclusive') locked_mode,
       s.blocking_session
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE s.username IS NOT NULL
  AND o.object_name = 'EMPLOYEES';

Find Blocking Sessions:

SET LINESIZE 180
COLUMN blocker_sid FORMAT 9999
COLUMN waiter_sid FORMAT 9999
COLUMN wait_time_min FORMAT 999.99

SELECT blocking_session blocker_sid,
       sid waiter_sid,
       username,
       ROUND(wait_time/60, 2) wait_time_min,
       sql_id
FROM v$session
WHERE blocking_session IS NOT NULL
  AND username IS NOT NULL;

Step 6: Create Undo Records (Most Critical for UPDATE)

Unlike INSERT, UPDATE requires storing the complete “before image” of each row.

Undo Record Structure:

┌──────────────────────────────────────────────────────┐
│              UNDO RECORD FOR UPDATE                  │
├──────────────────────────────────────────────────────┤
│                                                      │
│  Before Update:                                      │
│  ┌─────────────────────────────────────────────┐     │
│  │ employee_id: 101                            │     │
│  │ first_name: 'John'                          │     │
│  │ salary: 50000  ← OLD VALUE                  │     │
│  │ department_id: 10                           │     │
│  └─────────────────────────────────────────────┘     │
│                                                      │
│  After Update:                                       │
│  ┌─────────────────────────────────────────────┐     │
│  │ employee_id: 101                            │     │
│  │ first_name: 'John'                          │     │
│  │ salary: 60000  ← NEW VALUE                  │     │
│  │ department_id: 10                           │     │
│  └─────────────────────────────────────────────┘     │
│                                                      │
│  Undo Record Stores:                                 │
│  • Transaction ID                                    │
│  • Table and Row identifier                          │
│  • OLD salary value: 50000                           │
│  • Used for ROLLBACK and Read Consistency            │
│                                                      │
└──────────────────────────────────────────────────────┘

Why UPDATE Generates More Undo:

-- INSERT: Minimal undo (just delete the row if rollback)
INSERT INTO employees VALUES (101, 'John', 'Doe', 50000, 10);
-- Undo size: ~50 bytes

-- UPDATE: Full before image stored
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Undo size: ~200 bytes (entire row)

-- UPDATE multiple columns: Same undo size
UPDATE employees 
SET salary = 60000, department_id = 20, email = 'new@email.com'
WHERE employee_id = 101;
-- Undo size: ~200 bytes (entire row, not per column)

Monitor Undo Usage:

SET LINESIZE 180
COLUMN username FORMAT A15
COLUMN undo_mb FORMAT 999.99
COLUMN start_time FORMAT A20

SELECT s.username,
       s.sid,
       s.serial#,
       ROUND(t.used_ublk * 8192/1024/1024, 2) undo_mb,
       TO_CHAR(t.start_time, 'DD-MON HH24:MI:SS') start_time,
       s.sql_id
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY t.used_ublk DESC;

Check Undo Tablespace:

SET LINESIZE 150
COLUMN tablespace_name FORMAT A20
COLUMN status FORMAT A10
COLUMN size_mb FORMAT 999,999.99

SELECT tablespace_name,
       status,
       ROUND(SUM(bytes)/1024/1024, 2) size_mb
FROM dba_data_files
WHERE tablespace_name LIKE '%UNDO%'
GROUP BY tablespace_name, status;

Step 7: Constraint Validation

Oracle validates constraints on the NEW values.

-- Create table with constraints
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20) NOT NULL,
    salary NUMBER CHECK (salary > 0),
    email VARCHAR2(50) UNIQUE,
    department_id NUMBER,
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

-- NOT NULL violation
UPDATE employees 
SET first_name = NULL 
WHERE employee_id = 101;
-- ORA-01407: cannot update ("HR"."EMPLOYEES"."FIRST_NAME") to NULL

-- CHECK constraint violation
UPDATE employees 
SET salary = -5000 
WHERE employee_id = 101;
-- ORA-02290: check constraint (HR.SYS_C007123) violated

-- UNIQUE constraint violation
UPDATE employees 
SET email = 'existing@email.com' 
WHERE employee_id = 101;
-- ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

-- FOREIGN KEY violation
UPDATE employees 
SET department_id = 999 
WHERE employee_id = 101;
-- ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated

-- Correct UPDATE
UPDATE employees 
SET salary = 60000, department_id = 20 
WHERE employee_id = 101;
-- 1 row updated

Step 8-9: Redo Generation and Buffer Cache Update

Similar to INSERT, but UPDATE logs both old and new values.

Redo Record Content:

┌────────────────────────────────────────┐
│       REDO RECORD FOR UPDATE           │
├────────────────────────────────────────┤
│                                        │
│  • Operation: UPDATE                   │
│  • Table: EMPLOYEES                    │
│  • ROWID: AAAHYtAAEAAAACXAAA           │
│  • SCN: 1234568                        │
│  • Old Value: salary = 50000           │
│  • New Value: salary = 60000           │
│  • Undo segment pointer                │
│                                        │
└────────────────────────────────────────┘

Buffer Cache Modification:

BEFORE UPDATE (in Buffer Cache):
┌──────────────────────────────────┐
│ Block 1234                       │
│ Row 1: EmpID=101, Salary=50000   │
│ SCN: 1234567                     │
└──────────────────────────────────┘

AFTER UPDATE (in Buffer Cache):
┌──────────────────────────────────┐
│ Block 1234 (DIRTY)               │
│ Row 1: EmpID=101, Salary=60000   │
│ SCN: 1234568 (updated)           │
│ Status: Modified, not yet on disk│
└──────────────────────────────────┘

Step 10: Update Indexes (Only Affected Indexes)

Key Difference from INSERT: Only indexes on modified columns need updating.

-- Table structure
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,        -- Index: EMP_PK
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    salary NUMBER,                         -- Index: EMP_SALARY_IDX
    department_id NUMBER                   -- Index: EMP_DEPT_IDX
);

-- UPDATE only salary
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 101;

-- Index updates required:
-- ✓ EMP_SALARY_IDX (salary changed)
-- ✗ EMP_PK (employee_id not changed)
-- ✗ EMP_DEPT_IDX (department_id not changed)
-- Result: Only 1 index updated

-- UPDATE multiple columns
UPDATE employees 
SET salary = 60000, department_id = 20 
WHERE employee_id = 101;

-- Index updates required:
-- ✓ EMP_SALARY_IDX (salary changed)
-- ✓ EMP_DEPT_IDX (department_id changed)
-- ✗ EMP_PK (employee_id not changed)
-- Result: 2 indexes updated

View Index Updates:

SET LINESIZE 180
COLUMN table_name FORMAT A20
COLUMN index_name FORMAT A30
COLUMN column_name FORMAT A20

SELECT i.table_name,
       i.index_name,
       ic.column_name,
       i.uniqueness,
       i.status
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.index_name = ic.index_name 
                       AND i.owner = ic.index_owner
WHERE i.owner = 'HR'
  AND i.table_name = 'EMPLOYEES'
ORDER BY i.index_name, ic.column_position;

Step 11: Fire Triggers

UPDATE triggers can access both old and new values.

-- Audit trigger for salary changes
CREATE OR REPLACE TRIGGER emp_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        changed_date
    ) VALUES (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        USER,
        SYSDATE
    );
END;
/

-- Prevent salary decrease
CREATE OR REPLACE TRIGGER emp_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'Salary cannot be decreased');
    END IF;
END;
/

-- Test triggers
UPDATE employees 
SET salary = 55000 
WHERE employee_id = 101;
-- Error: Salary cannot be decreased (if old salary was 60000)

UPDATE employees 
SET salary = 65000 
WHERE employee_id = 101;
-- Success: 1 row updated, audit record created

View Triggers:

SET LINESIZE 200
COLUMN trigger_name FORMAT A30
COLUMN triggering_event FORMAT A20

SELECT trigger_name,
       trigger_type,
       triggering_event,
       status
FROM dba_triggers
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES'
  AND TRIGGERING_EVENT LIKE '%UPDATE%'
ORDER BY trigger_name;

Performance Optimization for UPDATE

1. Use Efficient WHERE Clauses

-- Bad: Full table scan
UPDATE employees 
SET salary = salary * 1.1 
WHERE UPPER(last_name) = 'SMITH';
-- Scans entire table, function on column prevents index use

-- Good: Index scan
CREATE INDEX emp_last_name_idx ON employees(last_name);

UPDATE employees 
SET salary = salary * 1.1 
WHERE last_name = 'SMITH';
-- Uses index, much faster

2. Minimize Undo Generation

-- Bad: Updates all columns
UPDATE employees 
SET first_name = first_name,
    last_name = last_name,
    salary = 60000,
    email = email
WHERE employee_id = 101;
-- Generates unnecessary undo/redo

-- Good: Update only changed columns
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 101;
-- Minimal undo/redo

3. Batch Updates with Commits

-- Bad: Single large transaction
UPDATE employees SET salary = salary * 1.1;  -- Updates 100,000 rows
COMMIT;
-- Huge undo segment usage, long lock time

-- Good: Batch with commits
BEGIN
    FOR emp IN (SELECT employee_id FROM employees) LOOP
        UPDATE employees 
        SET salary = salary * 1.1 
        WHERE employee_id = emp.employee_id;
        
        IF MOD(SQL%ROWCOUNT, 1000) = 0 THEN
            COMMIT;  -- Commit every 1000 rows
        END IF;
    END LOOP;
    COMMIT;
END;
/

4. Use Bulk Operations

-- Slow: Row-by-row in loop
BEGIN
    FOR emp IN (SELECT employee_id FROM employees WHERE department_id = 10) LOOP
        UPDATE employees 
        SET salary = salary * 1.1 
        WHERE employee_id = emp.employee_id;
    END LOOP;
    COMMIT;
END;
/

-- Fast: Single UPDATE statement
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 10;
COMMIT;

-- Fastest: Bulk collect with FORALL
DECLARE
    TYPE t_emp_ids IS TABLE OF NUMBER;
    l_emp_ids t_emp_ids;
BEGIN
    SELECT employee_id 
    BULK COLLECT INTO l_emp_ids
    FROM employees 
    WHERE department_id = 10;
    
    FORALL i IN 1..l_emp_ids.COUNT
        UPDATE employees 
        SET salary = salary * 1.1 
        WHERE employee_id = l_emp_ids(i);
    
    COMMIT;
END;
/

5. Avoid Unnecessary Index Updates

-- Consider dropping unused indexes before bulk update
DROP INDEX emp_unused_idx;

-- Perform bulk update
UPDATE employees SET salary = salary * 1.1;
COMMIT;

-- Recreate index if needed
CREATE INDEX emp_unused_idx ON employees(some_column);

Common UPDATE Errors and Solutions

Error: ORA-01407 (Cannot Update to NULL)

-- Problem
UPDATE employees 
SET first_name = NULL 
WHERE employee_id = 101;
-- ORA-01407: cannot update to NULL

-- Solution: Provide a value
UPDATE employees 
SET first_name = 'John' 
WHERE employee_id = 101;

Error: ORA-01654 (Unable to Extend Undo Segment)

-- Problem: Large update, undo tablespace full
UPDATE large_table SET column1 = 'value';
-- ORA-01654: unable to extend segment in undo tablespace

-- Solution 1: Add datafile
ALTER TABLESPACE undotbs1 ADD DATAFILE 
'/path/to/undotbs02.dbf' SIZE 2G;

-- Solution 2: Batch the update
UPDATE large_table SET column1 = 'value' WHERE ROWNUM <= 10000;
COMMIT;
-- Repeat in batches

Error: ORA-00060 (Deadlock Detected)

-- Session 1
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
UPDATE departments SET budget = 100000 WHERE department_id = 10;

-- Session 2 (simultaneously)
UPDATE departments SET budget = 100000 WHERE department_id = 10;
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- ORA-00060: deadlock detected while waiting for resource

-- Solution: Always update tables in same order
-- Both sessions should update employees first, then departments

Monitoring UPDATE Performance

Complete Monitoring Script

SET ECHO OFF
SET LINESIZE 200
SET PAGESIZE 100

PROMPT ========================================
PROMPT   UPDATE OPERATION MONITORING
PROMPT ========================================
PROMPT 

PROMPT 1. Top UPDATE Statements by Elapsed Time
PROMPT ----------------------------------------
COLUMN sql_text FORMAT A60 TRUNCATE
COLUMN elapsed_sec FORMAT 999.99
COLUMN rows_updated FORMAT 999,999

SELECT sql_id,
       SUBSTR(sql_text, 1, 60) sql_text,
       executions,
       rows_processed rows_updated,
       ROUND(elapsed_time/1000000, 2) elapsed_sec,
       buffer_gets
FROM v$sql
WHERE UPPER(sql_text) LIKE '%UPDATE%'
  AND sql_text NOT LIKE '%v$sql%'
  AND executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

PROMPT 
PROMPT 2. Undo Usage by Session
PROMPT ----------------------------------------
COLUMN username FORMAT A15
COLUMN undo_mb FORMAT 999.99

SELECT s.username,
       s.sid,
       ROUND(t.used_ublk * 8192/1024/1024, 2) undo_mb,
       t.start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY undo_mb DESC;

PROMPT 
PROMPT 3. Locked Objects
PROMPT ----------------------------------------
COLUMN object_name FORMAT A30

SELECT o.object_name,
       s.username,
       s.sid,
       s.blocking_session
FROM v$locked_object lo
JOIN dba_objects o ON lo.object_id = o.object_id
JOIN v$session s ON lo.session_id = s.sid;

PROMPT 
PROMPT 4. Buffer Cache Hit Ratio
PROMPT ----------------------------------------

SELECT ROUND((1 - (phy.value / (bg.value + cg.value))) * 100, 2) || '%' 
       AS hit_ratio
FROM v$sysstat phy, v$sysstat bg, v$sysstat cg
WHERE phy.name = 'physical reads'
  AND bg.name = 'db block gets'
  AND cg.name = 'consistent gets';

Best Practices Summary

1. Always Use WHERE Clause

-- Dangerous: Updates ALL rows
UPDATE employees SET salary = 50000;

-- Safe: Updates specific rows
UPDATE employees SET salary = 50000 WHERE department_id = 10;

-- Safest: Verify before update
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- Check count, then update

2. Use Bind Variables

-- Bad
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
UPDATE employees SET salary = 65000 WHERE employee_id = 102;

-- Good
UPDATE employees SET salary = :sal WHERE employee_id = :emp_id;

3. Index WHERE Clause Columns

-- Ensure indexes exist on frequently used WHERE columns
CREATE INDEX emp_dept_idx ON employees(department_id);
CREATE INDEX emp_status_idx ON employees(status);

4. Commit Appropriately

-- OLTP: Commit after each business transaction
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
COMMIT;

-- Batch: Commit in intervals
-- Every 1000-10000 rows depending on undo size

5. Monitor Long-Running Updates

-- Check progress of long-running UPDATE
SELECT sid, 
       serial#,
       sofar,
       totalwork,
       ROUND(sofar/totalwork*100, 2) pct_complete
FROM v$session_longops
WHERE opname LIKE '%UPDATE%'
  AND sofar <> totalwork;

Quick Reference: UPDATE Syntax

-- Basic UPDATE
UPDATE table_name SET column = value WHERE condition;

-- Update multiple columns
UPDATE employees 
SET salary = 60000, department_id = 20 
WHERE employee_id = 101;

-- Update with subquery
UPDATE employees 
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 10)
WHERE employee_id = 101;

-- Update with CASE
UPDATE employees
SET salary = CASE 
    WHEN department_id = 10 THEN salary * 1.1
    WHEN department_id = 20 THEN salary * 1.15
    ELSE salary * 1.05
END
WHERE hire_date < '2020-01-01';

-- Update from another table
UPDATE employees e
SET salary = (
    SELECT s.new_salary 
    FROM salary_updates s 
    WHERE s.employee_id = e.employee_id
)
WHERE EXISTS (
    SELECT 1 FROM salary_updates s 
    WHERE s.employee_id = e.employee_id
);

-- Update with RETURNING clause
UPDATE employees 
SET salary = salary * 1.1 
WHERE employee_id = 101
RETURNING salary INTO :new_salary;

Conclusion

Understanding UPDATE statement execution is crucial for Oracle database performance tuning. Key differences from INSERT include the need to locate existing rows, generate more extensive undo records, and potentially wait for locks held by other sessions.

Key Takeaways:

WHERE clause is critical – Use indexes for efficient row location
UPDATE generates more undo than INSERT – Full before image stored
Lock contention is common – Multiple sessions may wait for same rows
Only modified columns affect index updates – Unlike INSERT
Undo space management is crucial – Monitor for long transactions
Batch updates with commits – Prevent huge transactions
Use bind variables – Improve parse efficiency
Monitor blocking sessions – Prevent performance degradation

By mastering these concepts and using the monitoring scripts provided, you’ll be well-prepared for DBA interviews and equipped to optimize UPDATE performance in production environments!

Was this helpful?

Written by

W3buddy

Explore W3Buddy for in-depth guides, breaking tech news, and expert analysis on AI, cybersecurity, databases, web development, and emerging technologies.