Re: Undo retention and tablespace size

On Thu, 27 Oct 2005 02:55:13 +0200, Keysan interested us by writing:

> "HansF" <News.Hans_at_telus.net> wrote message
> news:pan.2005.10.27.00.35.44.191178_at_telus.net...
>
>> Did you use the 'formula' or Enterprise Manager's guess?
>
> Someone told me and now i realize that this someone was clearly passing me
> the Enterprise manager's guess...
 

It's not a bad tool, and the guess is reasonably accurate to the limit for which it was designed, which is "predict the UNDO size to accomodate 'x' seconds of undo based on recent transaction activity"

>> Why is this a concern?
>
> because i tried using this app of mine and undotbs went up to ~10GB with
> autoextend option on. Customer wants me to use the same app and mailed me
> the .ora with the 3 hours default parameter but with the aforementioned
> undersized undotbs.
>
> I'm not a db guru so i must to be sure before stating this is a wrong setup
> EVEN without using a "moderately busy DB" like you said.

If you need 10GB of UNDO space to complete the job, you need that space. You may try tricks to reduce it, such as frequent COMMITs in the app, but that usually backfires.

The idea of using the retention (which is an attempt to provide sufficient size to permit UNDO of transactions that were in flight and completed up to 3 hours ago) to manage the minimum size required for base transactions is relatively silly to me.

Turn the question around and find out how many seconds of UNDO under typical load you will get with 2 GB space and decide whether that will be enough. BY enough, I'd say at least twice the time needed to run your application. I suggest you start up the Enterprise Manager (single instance mode will do) and look at the first Instance page. One of the tabs will give you access to the UNDO space estimator.

You may also want to look in the "Administrator's Guide" found at http://docs.oracle.com There is a whole chapter dedicated to understanding and managing the UNDO and ROLLBACK.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting guarantees I will not respond further ***