Re: snapshot too old

Comments interspersed.

Mark Bole wrote:
> Daniel Morgan wrote:
>
> Here is what I've gotten from this thread so far:
>
> 1) there is a widespread, long-standing myth that committing every so
> many rows within a PL/SQL loop similar to the OP's is good because it
> keeps your undo from getting "too big".

Correct.

> 2) expert advice debunks this myth because (according to Kyte) "[undo]
> is not 'overhead', not something to minimize, not something to skimp
> on", and besides, doing fetches across commits can lead to ORA-1555 errors.

Correct.

> But isn't it still possible to get an ORA-1555 in this type of
> situation, even with no commits inside the loop, due to the select query
> on which the cursor is based failing a read-consistent fetch when other
> database activity causes undo segments to be overwritten while the loop
> is running?

Correct. Possible but less likely if one codes correctly and, theoretically impossible if the UNDO tablespace is large enough.

> My statement (now a question), more precisely, was this: isn't
> pre-fetching into memory the entire list of values to loop on, using
> something like a PL/SQL collection, one way (and maybe a good way) to
> avoid this particular issue? I don't see this approach used very often,
> is it because of lack of awareness or something else?

If you have enough memory that you can put everything you want into RAM and never have to go back for something in undo yes. As I belive Sybrand said ... buy a lot of RAM.

> The amount of undo normally needed for our OLTP database is relatively
> small. But when our developers come along after every other software
> release with a massive update script to "clean up the residue of a bug",
> having the UNDO tablespace suddenly swell to many times its normal size
> and stay that way becomes an operational liability {think capacity
> planning, think disaster recovery, think being paged in the middle of
> the night). With rollback segments, at least you could drop and
> recreate them one at a time and eventually shrink the tablespace(s)
> containing them back down -- I haven't found out how to do that with
> UNDO tablespaces.

Nor would you want to. Shrinking rollback segments was never a particularly good strategy except for adding overhead to a system. Other than the issue raised a month or so back by Howard Rogers I can't come up with a good reason not to throw a bunch of 70GB drives at undo at let it manage itself.

> Maybe 10-20% is too conservative, but I believe there is a point where
> the overhead can be considered excessive.
>
> --Mark Bole

Convince Mr. Kyte and you can convince me too.

-- 
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)