| undo management [message #129583] |
Mon, 25 July 2005 19:21  |
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 #129723 is a reply to message #129583] |
Tue, 26 July 2005 07:33  |
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 |
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 |
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 |
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 #130294 is a reply to message #130015] |
Fri, 29 July 2005 03:22 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
