Re: Shrinking Rollback segments (Longish)

From: Brett Neumeier <neumebm_at_hpd.abbott.com>
Date: 1997/05/30
Message-ID: <338F56CB.50D2EABF@hpd.abbott.com>#1/1

Ken Hensel wrote:
> We recently upgraded to Oracle version 7.1.4 from 7.1.3. After doing
> this we noticed deleteing records caused the amount for free space in
> the tablespace containing the rollback segments as shown in
> sys.dba_free_sapce to go down. Even after commits, rollbacks and
> database restarts the space does not seem to be retrieved. [...]

You should have noticed the same behavior in 7.1.3, unless there is an unusual and major bug in 7.1.4. But this expansion of rollback segments is normal (within certain parameters).

To fix it: first, make sure that your rollback segments have an OPTIMAL size specified in their storage parameters. This is the OPTSIZE column in the V$ROLLSTAT table. V$ROLLSTAT identifies rollback segments only by USN ("Undo Segment Number"); to figure out which USN corresponds to which rollback segment, look in the V$ROLLNAME table.

If your rollback segments don't have an optimal size -- that is, their OPTSIZE value is null -- you should rebuild them. For each rollback segment, do:

ALTER ROLLBACK SEGMENT rollname OFFLINE; DROP ROLLBACK SEGMENT rollname;
CREATE ROLLBACK SEGMENT rollname TABLESPACE tblspace

        STORAGE (INITIAL 5m NEXT 5m OPTIMAL 20m); ALTER ROLLBACK SEGMENT rollname ONLINE;

The "offline" and "drop" commands will not work until there are no transactions on the rollback segment. You could use an "alter rollback segment" command instead to specify the new storage clause, but rebuilding them will reset their size in any event and is not any more difficult to do.

There is room for some confusion regarding what the OPTIMAL paramter actually does. Basically, it means that whenever a rollback segment wraps to the next extent it will de-allocate any extents which are no longer in use. That means that bouncing the database won't de-allocate space; nor will just sitting around waiting. To force a rollback segment to shrink (before release 7.2) you need to explicitly set a transaction to the rollback segment in question and then fire off a bunch of database activity; as soon as your commands make the rollback segment wrap to the next extent, the segment will shrink dynamically. I find that the best way to do this is something like,

----------------snip-------------

create table foo (col1 char(250))
tablespace bar storage (initial 1m next 1m pctincrease 0);

insert into foo values ('whatever');
insert into foo select * from foo; /* until foo has about 10000 rows */ commit;

/* then open another window with SQL*DBA rollback segment monitor, or another SQL*Plus session where you just keep looking at the V$ROLLSTAT table */

SET TRANSACTION USE ROLLBACK SEGMENT rollname; update foo set col1 = 'anything you want';

/* repeat the update until the rollback segment shrinks, then go ahead and repeat the process for other rollback segments */

-----------------snip---------------

You say that you notice the problem when deleting records, so it might also be useful to mention that deletes from INDEXED TABLES generate a LOT more rollback information than non-indexed tables do -- in my testing it appears to be about 48 times as much. If you are doing a lot of deletes, consider dropping indexes on the tables first and rebuilding them afterwards.

Hope this helps.

-bn Received on Fri May 30 1997 - 00:00:00 CDT