Re: Snapshot too old but UNDO_RETENTION very high

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.

[Quoted] 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)