How to Handle ORA-01555: Snapshot Too Old Error in Oracle Database?

 

Introduction

The ORA-01555: Snapshot Too Old error is a common issue in Oracle Database that frustrates DBAs and developers alike. It occurs when a query cannot access the consistent read data it needs from the undo tablespace, often due to long-running queries or insufficient undo retention. This error can disrupt critical operations, such as reporting or data exports, in busy database environments. In this detailed tutorial, we’ll explore the causes of ORA-01555, provide actionable steps to resolve and prevent it, and include real-life examples with scripts. Covering basic to advanced techniques, this guide ensures all readers—beginners to seasoned professionals—can tackle this error effectively and maintain optimal database performance.

---

What Causes ORA-01555: Snapshot Too Old?

The ORA-01555 error occurs when Oracle cannot maintain a consistent read view (snapshot) of data for a query because the required undo data has been overwritten. Key causes include:

1. Insufficient Undo Retention: The undo tablespace’s retention period is too short, and undo data is overwritten before a long-running query completes.

2. Long-Running Queries: Queries that take too long to execute, requiring older undo data that’s no longer available.

3. Small Undo Tablespace: Limited space in the undo tablespace, causing frequent overwrites of undo data.

4. High DML Activity: Frequent inserts, updates, or deletes generating large amounts of undo data, filling the tablespace quickly.

5. Delayed Block Cleanout: Uncommitted transactions from earlier sessions delaying cleanup of undo data.

6. Improper Configuration: Incorrect settings for UNDO_RETENTION or automatic undo management.

---

Step 1: Understanding the Error

When Oracle executes a query, it uses undo data to provide a consistent read view (snapshot) of the database at the query’s start time. If the undo data is overwritten before the query finishes, Oracle raises ORA-01555.

Example Error Message:

ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small

Real-Life Scenario: In an e-commerce database, a daily sales report query fails with ORA-01555 during peak hours when many transactions (e.g., order updates) are occurring. The query takes 20 minutes, but the undo data it needs is overwritten due to high DML activity.

---

Step 2: Diagnosing ORA-01555

To resolve ORA-01555, first identify the contributing factors using Oracle’s diagnostic tools.

2.1 Check Undo Tablespace Usage

Verify the size and usage of the undo tablespace.

Query:

SELECT 
    tablespace_name,
    bytes / 1024 / 1024 AS size_mb,
    (bytes - free_bytes) / 1024 / 1024 AS used_mb
FROM 
    dba_data_files ddf,
    (SELECT tablespace_name, SUM(bytes) AS free_bytes 
     FROM dba_free_space 
     GROUP BY tablespace_name) dfs
WHERE 
    ddf.tablespace_name = dfs.tablespace_name
    AND ddf.tablespace_name = 'UNDO_TBS1';

Observation: If used_mb is close to size_mb, the undo tablespace is too small.

2.2 Check Undo Retention Settings

Review the UNDO_RETENTION parameter, which defines how long undo data is retained.

Query:

SELECT name, value / 60 AS retention_minutes 
FROM v$parameter 
WHERE name = 'undo_retention';

Example Output: If retention_minutes is 15 (900 seconds), but queries run for 20 minutes, undo data may be overwritten.

2.3 Identify Long-Running Queries

Find queries that run longer than the undo retention period.

Query:

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    q.sql_text,
    (s.last_call_et / 60) AS running_minutes
FROM 
    v$session s
JOIN 
    v$sql q ON s.sql_id = q.sql_id
WHERE 
    s.status = 'ACTIVE'
    AND s.last_call_et > 900 -- Queries running > 15 minutes
ORDER BY 
    running_minutes DESC;

Real-Life Finding: In the e-commerce system, the sales report query (sql_id = 'xyz123') runs for 20 minutes, exceeding the 15-minute UNDO_RETENTION.

---

Step 3: Resolving ORA-01555

Once diagnosed, apply these solutions to fix and prevent ORA-01555.

3.1 Increase Undo Tablespace Size

A larger undo tablespace can store more undo data, reducing the chance of overwrites.

Steps:

1. Check current datafiles:

SELECT file_name, bytes / 1024 / 1024 AS size_mb 
FROM dba_data_files 
WHERE tablespace_name = 'UNDO_TBS1';

2. Add a new datafile or resize the existing one:

ALTER TABLESPACE UNDO_TBS1 ADD DATAFILE '/u01/app/oracle/oradata/undo_tbs2.dbf' SIZE 500M AUTOEXTEND ON;

Pros:

• Prevents ORA-01555 by providing more space for undo data.

• Simple to implement.

Cons:

• Increases storage requirements.

• May require downtime for non-autoextending tablespaces.

Real-Life Fix: In the e-commerce system, increasing the undo tablespace from 1 GB to 2 GB eliminated ORA-01555 for the sales report.

3.2 Increase UNDO_RETENTION

Extend the retention period to keep undo data longer.

Steps:

1. Check current retention:

SHOW PARAMETER undo_retention;

2. Increase retention (e.g., to 30 minutes):

ALTER SYSTEM SET undo_retention = 1800;

Pros:

• Ensures undo data is available for longer-running queries.

• No application changes needed.

Cons:

• Requires sufficient undo tablespace size.

• May increase space usage.

Best Practice: Set UNDO_RETENTION to exceed the duration of your longest-running queries. For example, if queries take up to 20 minutes, set UNDO_RETENTION to 1800 seconds (30 minutes).

3.3 Optimize Long-Running Queries

Optimize queries to run faster, reducing their reliance on undo data.

Example: Inefficient Query

SELECT o.order_id, c.customer_name
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND o.order_date > SYSDATE - 30;

Execution Plan:

EXPLAIN PLAN FOR
SELECT o.order_id, c.customer_name
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND o.order_date > SYSDATE - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Sample Output:

------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost |
------------------------------------------------------
|   0 | SELECT STATEMENT   |            | 50000 |  3000|
|   1 |  HASH JOIN         |            | 50000 |  3000|
|   2 |   TABLE ACCESS FULL| ORDERS     | 50000 |  1500|
|   3 |   TABLE ACCESS FULL| CUSTOMERS  |  1000 |   500|
------------------------------------------------------

Fix: Add an index on order_date to avoid full table scans.

CREATE INDEX idx_orders_date ON orders(order_date);

Optimized Execution Plan:

------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Cost |
------------------------------------------------------
|   0 | SELECT STATEMENT     |                 | 50000 |   800|
|   1 |  HASH JOIN           |                 | 50000 |   800|
|   2 |   INDEX RANGE SCAN   | IDX_ORDERS_DATE | 50000 |   200|
|   3 |   TABLE ACCESS FULL  | CUSTOMERS       |  1000 |   500|
------------------------------------------------------

Result: Query runtime reduced from 20 minutes to 2 minutes, avoiding ORA-01555.

Pros:

• Improves overall performance.

• Reduces undo data dependency.

Cons:

• Requires query analysis and tuning expertise.

• Indexes increase DML overhead.

3.4 Use Automatic Undo Management

Ensure the database uses Automatic Undo Management (AUM) for optimal undo handling.

Check AUM:

SHOW PARAMETER undo_management;

Enable AUM:

ALTER SYSTEM SET undo_management = AUTO SCOPE = SPFILE;

Pros:

• Automatically manages undo segments.

• Simplifies configuration.

Cons:

• Requires sufficient undo tablespace sizing.

Real-Life Fix: The e-commerce DBA confirmed AUM was enabled but increased the undo tablespace size to support high transaction volumes.

3.5 Adjust Transaction Patterns

Reduce undo generation by optimizing transaction sizes.

Example: Large Batch Update

UPDATE orders SET status = 'Processed' WHERE order_date < SYSDATE - 30;

Optimized Version: Process in smaller batches.

DECLARE
  v_batch_size NUMBER := 10000;
  v_rows_updated NUMBER := 0;
BEGIN
  FOR rec IN (SELECT ROWID FROM orders WHERE order_date < SYSDATE - 30) LOOP
    UPDATE orders SET status = 'Processed' WHERE ROWID = rec.ROWID;
    v_rows_updated := v_rows_updated + 1;
    IF MOD(v_rows_updated, v_batch_size) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

Pros:

• Reduces undo generation per transaction.

• Prevents undo tablespace overflow.

Cons:

• Requires application or script changes.

• May increase processing time slightly.

---

Step 4: Advanced Techniques

For complex environments, consider these advanced solutions:

4.1 Enable Guaranteed Undo Retention

Guarantee undo data retention for a specified period, preventing ORA-01555.

Steps:

1. Enable retention guarantee:

ALTER TABLESPACE UNDO_TBS1 RETENTION GUARANTEE;

2. Verify:

SELECT tablespace_name, retention FROM dba_tablespaces WHERE tablespace_name = 'UNDO_TBS1';

Pros:

• Ensures undo data is not overwritten prematurely.

• Ideal for critical long-running queries.

Cons:

• Increases undo tablespace usage.

• May cause ORA-30036 (out of undo space) if undersized.

4.2 Use SQL Tuning Advisor

Analyze and optimize long-running queries causing ORA-01555.

Example:

BEGIN
  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'xyz123',
    task_name => 'TUNE_SALES_REPORT'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_SALES_REPORT');
  DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SALES_REPORT');
END;
/

Pros:

• Provides automated index and query recommendations.

• Reduces query runtime.

Cons:

• Requires Tuning Pack license.

• May suggest complex changes.

4.3 Partition Large Tables

Partitioning large tables reduces the data scanned, speeding up queries and reducing undo usage.

Example:

CREATE TABLE orders (
  order_id NUMBER,
  order_date DATE,
  customer_id NUMBER,
  status VARCHAR2(20)
)
PARTITION BY RANGE (order_date) (
  PARTITION p1 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION p2 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

Pros:

• Improves query performance.

• Reduces undo generation for partitioned queries.

Cons:

• Adds complexity.

• Requires Enterprise Edition.

---

Step 5: Monitoring and Prevention

Prevent ORA-01555 with ongoing monitoring and maintenance:

1. Monitor Undo Usage:

SELECT 
  tablespace_name,
  SUM(bytes) / 1024 / 1024 AS used_mb
FROM dba_undo_extents
GROUP BY tablespace_name;

2. Schedule Regular Checks: Use DBMS_SCHEDULER to monitor undo tablespace and long-running queries.

3. Tune Queries Proactively: Regularly review execution plans for top queries using V$SQL.

Example Monitoring Script:

SELECT 
  s.sql_id,
  s.sql_text,
  s.executions,
  (s.elapsed_time / s.executions) / 1000000 AS avg_seconds
FROM 
  v$sql s
WHERE 
  s.executions > 0
  AND (s.elapsed_time / s.executions) > 900000000 -- Avg > 15 minutes
ORDER BY avg_seconds DESC;

---

Pros and Cons of Solutions

Solution	Pros	Cons
Increase Undo Size	Simple, prevents overwrites	Increases storage needs
Increase UNDO_RETENTION	Supports long queries	Requires larger tablespace
Optimize Queries	Improves performance, no extra space	Needs tuning expertise
Batch Transactions	Reduces undo generation	Requires code changes
Guaranteed Retention	Ensures undo availability	May cause space errors

---

Best Practices and Standards

1. Size Undo Tablespace Appropriately: Base size on transaction volume and query duration.

2. Set Realistic UNDO_RETENTION: Align with the longest-running queries.

3. Optimize Queries First: Tune queries before increasing undo resources.

4. Use AUM: Always enable Automatic Undo Management.

5. Monitor Regularly: Set up alerts for undo tablespace usage and long-running queries.

6. Test Changes: Validate solutions in a test environment to avoid production issues.

---

Real-Life Case Study

Scenario: An e-commerce platform’s sales report query fails with ORA-01555 during peak transaction hours. The query scans 50 million rows in the ORDERS table and takes 20 minutes.

Actions:

1. Increased undo tablespace size to 2 GB:

ALTER TABLESPACE UNDO_TBS1 ADD DATAFILE '/u01/app/oracle/oradata/undo_tbs2.dbf' SIZE 1000M;

2. Set UNDO_RETENTION to 1800 seconds:

ALTER SYSTEM SET undo_retention = 1800;

3. Added an index on order_date:

CREATE INDEX idx_orders_date ON orders(order_date);

4. Rewrote the query to use smaller date ranges:

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN SYSDATE - 7 AND SYSDATE;

Result: Query runtime dropped to 3 minutes, and ORA-01555 was eliminated.

---

Conclusion

The ORA-01555: Snapshot Too Old error can disrupt critical database operations, but it’s manageable with the right approach. By increasing undo tablespace size, adjusting UNDO_RETENTION, optimizing queries, and monitoring proactively, you can prevent this error and ensure reliable performance. Use the scripts and best practices provided to diagnose and resolve ORA-01555 in your Oracle Database.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone