RE: How to size UNDO for a database?
- From: Timur Akhmadeev <Akhmadeev@xxxxxxxxxxxxxx>
- To: "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Mar 2013 07:23:59 +0000
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#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@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
Other related posts:
