RE: How to size UNDO for a database?

"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."

I think Tim's method is excellent for determining the size you actually need by measuring demand for undo. The other possibility is to over allocate past the bounds of the laugh test which might not be unreasonable given the ratio of iop/s to total acreage on modern devices. (This would be a bad idea if you've decided to allocate flash SSD or real SSD for undo, in which case you probably want to use serious effort as with Tim's monitoring plan to keep close to the margin of actual need.)

I don't see anywhere in Tim's advice that he says not to then change to a fixed size after you know what a reasonable fixed size is.

I think Timur's points are well taken once you do have an idea what the size should be.

To me this situation seems similar to using automatic memory management to let the software give you a measured idea of what is needed, followed by using fixed sizes with a healthy margin afterwards to avoid the side effects of dynamic shifting of small numbers of granules.

(And yes, I did not define the laugh test for this case. As Tim put it "nobody else on this list can begin to guess at.")

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Timur Akhmadeev
Sent: Tuesday, March 26, 2013 3:24 AM
To: tim_at_evdbt.com; oracle-l_at_freelists.org Subject: RE: How to size UNDO for a database?

Tim

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#REFR N10225). 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


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