Re: Rollback segments not shrinking back to OPTIMAL size!

Brian:

On the Optimal size issue, see the Oracle Server Concepts manual (For 7.2 its in Chapter 3 [Data Blocks, Extents and Segments] on page 3-22).

Basically, when a transaction needs to continue writing rollback info, if consecutive, INACTIVE extents follow the current extent in the SAME ROLLBACK SEGMENT, Oracle will begin shrinking the segment down to its optimal size, deallocating the oldest extents first.

Note that once a transaction begins using a rollback segment, it cannot use a different segment. That means that you have to size your you rollback tablespace to be able to accomodate the the high water marks of all your rollback segments. You also need to size the Initial and Next extents so that you do not hit the maximum number of extents per segment which is a function of the DB_BLOCK_SIZE (121 with a 2k block size and 505 with an 8K block size).

However, you DO have the option of assigning long running transactions to a specific rollback segment. Typically, the documentation will refer to a JUMBO rollback segment that is specifically reserved for long running transactions. In practice, most installation will have a large, dedicated tablespace that contains multiple large rollback segments just for long running Batch jobs.

To assign your long running transaction to a specific large rollback segment, use:

     SET TRANSACTION USE ROLLBACK SEGMENT Jumbo_RBS

If you have multiple, long running transactions running concurrently, then you should have multiple, large rollback segments available.

To create a large tablespace to hold the Jumbo_RBS1, Jumbo_RBS2, etc:

    CREATE TABLESPACE JUMBO_TS ...
    CREATE ROLLBACK SEGMENT Jumbo_RBS1 TABLESPACE JUMBO_TS ...

Hope this helps.

In article <320F53F7.D24_at_cincom.com>, bbiggs_at_cincom.com says...
>
>All,
>
>
>
>We have observed that with some of our application's long-running batch
>jobs, the rollback segments do not always shrink back to their OPTIMAL
>size. The way I understand rollback segments is that you are not
>guaranteed that a particular transaction will use a particular rollback
>segment. So, a transaction may cause a rollback segment to grow rather
>large, then commit, and the next transaction may not use the same
>rollback segment. Especially if the batch job is the only thing running
>in the database.
>
>
>
>We are using Oracle 7.1.6.2 on HP-UX 10.01. We have one rollback
>segment tablespace with 4 rollback segments, INITIAL and NEXT extents
>5MB and OPTIMAL 20MB. When the batch job runs, one rollback segment
>grows very large (~50MB) before the job does the first commit. Then it
>resumes processing, and appears to use another rollback segment, which
>grows to ~170MB, and commits again. Eventually one of the rollback
>segments gives an ORA-01650 error because it runs out of room in the RBS
>tablespace. However, the other rollback segments are still at their
>expanded sizes of 50MB and 170MB.
>
>
>
>QUESTIONS
>
>---------
>
>1) Why didn't the rollback segments shrink back to their optimal size of
>20MB?
>
>2) What events trigger a rollback segment to rollback? Is it
>time-based?
>
>
>
>Thanks,
>
>Brian
>
>
>
>--
>
>Brian M. Biggs mailto:bbiggs_at_cincom.com
>
>Cincom Systems, Inc. voice: (513) 677-7661
>
>http://www.cincom.com/
Received on Wed Aug 14 1996 - 00:00:00 CEST