Re: ROLLBACK_SEGMENTS definition

Thank goodness for 9i !!

(Still think your wrong, so there :)

Richard
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:GY3W8.23$N%4.127_at_news.oracle.com...
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:E36V8.27768$Hj3.85533_at_newsfeeds.bigpond.com...
> >
> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:ag2nt6$90n$1_at_lust.ihug.co.nz...
> > >
> > > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > > news:S55V8.27742$Hj3.85775_at_newsfeeds.bigpond.com...
> > > > Hi Howard,
> > > >
> > > > Oh thank goodness, we disagree on something (that sound you hear is
me
> > > > jumping around the room whilst ripping up my airline ticket to
> Tasmania
> > :)
> > > >
> > > > Public vs. Private Rollback Segments
> > > >
> > > > If I have 8 rollback segments I want to put online (because that's
> what
> > > > tuning the system has ultimately determined is most appropriate),
what
> > are
> > > > my options ? List them in the ROLLBACK_SEGMENTS parameter (note only
> the
> > > one
> > > > Hrishikesh :) which lets face it is not too hard, or use a bit of
> > > > mathematics (admittedly not most strongest point) and set the
> > TRANSACTIONS
> > > > and TRANSACTIONS_PER_ROLLBACK_SEGMENT appropriately.
> > > >
> > > > At this point, not a huge amount of difference I admit.
> > > >
> > > > But what if I specifically want my huge rollback segment online that
> my
> > > > large transactions have need with the SET TRANSACTION USE command.
How
> > can
> > > I
> > > > guarantee that it's one of the selected public rollback segments
that
> go
> > > > online with your suggestion. You can't.
> > > >
> > >
> > > You *can* mix public and private segments, you know ;-) If you've got
> this
> > > sort of mixed-load requirement, then get Oracle to bring the 'oltp'
ones
> > > online automatically, and bring the special humungous ones online
> > > yourself/via the init.ora.
> > >
> >
> > Yes you can but that further complicates the issue. Now you have to put
> some
> > rollback segments in one parameter and modify your calculations in the
> other
> > two (and remember my maths is not that good).
>
>
> Not really. If this is a mixed load, the bulk of your (public) rollback
> segments will come online automatically -and remember you don't need to be
> good at maths. Just stick transactions incredibly high and
> transactions_per_rollback_segment very low (1 springs to mind as a
suitable
> value). That forces all the segments you've created online, without too
much
> fuss. If there are insufficient public rollback segments, it makes no
> difference: the ones you;ve got are brought online anyway, and no errors
are
> generated.
>
> That leaves rollback_segments. The whole point of this is that it's mixed
> load -so you have one or two extremely large segments for bulk work. It's
> not going to be a killer to list one or two special segments in that
> parameter.
>
> This isn't very hard work.
>
> >
> > > > What if I have a number of tablespaces for my rollback segments
> (perhaps
> > > for
> > > > the above reason, perhaps for others) and I want a number from a
> > > particular
> > > > tablespace or none from another etc. to go online, how can I control
> > this
> > > > with public. You can't.
> > > >
> > >
> > > Then sort your tablespaces out! You should have made one rollback
> segment
> > > tablespace comprised of a number of datafiles, spread around the
place,
> so
> > > that the I/O is evenly distributed no matter which ones Oracle wants
to
> > > bring online.
> >
> > True. But during maintenance operations on rollback segments, I may
> > specifically not want rollback segments to be use which I can't easily
> > control through public.
> >
>
> Nope. Don't understand a word of that. You can online and offline public
> rollback segments, which have just as friendly names as private ones. You
> lose no management control over them for maintenance operations, just
> because they are public. The only difference is, you don't have to worry
> about naming each and every one of them before they are brought online at
> startup.
>
> > >
> > > > Public rollback segments in my way of thinking only really make
sense
> in
> > > an
> > > > OPS environment, but even then I would prefer the manageability
> > available
> > > > with private.
> > > >
> > >
> > > I think they make a huge amount of sense in any 'standard'
environment.
> As
> > > soon as you've got peculiar requirements, then yes, you need extra
> > > management control. The beauty is that you can have both, as needed.
> >
> > I'm a control freak and I really like to know which rollback segments
> (often
> > all of them, that's why I created them ) are actually online.
>
> All of 'em. If you created 100 of them, bring them all online. Might as
> well; they take up disk space just the same. The only difference is, you
> have to name each of the 100, I don't.
>
> >Using the
> > ROLLBACK_SEGMENTS parameter is the easiest and most flexible way to do
> this.
>
> Er, I would have thought v$rollstat might have been more flexible, just as
> informative, and hardly any more difficult than opening up an init.ora.
>
> > My database environment has increased in usage, another rollback segment
> is
> > warranted, I create it, I put it online, I update the ROLLBACK_SEGMENTS
> > parameter, I stop worry about it. Nice and simple (just like me).
> >
>
> My database environment has increased in usage, another rollback segment
is
> warranted, I create it as a public segment, I stop worrying about it. Even
> simpler (just like me).
>
> Regards
> HJR
>
>
>
> > >
> > > > Not a flame (take the jacket off), just a slight scorch with my
> > cigarette
> > > > lighter :)
> > > >
> > >
> > > You don't smoke! Owwww!!!
> >
> > True, but I need it to light the barbie on these coolish evenings in
> > (otherwise sunny) Canberra. Oh, come on it didn't hurt that much :)
> >
> > Cheers
> >
> > Richard
> >
> > >
> > > Regards
> > > HJR
> > >
> > > > Regards
> > > >
> > > > Richard
> > > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > > > news:ag2g4q$tht$1_at_lust.ihug.co.nz...
> > > > >
> > > > > "Hrishikesh Mehendale" <hrishikesh_mehendale_at_persistent.co.in>
wrote
> > in
> > > > > message news:ag1eaf$idt$1_at_news.vsnl.net.in...
> > > > > > Hi,
> > > > > >
> > > > > > I'm having a weird (?) problem, as follows:
> > > > > >
> > > > > > In my init<sid>.ora file, I have specified the following lines:
> > > > > >
> > > > > > ROLLBACK_SEGMENTS = RBS1
> > > > > > ROLLBACK_SEGMENTS = RBS2
> > > > > >
> > > > > > When I shutdown/startup the database, both (RBS1, RBS2) come
> online.
> > > > > >
> > > > > > However, if the lines are changed to either of
> > > > > >
> > > > > > rollback_segments = RBS1
> > > > > > ROLLBACK_SEGMENTS = RBS2
> > > > > >
> > > > > > ------ or -------
> > > > > >
> > > > > > ROLLBACK_SEGMENTS = RBS1
> > > > > > rollback_segments = RBS2
> > > > > >
> > > > > > only the second segment (RBS2) comes online (case sensitive ??)
> > > > > >
> > > > >
> > > > > Shouldn't be. Sure there isn't another line (even a blank one)
> between
> > > > them?
> > > > >
> > > > > If there isn't, then I suppose you ought to get the 'discoverer of
> the
> > > > > obscure bug of the month' award. But it's not really Oracle's
> fault...
> > > the
> > > > > standard advice is that if one parameter is repeated elsewhere in
> the
> > > > > init.ora, then the second version of it prevails. So in fact,
what's
> > > wrong
> > > > > is your *first* example where you appear to have two versions of
> > > > > ROLLBACK_SEGEMENTS and they are *both* respected.
> > > > >
> > > > > In other words, the standard way of referencing multiple rollback
> > > segments
> > > > > is "rollback_segments=(X,Y,Z)", on one line.
> > > > >
> > > > > Frankly (and I don my flame-prrof suit here), I've given up
worrying
> > > about
> > > > > all the rollback segment nonsense (and I'm not talking about 9i's
> > > > automatic
> > > > > undo, either). I just stick the word 'public' into every 'create
> > > rollback
> > > > > segment' statement, and watch them bring themselves online at
> startup
> > > > time,
> > > > > without referencing them in the init.ora at all:
> > > > >
> > > > > create public rollback segment R01 tablespace RBS;
> > > > >
> > > > > I've found no difference between a public and a private rollback
> > segment
> > > > in
> > > > > day-to-day usage, except the convenience of being brought online
> > > > > automatically. The only thing to watch is setting the TRANSACTIONS
> and
> > > > > TRANSACTIONS_PER_ROLLBACK_SEGMENT init.ora parameters, which
affect
> > how
> > > > many
> > > > > of the blighters get brought online at startup. But they are
single
> > > > paramete
> > > > > rs, taking simple arguments each. So it shouldn't be too hard to
> > script
> > > > > something to adjust them.
> > > > >
> > > > > Regards
> > > > > HJR
> > > > >
> > > > >
> > > > >
> > > > > > However, doing this:
> > > > > >
> > > > > > rollback_segments = RBS1
> > > > > > rollback_segments = RBS2
> > > > > >
> > > > > > brings both segments online.
> > > > > >
> > > > > >
> > > > > > I'm facing this problem on Oracle 8.1.7 EE for Solaris
(8.1.7.1.0)
> > and
> > > > for
> > > > > > Linux (8.1.7.0.1).
> > > > > >
> > > > > > Any ideas on (a) what I'm doing wrong (b) how to solve this?
> > > > > > My problem is I have to add extra rollback segment(s) to a
> possibly
> > > > > > existing declaration in the init<sid>.ora file through a script.
> > > > > >
> > > > > >
> > > > > > Thanks
> > > > > > Hrishikesh
> > > > > >
> > > > > > --
> > > > > > Hrishikesh Mehendale
> > > > > > Member of Technical Staff
> > > > > > Persistent Systems, Pune.
> > > > > > http://www.persistent.co.in/
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Tue Jul 09 2002 - 19:23:15 CDT