Re: Rollback Segment Trouble

: to fix the problem 1) increase the PCTINCREASE value in storage clause of
: the rollback tablespace or 2) set a higher INITIAL value in the storage
: clause of the rollback tablespace. The latter will require you to drop the
: table space and recreate it. The PCTINCREASE change will take effect the
: next time a transaction is run. Warning: there is a point where larger
: extents will hurt performance so don't carried away with your INITIAL
: value.

The best way to think about rollback segments is to think of all your rollback segments as a big set of extents. For practical purposes, a transaction can begin in any one of these extents. Therefore, i don't see any purpose to having extents not be all the same size. Hence, i always use (INITIAL=x NEXT=x PCTINCREASE=0). This can be set when creating the rollback segement or as the default storage parameter for the rollback segment tablespace(s). By the way, you can use ALTER TABLESPACE to change the default storage parms, you don't have to re-create the tablespace.

If maxextents was reached, the obvious solution is to increase the INITIAL and NEXT parameters to give you larger extents when re-creating the segments . A less obvious solution is to examine why so much rollback is needed. Often a runaway rollback segment is the result of a transaction holding it up (i.e. sombody went to lunch in the middle of a transaction), resulting in the need for all other transactions to allocate new extents rather than re-using existing ones.

As i've mentioned before, there isn't usually an obvious value to use for size or number of rollback segments. Tuning is usually needed.

: If you have access to Oracle's RTSS (Real Time Support System) there were
: some articles about this problem for version 6 database (refer to: RTSS Doc
: #100597.267). This is a very old document, I am sure there have been
: updates for V7 by now.

The RTSS bulletin board is one of the best resources out there (i used to work for Oracle so i'm biased though). And it's FREE to all supported customers.

Barry Received on Wed May 18 1994 - 22:53:37 CEST