Home » RDBMS Server » Performance Tuning » undo management
undo management [message #129583] Mon, 25 July 2005 19:21 Go to next message
mkuipers
Messages: 3
Registered: July 2005
Junior Member
We are looking at database that is running automatic undo management and we notice a large about of waits, shrinks, wraps etc in v$rollstat. I can't find any information on this. Should we ingore this or switch to manual undo mgt? We're still running 9.2.0.6

Thanks for the help
Re: undo management [message #129658 is a reply to message #129583] Tue, 26 July 2005 03:41 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

Have you ever considered increasing size of undo tablespace?

Regds
Girish
Re: undo management [message #129723 is a reply to message #129583] Tue, 26 July 2005 07:33 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'd leave it at automatic and not go back to manual. All you have to pay attention to is the size of the tablespace and the value of the retention parameter.

(btw, was reading in 10gR2 new features and oracle will automatically configure the retention parameter for you based on an assumed fixed size undo tablespace)
Re: undo management [message #129816 is a reply to message #129583] Tue, 26 July 2005 15:56 Go to previous messageGo to next message
mkuipers
Messages: 3
Registered: July 2005
Junior Member
We've increased the Undo space and now OEM says only 4% is being used, however there are only 10 undo segments no matter how many connections we have. We were running some tuning scripts and saw the waits, shrinks, wraps on rollstats and were wondering if this may be one of our issues?

Thanks for the reply's so far.
Re: undo management [message #130007 is a reply to message #129816] Wed, 27 July 2005 11:45 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
When you create an undo tablespace, Oracle will automatically create 10 undo segments for your use, he will try as much as possible to allocate only one transaction per undo segment (to help prevent "snapshot to old" errors amongst other things.

If the number of concurrent transactions grows Oracle will automatically create more undo segments upto the limit of the size of your tablespace, as the transaction load decreases the number will again be reduced to a minimum of 10.

Each individual undo segment can increase in size to support a single (or possible multiple) transaction, this means that although you are only currently using 4% of your UNDO tablespace you may actually need alot more at peak times.

Look at HWMSIZE to see how large individual segments have become, and AVGACTIVE to see how many transactions are using each segment simultaneously.

As long as your not getting "Snapshot too old", or "Unable to allocate extent in tablespace UNDO" errors then I wouldn't really worry about the individual values in v$rollstat.

The only other thing I would look for (If performance is an issue) is "Buffer busy waits" on the header blocks of Undo Segments. This can signify that the tablespace is too small and Oracle is having to allocate too many transaction to each Undo Segment.

Hope that helps.

AJ----------
Re: undo management [message #130010 is a reply to message #129583] Wed, 27 July 2005 12:04 Go to previous messageGo to next message
mkuipers
Messages: 3
Registered: July 2005
Junior Member
Thanks AJ

One last question

SEGMENT_SPACE_MANAGEMENT for UNDO's.

I noticed it was set to manual not Auto.


What should it be, suggestions?

Thanks all
Re: undo management [message #130015 is a reply to message #130010] Wed, 27 July 2005 13:00 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Set it to 'auto'
Re: undo management [message #130294 is a reply to message #130015] Fri, 29 July 2005 03:22 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Because of the frequent allocation and deallocation of undo blocks in the Undo tablespace it is usually better to use ASSM, this will help reduce recursive SQL to the data dictionary tables (which is always good), and allow for more efficient use of the undo segments by concurrent transactions - which is a major factor for freelists.

If you want more information about that give me a shout.

AJ
Re: undo management [message #130673 is a reply to message #130294] Mon, 01 August 2005 22:22 Go to previous message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
I would like more information about that,please.
I'm not so happy with the performance of ASM.
I have ASM on one of my servers and the same application runs on the other. One is with ASM, the other- not - I manually set the freelists for the 8 busiest tables. It has much better performance - the same data, the same hardware, Ora 10.1.0.4, same app. May be there's something I can tune to make ASM really functioning right and to get all benefits of it.
Thanks,mj
Previous Topic: delete statement cause application hang
Next Topic: How to avoid full tablescan using group function?
Goto Forum:
  


Current Time: Fri May 27 00:58:30 CDT 2022