"snapshot too old " and undo_retention 2005-09-29 - By Roger Xu
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 (See ORA-01555.ora-code.com): 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@(protected)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2180" name=GENERATOR></HEAD>
<BODY>
<DIV>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>Hi
List,</FONT></SPAN></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>Background: Oracle
9.2.0 w/ Auto Undo Management</FONT></SPAN></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>We have a huge
table
and it takes more than 24 hours to collect its statistics.</FONT></SPAN></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>But the
undo_retention is set to 864000 i.e. 24 hours. As a result we see the following
a lot.</FONT></SPAN></P>
<P><FONT face=Courier><FONT size=2><SPAN
class=269431921-29092005>"</SPAN>ORA-01555 (See ORA-01555.ora-code.com): snapshot too old: rollback segment
number 11 with name "_SYSSMU11$" too small<SPAN
class=269431921-29092005>"</SPAN></FONT></FONT></P>
<P><FONT face=Courier size=2><SPAN class=269431921-29092005>Since we have lots
of space in the undo tablespace, I am thinking of increasing undo_retention to
36 hours</SPAN></FONT></P>
<P><SPAN class=269431921-29092005><FONT face=Courier size=2>via <FONT
style="BACKGROUND-COLOR: #ffff66">"</FONT></FONT><FONT face=Courier><FONT
size=2><FONT style="BACKGROUND-COLOR: #ffff66">ALTER </FONT>SYSTEM SET
UNDO_RETENTION = 129600;"<BR></FONT></FONT></SPAN></P>
<P><FONT face=Courier size=2><SPAN class=269431921-29092005>Is this my only
choice?</SPAN></FONT></P>
<P><FONT face=Courier size=2><SPAN class=269431921-29092005>What are the
drawbacks to have a large undo_retention value assuming we never run out undo
tablespace?</SPAN></FONT></P>
<P><FONT face=Courier size=2><SPAN
class=269431921-29092005></SPAN></FONT> </P>
<P><FONT face=Courier size=2><SPAN
class=269431921-29092005>Thanks,</SPAN></FONT></P></DIV>
<P><FONT face=Courier><FONT size=2>Roger Xu <BR></FONT></FONT></P>
<BR>
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.
<BR>
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ _____<BR>
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@(protected)<BR>
</BODY></HTML>
|
|
