What Is the Number and Placement of Rollback
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
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 DBA to easily manage
The size of rollback segments is based upon three
* Average number of simultaneous active DML
* 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
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
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
Figure 1.1 Single-task versus two-task or multitask
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 the tools.
* Two-task tools can reduce throughput, depending on the
machine they are installed upon. The DBA needs to consider the costs versus the
benefits when deciding whether to use single- or two-task-linked tools.
Will This Database Be Shared between Multiple
A shared database (RAC) allows a number of instances to
access the same database. This allows the DBA to spread the SGA usage for a
large database system across the CPUs of several machines. The CPUs must be part
of the same CLUSTER. In previous releases this was also known as a parallel or
shared database; in Oracle9i, it's known as Real Application Clusters, or RAC.
In order to use this option on UNIX, the disks that are
shared must be configured as raw devices. This requires what is known as a
loosely coupled system; a set of clustered Sun, HP, or Windows machines is
an excellent example. This parallel server mode has the following
* An Oracle instance can be started on each node in the
loosely coupled system.
* Each instance has its own SGA and set of detached
* All instances share the same database files and
* Each instance has its own set of redo log groups.
* The database files, redo log files, and control files
reside on one or more disks of the loosely coupled system.
* All instances can execute transactions concurrently
against the same database, and each instance can have multiple users executing
* Row locking is preserved.
Since the instances must share locks, a lock process is
started, called LCKn. In addition, the GC_ parameters must be configured in the
INIT.ORA files. In Oracle8, Oracle Corporation supplies the required DLM. Under
Oracle9i RAC, there are many changes, which we will discuss them in Chapter 14,
Distributed Database Management. If the answer to the question, Will this
database be shared between multiple instances?, is yes, the DBA needs to know
how many instances will be sharing this database. This parameter will be used to
determine INIT.ORA parameters. This answer is also important when determining
the number and type of rollback segments. Rollback segments can either be
private and only used by a single instance, or public and shared between all
instances that access the database.
The DBA will need to know the names for all instances
sharing a database. He or she should also know the number of users per instance.
Figure 1.2 illustrates the concepts of shared and exclusive mode; Oracle is
usually run in exclusive mode. Essentially, exclusive mode is the ?normal? mode
for Oracle nonshared databases.
Figure 1.2 Shared- versus exclusive-mode databases.
Will This Database Be Distributed?
A distributed database, as its name implies, has its
datafiles spread across several databases in different locations. This may
require that there be DBAs in these distributed locations. The major
consideration will be network reliability. This is especially true when
two-phase commit is used. Under two-phase commit, if one of your distributed
database nodes goes down, you can't update tables that undergo two-phase commit
with that node's data.
According to the Oracle9i Distributed Database
Administrators Guide (Oracle Corporation, 2001) the DBA needs to consider the
following items in a distributed environment:
* The number of transactions posted from each location.
* The amount of data (portion of table) used by each
* The performance characteristics and reliability of the
* The speed of various nodes and the capacities of its
* The criticality of success if a node or link is down.
* The need for referential integrity between tables.
A distributed database appears to the user to be one
database, but is in fact a collection of database tables in separate databases
spread across several locations. These databases are, of course, on different
computer systems that are connected by a network.
The computers, or nodes in a distributed database
environment, will act as both clients and servers depending upon whether they
are requesting data from another database on a different node or providing data
to a different node as it is requested.
Each site is autonomous, that is, managed independently.
The databases are distinct, separate entities that are sharing their data. The
benefits of site autonomy are:
* The various databases cooperating in the distributed
environment can mirror the local organization's needs and desires. This is
especially useful at sites where there may be two organizations that need to
share some, but not all, data. An example would be two aerospace companies
cooperating on the space platform. They may need to share data about design but
not want to share financial information.
* Local data is controlled by the local database
administrator. This limits the responsibility to a manageable level.
* Failure at one node is less likely to affect other
nodes. The global system is at least partially available as long as a single
node of the database is active. No single failure will halt all processing or be
a performance bottleneck. For example, if the Pittsburgh node goes down, it
won't affect the Omaha node, as long as Omaha doesn't require any of
* Failure recovery is on a per-node basis.
* A data dictionary exists for each local database.
* Nodes can upgrade software independently, within
As DBA you will need to understand the structures and
limits of the distributed environment if you are required to maintain a
distributed environment. The features of a two-phase commit, as well as naming
resolution and the other distributed topics, will be covered in Chapter 14.
Figure 1.3 shows a distributed database structure.