Background: Oracle 9.2.0 w/ Auto Undo Management
We have a huge table and it takes more than 24 hours to collect its statistics.
But the undo_retention is set to 864000 i.e. 24 hours. As a result we see the following a lot.
"ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small"
Since we have lots of space in the undo tablespace, I am thinking of increasing undo_retention to 36 hours
via "ALTER SYSTEM SET
UNDO_RETENTION = 129600;"
Is this my only choice?
What are the drawbacks to have a large undo_retention value assuming we never run out undo tablespace?