Re: Rollback segments

Comments below.

Greg Stark <greg-spare-1_at_mit.edu> wrote in message news:87bstbhrq4.fsf_at_HSE-MTL-ppp62571.qc.sympatico.ca...
>
> "Howard J. Rogers" <howardjr_at_www.com> writes:
>
> > Yes, a huge one. New transactions cannot move into an extent of a
 rollback
> > segment which has an active transaction within it. With just two
 extents,
> > the chances of filling up the second extent and thereby needing to move
 back
> > into the first which is likely to still have one active transaction in
 it
> > (however trivial that transaction is) is extremely high. The more
 extents
> > you have, the lower the probability. When it can't move back into the
 first
> > extent because of the presence of an active transaction, the segment
 will
> > instead acquire (dynamically) an additional extent.
>
> Does each rollback extent contain output from exactly one transaction
 then?
> What happens if your rollback segments are not autoexend and you have as
 many
> active transactions as you have extents?
>

No! Transactions can share extents, and if that's not clear from the above, my apologies. In Oracle 6 days, it *was* the case that if a transaction, however trivial, grabbed 30 bytes from a 100M extent, that entire extent was blocked from being used by any other transaction until the original transaction had finished (ie, either been committed or been rolled back). But that was all changed in version 7, and transactions can now happily share extents. As Jonathan Lewis can attest, it is now true in 8i that transactions can even share blocks, provided one of them is dead. In other words, if transaction 1 takes up 30 bytes from block 1, extent 1, and commits, then transaction 2 can place its rollback into extent 1, block 1.

Nevertheless, the point I was making earlier remains generally true: if an extent has an active transaction in it somewhere, then that extent can not be moved into by new transactions. In other words, if transaction 1,2,3,4,5 and 6 have been placed in Extent 1, and all bar transaction 4 have been committed, Transaction 115 which has written its first block into extent 6 cannot write its second block into extent 1, because extent 1 is still live. It will be forced to acquire extent 7 to continue its writes.

Since the *probability* of there being a live transaction in an extent must inevitably increase with the size of the extent, the probability of having to acquire an extra extent must equally increase as extent size increases -at least up to a point.

You then ask what happens if your segment is not allowed to autoextend. Your transaction will simply fail and rollback. Suppose you *do* enable autoextends (ie, you set maxextents very high): what will happen when your rollback *tablespace* runs out of space... same thing. In other words, the problem of failed transactions due to non-growth of rollback segments is totally independent of the *number* of extents a rollback segment has, or the number of transactions using it.

All this boils down to a few simple rules: 6 extents or so; the segment as large as your largest-possible transaction (check v$transaction); 1 rollback segment per 4 concurrent transactions (unless you have stack loads of concurrent transactions, in which case 1 per 8 is acceptable); don't set optimal; force manual re-shrinks at the dead of night...er, that's about it.

> Do any of the statistics represent average,min,max rollback space used per
> transaction? It seems like it should be possible to gather such statistics
 as
> well as standard deviation and size the rollbacks carefully.

Not having v$rollstat to hand, I can't guarantee that what you want is precisely there, but that and v$transaction are likely to be your first ports of call.

Regards
HJR
>
> --
> greg
Received on Sat Jan 13 2001 - 21:13:29 CST