| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: undo segments vs. rbs
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_at_doh.state.fl.us 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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue May 18 2004 - 17:14:29 CDT
 |
 |