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 SEGMENT

This will yield the number of rollback segments needed. They should be sized to handle the maximum expected data manipulation language (DML) transaction.

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 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 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.

Tip

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 the tools.
• 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.