| Monitoring system managed undo [message #145281] |
Wed, 02 November 2005 03:55 |
mike s
Messages: 2
Registered: November 2005
Location: Merseyside, UK
|
Junior Member |
|
|
New to these forums, so bear with me if I seem a bit pedantic....anyway, I'm seeking a bit of guidance on the above.
When we upgraded our 1 and a bit Tb database 5 weeks ago, from 8.1.7.4 to 10.1.0.4, we also implemented the use of system managed undo - mainly on the grounds that the documentation suggested it was a Good Thing  We had no issues with it in our pre-upgrade testing.
The undo tablespace is sized the same as the original "rollback segment" tablespace at ~20Gb. At 8.1.7.4, we were having no space problems with this tablespace, but since upgrading, we have on 2 or 3 occasions seen undo tablespace occupancy creep up to and beyond 99% - which triggers an alarm (via Patrol) for our operators.
A further delve into the documentation and consequent examination of V$UNDOSTAT suggests that this >99% occupancy is not in fact in itself a problem, as in this situation, Oracle "steals" blocks if it needs to. It was only this morning that we actually saw some (very limited) "expired block" stealing for the first time.
So the question arises - how to modify our "undo tablespace monitoring" SQL script so that it only triggers alarms when there is "real" space pressure? Seems to us that we'd have some genuine cause for concern only if we began to see stealing of "unexpired" blocks.
On that basis, a query along the lines of
select sum(unxpstealcnt)
from v$undostat
where begin_time > sysdate - (20 * 60) / (24 * 60 * 60);
would appear to be what we want - looking to trigger the alarm if this query returns a non-zero result.
This must be an admin issue which has arisen in other 9i/10g sites which use SMU - would you agree the above is the right way to tackle the issue?
t i a
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
