Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: "snapshot too old" and undo_retention
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu
Sent: 29 September 2005 22:33
To: Oracle-L_at_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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 30 2005 - 02:12:51 CDT
![]() |
![]() |