Re: Rollback segments not shrinking back to OPTIMAL size!

Brian M. Biggs wrote:
>
> 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/The optimal settings are not triggered until the next extent is required
or reached for that rollback segment. ie. You must go over the 5m next extent before Oracle wakes up and restores the free space.

You can manually release the free space by using the following as sys.

define ts_name = &tablespace_name
set heading off
set feedback off
set termout off
set verify off
spool /tmp/coal.sql
select 'alter session set events ''immediate trace name coalesce level '||ts#||''';'
from ts$
where name like upper('%&ts_name%')
/
spool off
set heading on
set feedback on
set termout on
set verify on
_at_/tmp/coal.sql

Cheers Stu Received on Fri Aug 16 1996 - 00:00:00 CEST