Chapter 7   Tuning Database Servers


In this chapter section, we will discuss how to tune Oracle servers for maximum performance. We will also discuss how to tune Solaris to work with Oracle.

Tuning Oracle Servers

---

Oracle tuning is by itself a vast topic, but setting the following parameters correctly should be sufficient. Please refer to Oracle documentation for detailed information on each of the tunable configurations. This document is specifically for configuring oracle initial parameters on the Solaris® platform. These tips have been tested on Oracle 8.1.6 and later.

The first step is to set certain system shared memory pool parameters in /etc/system file. The Oracle architecture makes extensive use of shared memory segments for sharing data among multiple processes and semaphores for handling locking. The default kernel values may not be sufficient in most of the cases. The machine needs to be restarted after modifying /etc/system file. Typically these values should be sufficient, but may require some tweaking based on your system and resources:

You could use the command, dbassist, to create and tune the database. However, if the database instance is already created, then you will need to tune the parameters manually.

An Oracle server can be run in 2 modes - dedicated and shared server mode. Shared server or multi threaded mode enables many client user processes to share a small number of server processes.

All oracle initial parameters or tunable parameters are in this file:

$ORACLE_HOME/dbs/init<SID>.ora.

This file is actually a link to $ORACLE_HOME/admin/<SID>/pfile/init<SID>.ora

The values the installer would have created for the database instance may not be sufficient most of the time.

---

Caution

Take a back up of this file before editing the entries.

---

Tuning Solaris Kernel Parameters

The following paragraphs describe how to tune Solaris Kernel parameters for Oracle:

• Make sure that the Solaris kernel has parameters set sufficiently high for Oracle. The Oracle architecture makes extensive use of shared memory segments for sharing data among multiple processes and semaphores for handling locking. Many operating systems, including Solaris, do not by default offer sufficient shared memory or semaphores for maintaining an Oracle database. However, you can change kernel parameters in Solaris simply by editing the /etc/system file and restarting the server.

Table 7-1    Solaris Kernel Parameters for Oracle

Kernel Parameter	Initial Setting	Purpose
SHMMAX	4294967295	Maximum size of a single shared memory segment.
SHMMIN	1	Minimum size of a single shared memory segment.
SHMMNI	100	Maximum number of shared memory segments in entire system.
SHMSEG	10	Maximum number of shared memory segments one process can attach.
SEMMNS	2000	Maximum number of semaphores in entire system.
SEMMSL	1000	Maximum number of semaphores per set.
SEMMNI	100	Maximum number of semaphore sets in entire system.
SEMOPM	100	Maximum number of operations per semop call.
SEMVMX	32767	Maximum value of a semaphore.

The first four kernel parameters configure shared memory segments. The recommended settings shown here should be appropriate for almost any Oracle database implementation. The SHMMAX setting may seem excessive, but there is no penalty to be paid by setting SHMMAX larger than you actually need.

The last five kernel parameters configure semaphores. Each Oracle instance requires one semaphore for each process, plus ten extras. Additionally, the largest instance requires a second semaphore for each process. If you will only be setting up one database on your server, the upshot is that you will need two semaphores for each process plus ten extras.

The recommended settings for the first two semaphore kernel parameters, SEMMNS and SEMMSL, should be appropriate for most Oracle implementations. For systems with large numbers of concurrent database connections, you may need to increase these values. The recommended settings shown here for the last three semaphore kernel parameters should be appropriate for just about any Oracle database implementation.

In general, if your Solaris kernel already has any of these parameters set larger than recommended here, you should not reduce the settings. If you do change any kernel parameter settings in /etc/system, then reboot the server so that the new settings will take effect.

Add the following lines to the end of your /etc/system file:

set shmsys:shminfo_shmmax=4294967295

set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=100

set shmsys:shminfo_shmseg=10

set semsys:seminfo_semmns=2000

set semsys:seminfo_semmsl=1000

set semsys:seminfo_semmni=100

set semsys:seminfo_semopm=100

set semsys:seminfo_semvmx=32767