Re: [Q] Public and private rollback segment?

Comment:

The fact that a PUBLIC rollback segment comes online at startup without altering the init.ora is a very useful feature.

I am at this moment creating a 40 gig RBS tablespace in an SAP database, to be used by a single PUBLIC rollback segment. There are certain utility operations ( such as client delete ) that delete huge amounts of data.

We don't have control of the code, and are never quite sure how much rollback is enough. There's also the problem of multiple rollback segments being used for several large transactions, and using up so much space that other segments run out of space on subsequent transactions.

By making one large PUBLIC RBS and taking the others offline, the SAP Admin cat bounce the instance to her heart's delight, and the large RBS will continue to be online, *without* touching the init.ora.

The single RBS can extend out to the full size of the tablespace if necessary, and then I can just drop it and the TBS when done.

I like not having to change the init.ora for this. One less step to deal with when the temporary large segment gets dropped.

And yes, SAP requires some tricks at times to compensate for less than optimal programming of the app.

Jared

"Tim Gorman" <Tim_at_SageLogix.com>
Sent by: root_at_fatcity.com
 10/17/2002 10:59 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: [Q] Public and private rollback segment?

This is my understanding of "private" versus "public" rollback segments: Private RBS can be brought online only via explicit ALTER ROLLBACK SEGMENT xxx ONLINE command.
The "init.ora" parameter ROLLBACK_SEGMENTS is only a list for the automatic execution of ALTER ... ONLINE command at database instance startup, so it is essentially the same as an explicit ALTER ... ONLINE command...
Public RBS can also be brought online via explicit ALTER ... ONLINE or "init.ora" parameter ROLLBACK_SEGMENTS, if you wish... Public RBS can also be brought online at instance startup according to the formula using the parameters (TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT). In an OPS/RAC environment, this allows each instance to select as many public RBS from a "pool" of public RBS. The mechanism still works even in non-OPS/RAC environments too... The use of public RBS is not restricted to OPS/RAC, but it doesn't make much sense without it...
it doesn't make much sense *with* OPS/RAC either, but that's just my opinion...
The upshot is that the only difference between public and private RBS is this additional ability to forego the onlining at instance startup via the ROLLBACK_SEGMENTS parameter or subsequently via ALTER ... ONLINE statements. Instead, at instance startup the simple formula using TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT is used. Since these are all static parameters and the difference in functionality occurs at only one point-in-time (i.e. instance startup), it really is purely a matter of style. I see no advantage to using public RBS at all...  

Corrections? Comments? Questions?  

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 17, 2002 10:09 AM

>
>
> Can't remember where I copied this from, but I believe it came from
> metalink. I don't have a version associated with it, so things might
have
> changed in v9.
>
> HTH
> Barb
>
>
> Public vs. Private Rollback Segments
> ------------------------------------
> A common misconception about `Private' rollback segments is that they
are
> segments reserved for a particular use or a particular transaction. The
only
> difference between Public and Private rollback segments is in relation
to
> the Parallel Server Option. A public rollback segment can be acquired
> implicitly by any instance in a parallel server environment. A private
> rollback segment must be explicitly acquired by a particular instance
using
> the rollback_segments parameter. If not using OPS, the difference
between
> the two is insignificant.
>
>
> > ----------
> > From: dist cash[SMTP:mccdba_at_hotmail.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Thursday, October 17, 2002 9:18 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: [Q] Public and private rollback segment?
> >
> >
> >
> >
> > We have ORACLE 8.1.7.4 running on NT. My questions are:
> >
> > 1. What difference between public rollback segment and private
segement?
> >
> > 2. what is benefit on public segment than private segment?
> >
> > 3. does public segment only use on "paraller server (RAC)"?
> >
> > Thanks.
> >
> >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).