I have a production database running in AUM and I am receiving the "ORA-1555: Snapshot too old" error when a long-running query is executed. I tried changing the UNDO_RETENTION to 40,000 seconds (as the query takes more than 24 hours to complete processing). The UNDO tablespace is set to autoextend mode. Please find below the queries and results for the suggested values:
SQL> SELECT MAX(maxquerylen) FROM v$undostat;
SQL> SELECT MAX(undoblks) FROM v$undostat;
The problem now is can we set the UNDO_RETENTION value to the above value (more than 74,670 seconds)? Will it create any performance-related issue for any of the other processes?
Your UNDO_RETENTION setting should be at least MAX(maxquerylen) as seen in V$UNDOSTAT over time. And your Undo tablespace should be at least MAX(undoblks) times your DB_BLOCK_SIZE parameter. Setting your UNDO_RETENTION too large will not necessarily cause performance problems, but it can require a larger Undo tablespace than you really need. Oracle will try to keep all of that Undo for a very long time. If I saw the max query length of 74,670 seconds, I would consider setting UNDO_RETENTION to at least 100,000 seconds and even 150,000 seconds, but I'm not sure that I would consider 400,000 seconds.
Dig Deeper on Oracle database design and architecture