Re: Rollback segments

"Naushi Hussain" <naushi.hussain_at_alliedsignal.com> wrote in message news:3A648A3F.B2078E6E_at_alliedsignal.com...
> I want to correct again that in 8i, a transaction can extent among blocks,
> extents but a block will only contain one active transaction. More than
 one
> transactions can occupy an extent.
>

I'm afraid I have no idea what the above statement means, but if it means what I think it means, then we have no disagreement -I said below that transactions can only share blocks once one of them is "dead" (ie, has been committed or rolled back). And transactions sharing extents is VERY old news.

Regards
HJR
> "Howard J. Rogers" wrote:
>
> > 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 Wed Jan 17 2001 - 05:11:49 CST