Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: using a Rollback Segment for all transactions

Re: using a Rollback Segment for all transactions

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 22 Nov 2002 16:25:17 +1000
Message-ID: <20jD9.81546$g9.229653@newsfeeds.bigpond.com>


"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3DDD6EEC.FE225D02_at_exesolutions.com...
> dev_at_null wrote:
>
> > On Wed, 20 Nov 2002 19:31:16 GMT, Daniel Morgan
<dmorgan_at_exesolutions.com>
> > wrote:
> >
> > > bertrand Quatromme wrote:
> > >
> > > > I work on a datawarehouse.
> > > > I have many transactions which works on multi-threaded sessions.
> > > >
> > > > I try to have all my transaction use a rollback segment which is not
> > > > always the default one.
> > > >
> > > > Actually I have only found the following command :
> > > >
> > > > set transaction use rollback segment RBS_DSS;
> > > >
> > > > But Can I do it at the session level ? or for the particular user
which
> > > > runs these transactions (at the time I create this user for example
like
> > > > when I choose the default tablespace) ?
> > > >
> > > > Thanks for all help.
> > > >
> > > > --
> > > > bertrand
> > >
> > > It would be a good thing if you just forgot about SET TRANSACTION. If
your
> > > rollback segments are properly sized and present in sufficient number
about
> > > all you can do with SET TRANSACTION is make a mess of things.
> > >
> > > But in answer to your question no you can not assign a user to a
rollback
> > > segment. SET TRANSACTION must be issued at the beginning of each
session and
> > > again immediately following every COMMIT and ROLLBACK.
> > >
> > > Daniel Morgan
> >
> > Hmm, curious. I was under the impression that set transaction had its
uses. For
> > example, in a system that usually saw a lot of oltp with the occasional
bulk
> > load. Isn't the general advice to set up a load of small rollback
segments for
> > the oltp and have a wacking great big one for the bulk work?
>
> General advice is often bad advice. What happens if that one rollback
segment is
> offline? What if it is currently being used by another transaction? What
if it is
> dropped? The list of possible issues is quite long.
>
> Size all rollback segments large enough to handle the largest transaction
on the
> system and then forget the crashes and the maintenance nightmares.

Hi Daniel,

But then suffer performance issues as excessive rollback blocks are forced to be written to disk as Oracle is no longer able to re-reference them in sufficient time when cached.

The balance between 'performance' and 'ease of maintenance' needs to be carefully considered. If rollback caching *is possible* then it should generally be encouraged, which requires few/small RBSs. If it's not possible (due to snapshot too old errors, insufficient memory or the such), then yep the balance can more easily be determined.

Cheers

Richard
>
> Daniel Morgan
>
Received on Fri Nov 22 2002 - 00:25:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US