If it is a huge table then it does not really matter how accurate the statistics are so why bother gathering them. Estimate them instead. dbms_stats.set_table_stats( user, 'TABLE', numrows => 50000, numblks => 10000 ); dbms_stats.set_column_stats( user, 'TABLE', 'COLUMN', distcnt => 15000 ); I think space is the only issue with a large undo_rention area. However do you really want a job to be running for 36 hours Is the table not partitioned? If so then use the partition option of dbms_stats and break the job down into smaller sections HTH John _____ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Roger Xu Sent: 29 September 2005 22:33 To: Oracle-L@Freelists. Org (E-mail) Subject: "snapshot too old" and undo_retention Hi List, 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? Thanks, Roger Xu This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the material. ____________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System. Any questions please call 972-721-8257 or email your request to tech_support@xxxxxxxxxxxx