Re: undo segments vs. rbs
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 18 May 2004 16:16:56 -0600
Unfortunately, the only tablespace format acceptable for an undo tablespace is
LMT autoallocate. Uniform size is not allowed. Which
really puts a hole in the documentation's "You can have multiple undo
tablespaces with various configurations for different
operations." IIRC, this also introduces the possibility of fragmentation within
your undo tablespace (I think someone out there in
Oracle-L Land was testing this, but I don't recall any definitive results). The
bottom line is that the size of an undo segment in
auto mode is out of your control. Even the # of segments created is out of your
control. You can control the size of the undo
tablespace (hey, 1 out 3 ain't bad). My recommendation is to size it at 1.5x
the current rbs tablespace size. If you want, you can
set it to autoextend, but the extent management algorithm may cause the
autoextension when you really don't need it.
I think the documentation has a formula to determine the optimal size of the
undo tablespace. Unfortunately, you have to already be
running in automatic undo mode to get the necessary input data. <soapbox> It
sure would have been nice if Oracle would have tracked
the undo usage data (or even a near guess) while you were using manual mode
(rollback segments). That would make transition to
auto-undo so much nicer. </soapbox>
DO NOT use auto undo on high volume OLTP systems. Stay with the traditional
rollback segments. It is better to have ORA-1555s than
ORA-7445s followed by ORA-600s followed by a call from the help desk at 3am
telling you that people are complaining that the
database is down. (okay, I guess I really am still on my soapbox).
Regards,
Daniel
Mladen Gogala wrote:
> Well, I suggest you to have one LMT with uniform extent size of 67108864 bytes
> and forget about sizing and re-sizing. To save you the trouble of calculating
> 64*1048576=67108864. Nothing like the binary numbers, don't you agree? I
> mean,
> if 10M is a large extent, 64M would probably be considered humongous and would
> solve all your problems. As Oracle is NOT caching extents (a very persistent
> fairy tale, with the same amount of truth as Cinderella, Sleeping Beauty or
> Red
> Riding Hood) you will not see any difference in performance. What you will
> also stop
> seeing are the coveted ORA-01555 messages. God knows when did I see my last
> one?
> It makes me so nostalgic.
>
> On 05/18/2004 04:34:15 PM, Paula_Stankus@xxxxxxxxxxxxxxx wrote:
>
>>I am working on moving a productional database from 8.0.6 to 9i. I have =
>>a question: if I have 10 rbs at 128K (initial and next extent) and one =
>>"large" on at 10,485,760 bytes how does that translate into undo =
>>segments? =20
>>
>>Are there any best practices on sizing undo segments?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
