Re: Snapshot too old but UNDO_RETENTION very high

Hello Daniel,

   I believe at this point we argue on two different issue.

   Let's user clarify what he is trying to achieve and see, if he can post related SQL.

Regards,

Ron

Support

DBA Infopower

https://www.dbainfopower.com

The advice provided by DBA Infopower are provided "as is" with no warranty. DBA Infopower expressly disclaims any warranty, regarding the advice including any implied warranty of merchant fitness for a particular purpose aviating course of dealing and/or performance. DBA Infopower does not warrant that the advices provided by DBA Infopower will be free from bias, detests, errors, eavesdropping or listening. DBA Infopower shall not be responsible for the quality of information or the authentication of the services or details given by experts on the advice. By using this advice the user of the advice accepts the terms and conditions of this statement.

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1076135406.440732_at_yasure...
> DBA Infopower Support wrote:
> > Hello Daniel,
> >
> > Please correct me if I am wrong:
> >
> > User complains, that while running lengthy update it fails with
> > Ora-1555.
> >
> > Amount of rows is 9M rows and considering that other transactional
> > activity on database continues it is very probably that consistent
> > information in rollback segment will be overwritten and 1555 error would
be
> > generated.
>
> You are incorrect. Let me quote Tom Kyte from Expert one-on-one Oracle
> (as published by WROX), page 185:
>
> "The ORA-01555 is one of those errors that confound people. It is the
> foundation for many myths, inaccuracies and suppositions. The error is
> actually straightforard and has only two real causes, but since there is
> a special case for one of them that happens so frequently, I'll say
> that there are three. They are:
>
> * The rollback segments are too small for the work you perform on your
> system.
>
> * Your programs fetch across COMMITs (actually a variation on the
> above).
>
> * Block cleanout."
>
> Not one of these is solvable by more frequent commits. In fact, as I
> pointed out, your solution actually aggravates the problem making 1555s
> more likely.
>
> Let me continue from page 190 of Tom's book:
>
> "The amusing thing is that people sometimes react to this error by
> committing even more frequently since the message says rollback segment
> too small. The thinking is that this will help the problem (...) when in
> fact it will only ensure that it happens even faster."
>
> If you need more ... read Chapter 5 of Tom's book.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Sat Feb 07 2004 - 08:07:50 CET