Re: Snapshot too old but UNDO_RETENTION very high

[Quoted] On Fri, 6 Feb 2004 11:03:29 -0800, "DBA Infopower Support" <support_at_dbainfopower.com> wrote:

>Frequent commits would greatly reduce probability of ORA-1555, since
>consistent data required by transaction would have much less chance to be
>lost in rollback segments.

 I belive that is generally incorrect. Whilst a transaction is uncommitted the rollback/undo is effectively protected. Once you commit, it is subject to being overwritten. Increased commits means the rollback/undo data is _more_ likely to be 'lost', leading to snapshot too old.

 UNDO_RETENTION is not a guarantee. Look at the Reference manual.

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1216.htm#REFRN10225

"The UNDO_RETENTION parameter works best if the current undo tablespace has enough space for the active transactions. If an active transaction needs undo space and the undo tablespace does not have any free space, then the system will start reusing undo space that would have been retained. This may cause long queries to fail. Be sure to allocate enough space in the undo tablespace to satisfy the space requirement for the current setting of this parameter."

 Therefore, the more you commit, the more undo space you 'unprotect', and so the more likely you are to re-use undo required to give a read-consistent view as-of the SCN you started at.

 If you want to reduce the chance of snapshot too old, the general approaches are (as I understand them, corrections welcome):

(1) Increase your UNDO tablespace in size enough to cover the space required.

(2) Reduce periodic commits.

 Periodic commits are typically to mitigate against running out of rollback/undo space, but come at the cost of _increasing_ chance of snapshot too old. And in any case - they should only be done when it leaves the data in a consistent state. (Except perhaps in the case of an 'offline' upgrade where it is ensured that no client can connect to see the inconsistent state, and resuming the upgrade process will eventually restore the database to a fully consistent state [just covering the bases here since that's part of my full-time job ;-) ]).

(3) To arrange your DML in your process so it doesn't don't re-read blocks that you are altering during the course of the process (so they don't require the undo that is likely to be overwritten in the first place).

(4) As a last resort which is only applicable in certain _very_ limited circumstances, close and re-open the cursor and continue with a snapshot consistent at a later time than you started.

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>