Re: Rollback Segment Wrapping

Technically, a wrap is indeed a move from any extent to any other extent.

Am I being daft when I ask.... why can't you take the number as provided by v$rollstat and divide by the number of extents to arrive at the number of times you've 'recycled'? Maybe I'm missing something (which is always quite possible).

Too few segments is in any case more simply diagnosed by checking for rollback segment header contention: select sum(waits)*100/sum(gets), sum(waits), sum(gets) from v$rollstat; ...and that ought to be less than around 1%. Add more segments if not.

Also check select value from v$sysstat where name='consistent gets'; and then select class, count from v$waitstat where class like '%undo%'; .... the second number should be less than 1% of the first lot added together. If it's not, you've too few segments.

As you indicate that you've already demonstrated a high number of waits, presumably using something like either or both of these queries, I can't really see what further proof you need to provide to your DBAs!

As for size issues, these should also be more simply diagnosed by checking, for example, v$transaction for the size of the transactions taking place -all rollbacks should be able to house the largest typical one, unless you have a hybrid system, where some rollbacks are reserved for batch transactions, and some for smallish OLTP-type stuff. Or you could look at the writes column of v$rollstat before and after typical transactions and size them that way.

You say that you've already diagnosed the fact that your rollback segments are frequently extending... unfortunately, that doesn't prove that they are too small, because a rogue user can leave a transaction uncommitted, which will induce extent acquisition, but only tells us that you have rogue users, not that the segments are badly sized. You can distingush between the two causes of extent acquisition, though, by looking at v$rollstat in conjunction with v$transaction, which tells you the start time of transactions. Rogue uncommitted ones will have a much earlier start time than normal ones.

All of which means that knowing whether you've 'recycled' your segments is not typically necessary for size/number problem diagnosis. That said, a lot of 1555s would indeed suggest you are recylcing too fast.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================

"billiauk" <billiauk_at_yahoo.co.uk> wrote in message
news:dee17a9f.0201090733.7814dbcd_at_posting.google.com...


> Is there any way we can get meaningful information from v$rollstat

> regarding wraps in the sense that the rollback segment recycles?

>

> There is a script available through Metalink that shows "wraps" as the

> ratio of writes/rssize, yet the view has a "wraps" column which I

> understood to be the number of extent switches.

>

> I'm trying to prove to our DBA's that we have too few rollback

> segments and that they are too small. We have a high number of waits

> and extends and several snapshot too olds, not all of which can be

> attributed to block cleanout.

>

> Thanks

>

> Adrian