Re: Do I really need more than 1 rollback segment?

You need more than two segments. Your problem is that you need a space for the allocated segment to extend, but you've already filled the space in the datafile by the other rollback segment. And at a huge size which will probably never be used fully.

No information on the number of concurrent users, average rollback size etc, so I'd recommend that *as a starting point* you leave your 1gb datafile, and drop both rollback segments.

Then, assuming 10 users, create 5 rollback segments with 5m/5m/minextents 20 and see how that works. Monitor wraps/extends in the rollback segments during peak time and drop/recreate at a more appropriate initial/nextextent if necessary.

Rule of thumb: rollback segments should be as small as you can make them, with lots of extents. You don't want them too small that they're constantly extending however. Oh, and set an optimal size for them as well.

nb: I know most docs say check v$resource_limit for peak transactions and create one rbs each. In my situation, I've found that number to be around 180, yet I get by quite happily with only 8 segments with 2m/2m/20 as the settings. YMMV.

stanb_at_panix.com (Stan Brown) wrote in message news:<9ume07$p3$1_at_panix3.panix.com>...
> I'm fighting with a 7.3.4.5 instance that has sudenly decided it can't
> preform some task that it used to suced at, because of inabilty to extend a
> rollback segment.
>
> When this first started hapening I had (in addition to the system rollback
> segment) 4 rollback segments in a tablespace which was 500M in size. At
> this point in time I have doubled the tablespace to 1G, and created only 2
> rollback segments of 499M each. Since it's still failing, I am tempted to
> drop one of thes, and create a single rollback segment of 1G.
>
> This system has abotu 200 to 300 inserts that fire off every 15 seconds,
> and are comited imediatly. In addition to this it has routine processing
> which makes fairly large changes, but I have assured that only one
> transaction is open for this processing at any one time, before comittig.
>
> Given this, does it make sense to go to only one rollback segment, other
> than the system one?
Received on Thu Dec 06 2001 - 05:06:25 CST