The ORA-01555 error originates from the overwrite of the historical data required in the Oracle database consistency reading mechanism, which includes insufficient UNDO table space, long transaction interference, poor SQL execution efficiency and delayed block cleaning mechanism. 1. Check and expand the UNDO table space, enable automatic expansion or increase UNDO_RETENTION time; 2. Find and terminate transactions that have not been submitted for a long time to free UNDO resources; 3. Optimize the SQL statement execution plan, add appropriate indexes and process big data in batches; 4. Avoid executing large-scale queries immediately after large-scale DML operations, and manually refresh the buffer cache if necessary. The above measures can be effectively solved.

ORA-01555 is a common error in Oracle database, prompting "snapshot too old", which means that the query cannot obtain the rollback information required for consistent reads. Simply put, your query runs for too long and the old data it needs has been overwritten or cleaned.

Why does this error occur?

Oracle uses a consistent read mechanism to ensure that the query sees a consistent data snapshot. When a query is executed, it relies on information in the UNDO tablespace to rebuild past data versions. If this information is overwritten (for example, UNDO is reused after the transaction is committed), the query cannot continue, and an ORA-01555 error is thrown.

Check whether the UNDO tablespace size is reasonable

This is the most common reason. If the UNDO tablespace is too small, or the automatic expansion settings are unreasonable, long-running queries may not find enough historical data.

• Check the current UNDO tablespace usage :

 SELECT 
  a.tablespace_name,
  (SUM(a.bytes) / 1024 / 1024) AS "Total Size MB",
  (SUM(a.maxbytes) / 1024 / 1024) AS "Max Size MB"
FROM dba_data_files a
WHERE a.tablespace_name LIKE 'UNDOTBS%';

• Recommended operations :
  • If you find that UNDO tablespaces are often close to full load, consider increasing their size or enabling automatic scaling.
  • Set the appropriate RETENTION time, such as increasing the UNDO_RETENTION parameter (units in seconds).

Check for long transaction interference

Sometimes, there are transactions that have not been submitted for a long time in the system, which will cause UNDO to be released in time, affecting other queries.

• Find the currently active transaction :

 SELECT 
  s.sid, 
  s.serial#, 
  t.xidusn, 
  t.used_ublk, 
  t.start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr;

• Suggested practices :
  • Avoid doing too much in one transaction and commit as early as possible.
  • If there is an exceptionally pending session, you can consider terminating to free UNDO resources.

Inadequate optimization of the query itself leads to excessive execution time

Sometimes the problem is not the database configuration, but the SQL statement itself is poorly efficient, resulting in the consistent read window being too long.

• Common phenomena :

  • Full table scanning has no index support
  • Large number of sorting or hashing connections
  • Lack of appropriate WHERE conditions to filter data

• Recommended improvements :

  • Check the execution plan and confirm whether there are performance bottlenecks
  • Create appropriate indexes, especially fields for filtering and joining
  • Processing large data volume result sets in batches

Enabled "delayed block clearing" mechanism may also cause this problem

In some cases, even if there are no long transactions, Oracle may cause the consistency view to be missing when performing delayed block clearing, triggering ORA-01555.

• This kind of scenario is relatively rare , but if you have just done large batches of DML operations (such as UPDATE, DELETE) and then run a large query, you may encounter this problem.
• Solution :
  • Try to avoid executing large-scale queries immediately after DML
  • ALTER SYSTEM FLUSH BUFFER_CACHE; (suitable for testing environments)

Basically, these common reasons and coping methods. Although this error may seem a bit scary, it can be effectively avoided most of the time just adjust the UNDO configuration, optimize SQL, or control the transaction lifecycle.

The above is the detailed content of How can you diagnose and resolve ORA-01555 'snapshot too old' errors?. For more information, please follow other related articles on the PHP Chinese website!