RE: How to size UNDO for a database?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Akhmadeev@xxxxxxxxxxxxxx>, <tim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Mar 2013 09:14:26 -0400
Tim's last sentence is very telling:
"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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Timur Akhmadeev
Sent: Tuesday, March 26, 2013 3:24 AM
To: tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Tuesday, March 26, 2013 12:34 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx
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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
--
//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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
