Number 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 DBA to easily manage these resources.

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.

Figure 1.1 Single-task versus two-task 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 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 Instances? 

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 characteristics:

* An Oracle instance can be started on each node in the loosely coupled system.

* Each instance has its own SGA and set of detached processes.

* All instances share the same database files and control files.

* 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 transactions concurrently.

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

* The performance characteristics and reliability of the network.

* The speed of various nodes and the capacities of its disks.

* 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 Pittsburgh's data.

* Failure recovery is on a per-node basis.

* A data dictionary exists for each local database.

* Nodes can upgrade software independently, within reason.

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.