||Oracle Tips by Burleson
Number and Placement of Rollback Segments
Another item controlled by the number of data manipulation
language users (INSERT, UPDATE, and DELETE commands) and the
transaction load on the system is the number of rollback segments.
The formula, as stated before, is:
NUMBER OF DML TRANSACTIONS / NUMBER OF TRANSACTIONS PER ROLLBACK
This will yield the number of rollback segments needed. They should
be sized to handle the maximum expected data manipulation language (DML)
The placement of rollback segments is decided based upon resource
contention prevention. Put them where they won’t cause contention
with other Oracle files. Transactions are spread across all active
rollback segments. Usually, it is a good idea to locate the rollback
segments in a tablespace or tablespaces dedicated to rollback
segments. This allows the Remote DBA to easily manage these resources.
Oracle rollback segments
The size of rollback segments is based upon three factors:
- Average number of simultaneous active DML transactions.
- Average number of bytes modified per transaction.
- Average duration of each transaction.
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
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 Oracle9i, 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).
Will the Tools Be Linked Single-Task or Be Independent (Two-Task)?
This question deals with the way the Oracle tools, such as SQLLOADER,
IMP, or EXP, address the Oracle kernel. See Figure 1.1 for a
graphical demonstration of the concept of single-task versus a two-
or multitask structure.
If the tools are linked single-task, they address a specific node’s
Oracle kernel by default. To access another node or another system,
a connect string must be used (connect strings will be covered in
Chapter 14, Managing in a Distributed Environment). This mode is
useful for a single-node database situation and saves on memory and
task usage. This is generally used where a client/server
architecture is not used. It has been demonstrated that relinking
some tools single-task, such as the import and export utilities,
will increase their performance by up to 30 percent.
Single-task linking will be allowed only in preOracle9i releases, so
plan now to remove it from use.
If the tools are linked independent, or two-task, a connect string
must always be used. It is called “two-task” because the tools must
run as one task while the Oracle executable runs as another.
Two-task is generally used in a client/server situation. This allows
the following benefits:
Client machines to perform CPU-intensive tasks, offloading these
tasks from the server.
- Movement of tools from one environment to another (such as
from a development area to a production one) without relinking.
- The Oracle8i server to be relinked without relinking all of
- Two-task tools can reduce throughput, depending on the
machine they are installed upon. The Remote DBA needs to consider the
costs versus the benefits when deciding whether to use single-
or two-task-linked tools.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.