Re: OPTIMAL and LMT's

I agree with Howard! Setting Optimal is a work around not the solution. Finding and fixing the rouge transactions that cause the balloon, so that they either don't balloon or are assigned to a large rollback segment to use is the solution.

If you let Oracle shrink rollback segments automatically back to optimal you run a much higher risk of other transactions failing to find information needed for a consistent read. The data needed was there a second ago but oops Oracle shrunk the rollback too bad I'll issue an "ORA-01555: snapshot too old" instead of finishing the users query.

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:3bf18d47$0$5065$afc38c87_at_news.optusnet.com.au...
> You mean you let rip a vast transaction, which completes, but in the
process
> has caused a segment to balloon in size?
>
> Not a particular problem: the extents that transaction used will be made
> avaialble to new transactions in exactly the same way as any other
> transaction's extents would be. Oracle assigns new transactions to the
> segment which contains the fewest number of active transactions. A huge
> unused segment is just as likely to be picked in that scenario as a small
> unused segment.
>
> I'm sure that the algorithm has some subtleties about it that might mean
> it's not perfect -but in that case, the worst that can happen is that
you've
> traded some disk space for performance. Sounds like a reasonable deal to
> me!
>
> I don't recommend letting ballooned segments *stay* ballooned for
eternity,
> in any case. Just that an 'alter rollback segment blah shrink to X' can
be
> issued when it suits you (i.e., when the database is relatively quiet),
> rather than have the shrink take place when you least desire it (i.e., in
> the middle of a new transaction).
>
> In any case, if you have huge transactions starting in small rollback
> segments, performance is likely to be sub-optimal in the first place,
since
> extent acquisition will have to take place when the transaction loops back
> on top of itself within the segment. That sounds like bad design, bad
> DBAing or both. Judicious use of onlining and offlining of rollback
> tablespaces or segments should allow you to start any transaction and
> guarantee which rollback segment it will start in (and thus complete in).
>
> It should be possible, for example, to run a script just prior to a known
> huge transaction that offlines every rollback segment in the system, and
> onlines a large segment. Start the transaction, and immediately reverse
the
> process. Downtime to users engaged in small DML will be minimal, and the
> large transaction must do its stuff in the large segment that is now
locked
> out from other transactions (because it will be in the PENDING OFFLINE
> state).
>
> Obviously, "set transaction use rollback segment..." would be better, but
as
> you say, not everybody can tinker with the code that omits this command.
> DBA scripts can fill the gap, though, to some extent at least.
>
> Regards
> HJR
> --
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "Raymond" <r_h_at_hetnet.nl> wrote in message
> news:9srusi$q0j$1_at_news1.xs4all.nl...
> >
> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:3bf16d2d$0$5065$afc38c87_at_news.optusnet.com.au...
> > > OPTIMAL is never a good idea, whatever sort of tablespace you've got,
if
> > you
> > > value performance.
> >
> > But what if you have huge transactions occasionally? Won't
> > one of the RB segments in the RB tablespace take up almost all
> > space, without ever making it available to the other segments?
> > If you have such transactions, setting OPTIMAL for all
> > your RB segments seems to be the only option.
> > It isn't always possible to direct such transactions to a specific
> > rollback segment...
> >
> > Raymond.
> >
> >
>
>
Received on Tue Nov 13 2001 - 16:37:34 CST