Re: OPTIMAL and LMT's

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.

>

>