Previous Topic: Rollback SegmentsNext Topic: Sample Files

Oracle Tuning Recommendations › Storage Parameters › Set Optimal Configuration for the Rollback Logs

Set Optimal Configuration for the Rollback Logs

For large transactions, rollback segments may need to allocate new extents as the transaction continues. When the transaction ends, a properly configured Oracle database releases the additional rollback extents so that they can be used for other rollback segments. To ensure that the additional rollback extents are released, configure the OPTIMAL parameter for each rollback segment.

To set the optimal configuration for the rollback logs of large databases

  1. Log in to Oracle as SYS or SYSTEM and run the following query and check the result to view the current configuration of the Rollback Segments: 
    SQLWKS> select SEGMENT_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, STATUS from dba_rollback_segs;

    The result is:

    SEGMENT_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT STATUS
SYSTEM	  51200 	51200		2	121 	 ONLINE
RB_TEMP	  102400	102400		2	121	 OFFLINE
RB1		  2097152	2097152		2	121	 ONLINE
RB2		  2097152	2097152		2	121	 ONLINE
RB3		  2097152	2097152		2	121	 ONLINE
RB4		  2097152	2097152		2	121	 ONLINE
RB5		  2097152	2097152		2	121	 ONLINE
RB6		  2097152	2097152		2	121	 ONLINE
RB7		  2097152	2097152		2	121	 ONLINE
RB8		  2097152	2097152		2	121	 OFFLINE
RB9		  2097152	2097152		2	121	 OFFLINE
RB10	  2097152	2097152		2	121	 OFFLINE
RB11	  2097152	2097152		2	121	 OFFLINE
RB12	  2097152	2097152		2	121	 OFFLINE
RB13	  2097152	2097152		2	121	 OFFLINE
RB14	  2097152	2097152		2	121	 OFFLINE
RB15	  2097152	2097152		2	121	 OFFLINE
RB16	  2097152	2097152		2	121	 OFFLINE
18 rows selected.

    Note: In this example, there are sixteen rollback segments with 2 MB INITIAL EXTENT, 2 MB NEXT EXTENT, and MAX EXTENTS of 121 MB.

  2. Run the following query for each Rollback Segment: 
    alter rollback segment rb1 offline;
 drop rollback segment rb1;
 create public rollback segment rb1
tablespace rollback_data
storage (initial 6M
next 6M
minextents 2
maxextents 121
optimal 12M);
 alter rollback segment rb1 online;
 alter rollback segment rb2 offline;
 drop rollback segment rb2;
 create public rollback segment rb2
tablespace rollback_data
storage (initial 6M
next 6M
minextents 2
maxextents 121
optimal 12M);
 alter rollback segment rb2 online;
 
<Repeat for each Rollback Segment>

    This query sets the optimal Rollback Segment configuration to 6 MB INITIAL EXTENT, 6 MB NEXT EXTENT, 2 MB MIN EXTENT, 121 MB MAX EXTENT, and 12 MB OPTIMAL.

  3. Run the following query to verify the Rollback segment configuration changes: 
    SQLWKS> select SEGMENT_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,MAX_EXTENTS, STATUS from dba_rollback_segs;

    The result is:

    SEGMENT_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT STATUS
SYSTEM	  51200 	51200	    2	     121    	ONLINE
RB_TEMP	  102400	102400	    2	     121    	OFFLINE
RB1		  6291456	6291456	    2	     121    	ONLINE
RB2		  6291456	6291456	    2	     121    	ONLINE
RB3		  6291456	6291456	    2	     121    	ONLINE
RB4		  6291456	6291456	    2	     121    	ONLINE
RB5		  6291456	6291456	    2	     121    	ONLINE
RB6		  6291456	6291456	    2	     121    	ONLINE
RB7		  6291456	6291456	    2	     121    	ONLINE
RB8		  6291456	6291456	    2	     121    	ONLINE
RB9		  6291456	6291456	    2	     121    	ONLINE
RB10	  6291456	6291456	    2	     121    	ONLINE
RB11	  6291456	6291456	    2	     121    	ONLINE
RB12	  6291456	6291456	    2	     121    	ONLINE
RB13	  6291456	6291456	    2	     121    	ONLINE
RB14	  6291456	6291456	    2	     121    	ONLINE
RB15	  6291456	6291456	    2	     121    	ONLINE
RB16	  6291456	6291456	    2	     121    	ONLINE
18 rows selected.

Tablespaces

To improve performance you can create more than one tablespace for indexes. Then, after you install the software, you can move some indexes to the other tablespaces.

The following table lists the suggested initial sizes for DATA and INDEX tablespaces:

Tablespace

Small

Medium

Large

DATA

200 MB

500 MB

1 GB

INDEX

200 MB

250 MB

500 MB

DATA and INDEX tablespaces with these initial sizes can accommodate at least ten models or versions.