Re: about large and small rollback segments

"Phil" <phil_sheu_at_amax.com> wrote in message news:DGBm5.667$2l4.19059_at_newsread2.prod.itd.earthlink.net...
> Hi,
>
> if disk storage is not a problem, can we make every rollback segment
 larger?
>
> is there a trad-off for large rollback segment?
>
> Thanks and have a nice day!
>
> Phil
>

It's a contention thing. If you have hundreds of transactions firing off simultaneously (ie, this is an OLTP environment), though each transaction is small (and hence doesn't need vast quantities of space), there are hundreds of them all queueing up for simultaneous access to a single large rollback segment.

So, to avoid the contention, you need lots of rollback segments -then Oracle will distribute the transactions across all of them on a round-robin sort of basis. So in OLTP environments, you need lots of rollback segments (around 1 per 4 concurrent transactions is the usual advice), but they don't need to be huge.

If you do big, bulky batch update-type of transactions, the reverse applies: there usually aren't a zillion of these type of transactions firing off simultaneously, so concurrency is not an issue, but they take for ever to complete, and hence you need to have large segments to accomodate all the necessary rollback blocks.

In real life, you probably do a bit of both kinds of transaction, so you need two separate rollback tablespaces, each filled with one kind of segment (big and few, and small and lots). If you have access to the source code of your application, you can direct specific transactions to use specific rollback segments; otherwise you spend your DBA days offlining one of the tablespaces, and bringing the other on-line when the big transactions need to run.

Regards
HJR
>
Received on Fri Aug 18 2000 - 17:07:00 CDT