3.4.

Oracle's overall performance is affected by the components that are installed, as well as by how those components are configured. A high-performance Oracle database is essential to obtaining maximum performance from transactions run against that database. This section provides general configuration guidelines and some specific recommendations for configuring SQL*Net/Net8, MTS, Parallel Query, and Parallel Server.

3.4.1. Configuration Guidelines

While every installation is different, there are some general configuration guidelines that can be applied to most databases, regardless of the components installed or the use of the particular database. These general guidelines are described in the following sections.

3.4.1.1. Check the documentation

This one may seem obvious, but it needs to be said: read the documentation. Even experienced DBAs will benefit from a quick read of the pertinent documentation before beginning an Oracle installation. We recommend that you look at the following (at least):

• Hardware-specific Installation and User's Guide (IUG)

• Server Administrator's Guide

• Release Notes (usually packed with the media)

• The README file, which is usually found on the installation media and contains last-minute information that may not be in the printed documentation.

3.4.1.2. Check resource requirements

Before beginning an installation, be certain that sufficient system resources are available. The IUG for your platform contains comprehensive information about disk storage and memory requirements. Remember that these requirements are minimums, and that the resources required may actually be higher, depending on other configuration decisions you make. For example, more memory will be required if you specify a larger SGA.

In particular, make sure there is enough disk space available on the device where you place the Oracle software (typically called ORACLE_HOME ) to load all software and ancillary files.

3.4.1.3. Check system privileges

Most operating systems require the account that is performing the Oracle installation to have certain privileges. Be sure to check the IUG for these, and make sure the system administrator has set them properly. Note that these privileges may include the right to create directories and files on specific devices.

3.4.1.4. Determine control file locations

Oracle requires at least one control file. You should require at least two, and usually more, control files. This is critical because if all copies of the control file are lost, you will be unable to mount your database. Plan to place control files on different disk devices and, where possible, on different disk controllers.

3.4.2. SQL*Net Configuration

SQL*Net (Oracle7) and Net8 (Oracle8) must be configured, usually using Oracle Network Manager or the Net8 Assistant. This is typically done after the database software is installed and after at least one Oracle instance is up and running, but the configuration should be planned in advance. Before beginning a SQL*Net/Net8 configuration, you must know:

• The types of network protocols that will be used to access Oracle in your environment

• The naming scheme you will use to identify Oracle network nodes

• The names and network locations of all servers, gateways, and MultiProtocol Interchanges in your environment

Once SQL*Net/Net8 is configured, the following files (at least) must be placed on each server:

listener.ora

Controls the operation of the SQL*Net listener process

tnsnames.ora

Maintains the relationship between logical node names (aliases) and physical locations in the network when the Oracle Names software is not used

sqlnet.ora

Controls logging of Oracle network operations (not required but highly desirable)

If you are using the Multi-Threaded Server, this fact must also be configured in the INIT.ORA file, as shown in the next section.

3.4.3. Multi-Threaded Server Configuration

The Multi-Threaded Server (MTS) is configured in the INIT.ORA file, as shown in the following sample INIT.ORA parameter settings:

mts_dispatchers="ipc,1"
mts_dispatchers="tcp,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=TEST
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=TEST))"
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(HOST=10.74.72.42)(PORT=1526))"

					  

This example will configure a Multi-Threaded Server that will handle TCP/IP connections to the TEST database. A maximum of 10 dispatchers will be started, and up to 10 server processes will be created.

Remember that each MTS process counts against the total count specified in the INIT.ORA parameter PROCESSES, as well as against the maximum processes allowed for the Oracle user at the operating system level.

3.4.4. Parallel Query Configuration

Parallel Query Option (PQO) is a powerful feature of Oracle, but in order to use it properly, the database must be configured properly. Parallel Query allows multiple CPU systems to divide certain database tasks (usually full table scans) into several pieces that can be executed at the same time (in parallel). In order to perform this task, the following are required:

• Multiple parallel processes must be permitted by setting the INIT.ORA parameter PARALLEL_MAX_SERVERS to a value greater than 0.

• Tablespaces must be created using multiple datafiles, which should be allocated to separate devices. Ideally, there will be as many devices allocated to each tablespace as there are CPUs in the system.

• Tables taking advantage of Parallel Query should have their degree of parallelism set (using the PARALLEL clause in the CREATE TABLE statement) to the number of datafiles comprising the tablespace in which the table is created.

3.4.5. Parallel Server Configuration

In order to utilize Oracle Parallel Server (OPS), which allows a single Oracle database to be shared by multiple Oracle instances, you must carefully specify the Parallel Server characteristics using INIT.ORA parameters on each participating instance, including:

PARALLEL_SERVER

Must be set to TRUE to enable the Oracle Parallel Server (Oracle8 only).

INSTANCE_NUMBER

Identifies the instance to the database.

ROLLBACK_SEGMENTS

Specifies the private rollback segments to be used by each instance. Public rollback segments can also be specified, but this is not necessary.

THREAD

Identifies the redo log thread to be associated with the instance.

GC_DB_LOCKS

The total number of instance locks (Oracle7 only).

GC_FILES_TO_LOCKS

The number of database file locks.

GC_LCK_PROCS

The total number of distributed locks.

GC_ROLLBACK_LOCKS

The total number of rollback locks.

GC_SAVE_ROLLBACK_LOCKS

The number of rollback save locks (Oracle7 only).

GC_SEGMENTS

The maximum number of segments that may have activities impacting space management performed on them simultaneously (Oracle7 only).

INSTANCE_GROUPS

Assigns the instance to one or more specified groups (Oracle8 only).

LM_LOCKS

The number of locks that will be configured for the lock manager (Oracle8 only).

LM_PROCS

The number of processes for the lock manager (Oracle8 only).

LM_RESS

The number of resources that can be locked by each lock manager instance (Oracle8 only).

OPS_ADMIN_GROUP

Assigns the instance to a group for monitoring (Oracle8 only).

PARALLEL_INSTANCE_GROUP

Identifies the parallel instance group to be used for spawning parallel query slaves (Oracle8 only).

ROW_LOCKING

Should be set to ALWAYS.

SERIALIZABLE

Should be set to FALSE (Oracle7 only).

SINGLE_PROCESS

Should be set to FALSE (Oracle7 only).

Additional information on these parameters can be found in Chapter 12. Because Oracle Parallel Server is a very complex product, you should consult the Oracle Parallel Server Concepts and Administration Guide before attempting to configure a Parallel Server environment. Here are a few points to keep in mind when doing this configuration:

• On Unix platforms, all datafiles must be created in raw partitions.

• When creating a database, only redo thread 1 is created automatically; additional threads must be explicitly created, and you must specify which thread a redo log belongs to.

• Although not required, ensuring that the instance number and thread number are the same will avoid confusion.