Re: Choosing a rollback segment

Dave Schweisguth wrote:
>
> 99% of our transactions are small; 1% are very large. Presently we have many
> large rollback segments: many because we have many small transactions, large
> because we have those few very large transactions. It would be nice if we
> could confine those large transactions to a few large rollback segments, and
> allow the rest of the rollback segments we need for the many small
> transactions to be small.
>
> "set transaction use rollback segment", I hear you thinking. Yes, but what
> do we do if we want to be able to run multiple large transactions at once,
> and those transactions come from the same piece of code? How do we ensure
> that multiple large transactions don't use the same rollback segment?
> There's a dbms_something procedure with which one can set a rollback segment
> dynamically, but it isn't clear to me yet how we could decide which rollback
> segment to use on the fly. I may well be able to work something out with a
> little more research into the dba_ views, but would be interested to hear
> whether others have had this same problem and how they solved it.

You can dynamically take rollbacks on and off line dynamically. You could create your "everyday" rollbacks as small ones and have them identified in your INIT.ORA so that they are always brought on-line at database startup. Then create enough big rollabacks to handle the most simultaneous big jobs you'll be running. Keep them offline. Then write a script to bring the big ones online as needed and assign the transactions to the big rollback of choice.

See ALTER ROLLBACK SEGMENT.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||