Oracle Sizing Rollback Segments for Long-running Jobs

Like tables and indexes, rollback segments may run out of space during processing and need to request additional extents. Dynamic extension can have a serious impact on performance, particularly on the performance of long-running jobs. By assigning larger rollback segments to long-running update jobs, you can improve performance.

If your application rollback tablespace is not large enough to hold both the small and large rollbacks simultaneously, you can assign alternative rollback segments for daily and overnight processing. You will need to offline (or drop) all of the smaller OTLP rollback segments used for high-transaction daily usage and online a smaller number of larger rollback segments for overnight processing. When the overnight jobs are completed, you can then offline the larger rollback segments and reinstate the smaller segments. You assign a particular rollback segment to a transaction with the statement

SQL>  SET TRANSACTION USE ROLLBACK SEGMENT {segment_name}

To be effective, this statement must be the first DML statement (of any kind) after a successful connect, DDL, commit, or rollback statement. You must issue this statement after each subsequent commit or rollback. The diagram below illustrates how the alternative assignment of rollback segments works.