RE: "snapshot too old" and undo_retention
- From: "Hallas, John, Tech Dev" <John.Hallas@xxxxxxxxxxxxxxxxx>
- To: <roger_xu@xxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Sep 2005 08:04:30 +0100
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
Other related posts:
