[Oracle] Analysis of maddening ORA-01555 Problems

Therefore, in the daily maintenance of the database, if only one or two encounter ORA-01555 errors, generally ignore, But if you often encounter this error, make some adjustments to avoid this error from happening again.

1. Why is a ORA-01555 error?
This error is caused by the Read consistency of the database. When querying the data to be accessed, it will go to the undo to access the pre-image of the data, if the previous image is overwritten, the query statement returns an error for the ORA-01555.

2. What are the main causes of ORA-01555 errors?
1) undo is too small
The intuitive solution is to tell the database application how long it takes to query the longest, set UNDO_RETENTION to this value, and increase the undo tablespace size accordingly.
However, to fundamentally solve the problem of undo being too small, you must start with application code optimization to reduce the query running time.

2) Delayed block clearing
This situation is rare, especially in the OLTP system, which may be encountered in the OLAP system. The solution is to use the DBMS_STATS package to scan related objects after a large number of insert or update operations.