[Oracle] maddening ORA-01555 Problems

[Oracle] The first thing to note about the maddening ORA-01555 problem is that a ORA-01555 error is a "secure" error that won't cause data loss or corruption, only make the query that receives this error Unable to continue, therefore, in the routine maintenance work of the database, if only one or two encounter ORA-01555 error, generally ignore first, however, if this error is frequently encountered, 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) The undo is too small. The intuitive solution is that DBA tells the database application how long it takes to query the longest, sets UNDO_RETENTION to this value, and increases the size of the undo tablespace 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 is rare, especially in OLTP systems, which may be encountered in OLAP systems. Solution: after a large number of insert or update operations, remember to use the DBMS_STATS package to scan related objects.