Re: Measuring Rollback Usage

"Howard J. Rogers" <howardjr_at_www.com> writes:

> First, queries don't generate rollback. Only DML does that.

I was referring to all of select, insert, delete, and update.

> Second, v$rollstat joined with v$transaction will give you what you are
> after -in cunningly named fields like UBABLK and UBAREC.

So cunning you could stick tail on it and call it a weasel.

> At least, you can use that to work out how much rollback a given transaction
> is generating. Whether that stuff is then being read by anyone else is much
> more difficult to work out (ie, I don't know a way of doing it... and I
> suspect there isn't a way, because if there were, we'd never have Snapshot
> Too Old errors).

Well that doesn't directly follow since Oracle wouldn't be able to predict the future and predict which data in rollback would be needed.

I suspect you're right though since it seems it would introduce a major point of contention if every time rollback was read it had to look up the original query that caused the rollback and update something there. Even updating the rollback segment itself seems like it would introduce a lot of overhead.

-- 
greg