| |
 |
|
Donald K. Burleson
Oracle Tips |
Number and Placement of
Rollback Segments
The longer a transaction, the larger
the rollback segment it will require. One is automatically created
when the database is created. This initial rollback segment is for
SYSTEM tablespace use. If you have plans for more than one
tablespace, you will need a second rollback segment. Of course, this
second segment will have to be created in the SYSTEM tablespace.
Once the ROLLBACK tablespace is defined, and additional rollback
segments are created, the second rollback segment in the SYSTEM
tablespace should be placed offline or dropped.
Each rollback segment must be created with a MINEXTENTS value of at
least 2 and a MAXEXTENTS based on the number of rollback segments in
the tablespace, the size specified for each extent, and the size of
the ROLLBACK tablespace. Each of the extents should be the same
size; that is, initial should equal next, and pctincrease has to be
set to 0 percent (look at the STORAGE statement specification in
Appendix B in the download area for an explanation of these
parameters). If you intend to do large batch transactions, it may be
advisable to create a large rollback segment used only for batch
operations. This single large segment can be left offline until
needed, then activated and used for a specific transaction using the
SET TRANSACTION USE ROLLBACK SEGMENT command.
If you opt to use the UNDO tablespace in Oracle, rather than the
traditional rollback segments, make sure you size it according to
the required transaction load and the desired retention time for
flashback queries (more on this in the section on tuning undo
tablespaces in Chapter 12).
This is an
excerpt by Mike Ault’s book “Oracle
Administration & Management”. If you want more current Oracle tips
by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or
Ault’s Oracle Scripts Download.
|
