RE: How to size UNDO for a database?

The approach to undo looks nice and I would like it to satisfy the needs, but I doubt so. If you set undo to be auto extensible with 128M initial file size & you don't change undo_retention to the desired value, then you have no control of how much undo will be used and what is the calculated (tuned) undo_retention value (see the docs http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams262.htm#REFRN10225). Suppose you have a low activity profile in the database for some time and the undo needed to support the load is low, thus tuned undo retention is low; and Oracle retains very little undo data over time. If you will have a long running query at this time which will need undo data which is not there due to low tuned undo retention, you will easily see ORA-1555. This case is also noted in the MOS Doc 461480.1 https://support.oracle.com/epmos/faces/DocumentDisplay?id=461480.1 although it's quite old.

On the other hand, if you allocate fixed size undo, you know that Oracle will use _all_ available space for undo data, maximizing tuned undo_retention. In this case you are sure that all the data you can use will be used by Oracle for the right purpose (transactional activity) and for satisfying read consistency requests when it is necessary. One possible drawback is bug #9681444 (https://support.oracle.com/epmos/faces/DocumentDisplay?id=1112431.1). As you might guess I'm biased towards the fixed size undo for a well-known environment despite this bug. So if you are setting undo data file maxsize to 30g, I would rather make it equal to initial size plus explicitly specify autoextend off.

Regards
Timur Akhmadeev

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Tuesday, March 26, 2013 12:34 AM
To: oracle-l_at_freelists.org
Subject: Re: How to size UNDO for a database?

Ann,
Make the UNDO tablespace autoextensible, starting with a relatively small size, incrementing in relatively small "chunks", with a maximum file size that is standard for your environment.

For example, assuming that I'm running in an environment where the largest datafile I wish to backup (and restore) is 32 Gb, and I've decided to allocate Oracle-managed datafiles on ASM or file-system space in 128 Mb chunks, I'll use something like...

    create undo tablespace undo01 datafile size 128m autoextend on next     128m maxsize 32768m;

This allows you to start with something small, then over time to let it size itself, so that after a few days, weeks, or months of normal operation, you'll be able to look at V$UNDOSTAT and have an excellent idea of what normal undo usage is for the workload hitting your database.

Nothing is better than actual usage information for your environment.

If you fear running out of space even so, set the parameter RESUMABLE_TIMEOUT to a non-negative value, then monitor error messages to your "alert.log" file. If a resumable error is output to your alert.log, then a resumable_timeout setting of 1800 will give you 1,800 seconds (i.e. 30 minutes) to either increase AUTOEXTEND MAXSIZE or add another datafile or otherwise deal with the issue. Again, notice that the idea is to use these mechanisms to find the proper setting for your specific workload, which nobody else on this list can begin to guess at.

Hope this helps!

Thanks!

Tim Gorman

consultant -> Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035-2151 USA
web/blog   => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1 (303) 885-4526
twitter    => timothyjgorman

president  -> Rocky Mtn Oracle Users Group (www.RMOUG.org)
board      -> Oracle Developers Tools Users Group (www.ODTUG.com)
advisor    -> Northern California Oracle Users Group (www.NoCOUG.org)
secretary  -> Project SafeGuard (www.PSGHelps.org)
member     -> OakTable Network (www.OakTable.net)

Oracle ACE Director (www.oracle.com/technetwork/community/oracle-ace)

Lost Data? => www.ora600.be/ for info about DUDE...

On 3/25/2013 1:02 PM, Apps DBA wrote:
> Hi Gurus,
> I would like to know how to analyze and size undo for a busy database
> or while design itself. As the transactions increasing for a busy DB
> like reporting or DW DB we usually face lot of jobs failing with
> snapshot too old error. Please share your knowledge.
>
> Thanks,
> Ann
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l




________________________________
The information transmitted herein is intended only for the person or entity to which it is addressed and may contain confidential, proprietary and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
--
http://www.freelists.org/webpage/oracle-l