Re: Autoextend for rollback

Thanks everyone for all of the good feedback on this item. The Oracle documentation didn't have any specific guidance. I'll file this all away for the next time I need to work with rollback segments.

Ken

Jurij Modic wrote:
>
> On Fri, 18 Jun 1999 08:58:58 -0400, Kenneth C Stahl
> <kstahl_at_lucent.com> wrote:
>
> >Has anyone ever used auto extend for tablespaces that only contain
> >rollback segments?
> >
> >For example, let's say I create a tablespace called RBS which will only
> >hold rollback segments and give it a size of 15M. I then create ten
> >rollback segments with INITIAL 100K NEXT 100K OPTIMAL 200K MINEXTENTS 2
> >MAXEXTENTS 249. Quite obviously, the first time that I have a
> >transaction which causes a rollback segment to grow towards 249 extents
> >I'll get the "snapshot too old" error message and my transaction will
> >rollback.
>
> No, when your transaction reaches the MAXEXTENTS limit of RB segment,
> you'll get the ORA-1628(max number of extents reached). If before that
> you reach the limitation of the tablespace size you'll get ORA-1650
> (unable to extend rollback segment). The ORA-1555 (snapshot too old)
> has nothing to do directly with the tablespace size or RB maxextents
> limitations, and will generally be returned to query operations, no to
> transactional operations.
>
> >However, if I give the tablespace the ability to auto extend
> >then the tablespace will grow as needed. I realize the risk here is that
> >if I have multiple transactions that are all causing the rollback
> >segments to grow to many extents that I'll end up with a collossal
> >tablespace but that is something I could always monitor and adjust as
> >needed - but at least I wouldn't be beeped in the middle of the night
> >when batch processing is going on and some job aborts because I ran out
> >of space in the RBS tablespace.
> >
> >Thoughts? Dark corners? Nooks and crannies? Convential wisdom?
>
> The conventional wisdom here is to estimate the right number of RB
> segments and also to assign them the correct physical attributes so
> that they fit your with average transaction rate/size. Extending of
> segments (RB or any other type) is quite expensive operation, so you
> have to size your RBs so that they rarely have to extend. In general,
> the waste majority of database transaction sizes and their rate can be
> predicted and estimated in advance, so you size your RBs according to
> them. However, there usually there some exceptional (mostly
> maintenance) operations that can't fit to the ordinary rollback
> segments. So it is common practice to assign a special, large
> tablespace to a special rollback segment with larger physical
> attributes. You normally keep this large RB offline, but when there is
> a need for a large transaction you put this RB online and assign the
> transaction to it (either with SET TRANSACTION or by putting all other
> RB segments offline).
>
> So, in general, for well maintained database, there should be no need
> to set the rb tablespace's datafiles to autoextend. However, I don't
> deny that sometimes this autoextend option is very comfortable,
> especially if the database is not under constant monitoring by a DBA.
>
> >Ken
>
> HTH,
>
> Jurij Modic <jmodic_at_src.si>
> Certified Oracle7 DBA (OCP)
> ================================================
> The above opinions are mine and do not represent
> any official standpoints of my employer
Received on Sat Jun 19 1999 - 09:38:22 CDT