Re: Rollback segments not shrinking back to OPTIMAL size!

I assume you have a long running update process and after X rows you commit. That commits your update data but since your query is guarranted read consistency your rollback also contains the state of your tables for your query to perform. The rollback is preserving the state of all tables used in a query. I experienced this first hand when I was building tables from views and was committing every 10000 rows. I was getting the same errors and it was because the rollback was preserving the state for my query.

You can dedicate a large rollback to your transaction/batch job by using 'SET TRANSACTION'. Also other transactions can use the rollback segment that you are using for batch processing.

Good luck,
Rich Bernat
Chevron.Corp

In <320F53F7.D24_at_cincom.com>, "Brian M. Biggs" <bbiggs_at_cincom.com> writes:
>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 Tue Aug 13 1996 - 00:00:00 CEST