2
Tuning for Oracle9i on UNIX

Types of Performance Bottlenecks

Performance bottlenecks are often caused by the following:

• Memory contention

  Memory contention occurs when processes require more memory than is available. When this occurs, the system pages and swaps processes between memory and disk.

• Disk I/O contention

  Disk I/O contention is caused by poor memory management, poor distribution of tablespaces and files across disks, or a combination of both.

• CPU contention

  Although the UNIX kernel usually allocates CPU resources effectively, many processes compete for CPU cycles and this can cause contention. If you installed Oracle9i in a multiprocessor environment, there might be a different level of contention on each CPU.

• Oracle resources contention

  Contention is also common for Oracle resources such as locks and latches.

Common Tools

The following sections provide information on common tools:

• vmstat

• sar

• iostat

• swap, swapinfo, swapon, and lsps

  
  

  See Also: The operating system documentation and UNIX man pages for more information on these tools.

  
  

vmstat

Use the vmstat command to view process, virtual memory, disk, trap, and CPU activity, depending on the switches you supply with the command. Enter one of the following commands to display a summary of CPU activity eight times, at five-second intervals:

• HP and Solaris:

  $ vmstat -S 5 8
  
  

• AIX, Linux, and Tru64:

  $ vmstat 5 8
  
  

The following example shows sample output from the command on Solaris:

procs     memory            page            disk          faults      cpu
 r b w   swap  free  si  so pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0   1892  5864   0   0  0  0  0  0  0  0  0  0  0   90   74   24  0  0 99
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   46   25   21  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   47   20   18  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  2   53   22   20  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   87   23   21  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   48   41   23  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   44   20   18  0  0 100
 0 0 0  85356  8372   0   0  0  0  0  0  0  0  0  0  0   51   71   24  0  0 100

The w column, under the procs column, shows the number of potential processes that have been swapped out and written to disk. If the value is not zero, swapping is occurring and your system is short of memory.

The si and so columns under the page column indicate the number of swap-ins and swap-outs per second, respectively. Swap-ins and swap-outs should always be zero.

The sr column under the page column indicates the scan rate. High scan rates are caused by a shortage of available memory.

The pi and po columns under the page column indicate the number of page-ins and page-outs per second, respectively. It is normal for the number of page-ins and page-outs to increase. Some paging always occurs even on systems with plenty of memory available.

sar

Use the sar command to display cumulative activity counters in the operating system, depending on the switches that you supply with the command. The following command displays a summary of I/O activity ten times, at ten-second intervals:

$ sar -b 10 10

The following example shows sample output from the command on Solaris:

13:32:45 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
13:32:55       0      14     100       3      10      69       0       0
13:33:05       0      12     100       4       4       5       0       0
13:33:15       0       1     100       0       0       0       0       0
13:33:25       0       1     100       0       0       0       0       0
13:33:35       0      17     100       5       6       7       0       0
13:33:45       0       1     100       0       0       0       0       0
13:33:55       0       9     100       2       8      80       0       0
13:34:05       0      10     100       4       4       5       0       0
13:34:15       0       7     100       2       2       0       0       0
13:34:25       0       0     100       0       0     100       0       0

Average        0       7     100       2       4      41       0       0

iostat

Use the iostat command to view terminal and disk activity, depending on the switches you supply with the command. The output from the iostat command does not include disk request queues, but it shows which disks are busy. This information is valuable when you must balance I/O loads.

The following command displays terminal and disk activity five times, at five-second intervals:

$ iostat 5 5

The following example shows sample output from the command on Solaris:

tty          fd0           sd0           sd1           sd3          cpu
 tin tout Kps tps serv  Kps tps serv  Kps tps serv  Kps tps serv  us sy wt id
   0    1   0   0    0    0   0   31    0   0   18    3   0   42   0  0  0 99
   0   16   0   0    0    0   0    0    0   0    0    1   0   14   0  0  0 100
   0   16   0   0    0    0   0    0    0   0    0    0   0    0   0  0  0 100
   0   16   0   0    0    0   0    0    0   0    0    0   0    0   0  0  0 100
   0   16   0   0    0    0   0    0    2   0   14   12   2   47   0  0  1 98

Use the iostat command to look for large disk request queues. A request queue shows how long the I/O requests on a particular disk device must wait to be serviced. Request queues are caused by a high volume of I/O requests to that disk or by I/O with long average seek times. Ideally, disk request queues should be at or near zero.

swap, swapinfo, swapon, and lsps

Use the swap, swapinfo, swapon, or lsps command to report information on swap space usage. A shortage of swap space can prevent processes from spawning, cause slow response times, or cause the system to stop responding. The following table lists the appropriate command to use for each platform:

Platform	Command
AIX	lsps -a
HP	swapinfo -m
Linux	swapon -s
Solaris	swap -l
Tru64	swapon -s

The following example shows sample output from the swap -l command on Solaris:

swapfile             dev        swaplo blocks   free
/dev/dsk/c0t3d0s1    32,25      8 197592        162136

Linux Tools

On Linux systems, use the free command to view information on swap space, memory, and buffer usage. A shortage of swap space can prevent processes from spawning, cause slow response times, or cause the system to stop responding.

Solaris Tools

On Solaris systems, use the mpstat command to view statistics for each processor in a multiprocessor system. Each row of the table represents the activity of one processor. The first row summarizes all activity since the last system reboot; each subsequent row summarizes activity for the preceding interval. All values are events per second unless otherwise noted. The arguments are for time intervals between statistics and number of iterations. The following example shows sample output from the mpstat command:

CPU minf mjf xcal  intr ithr  csw icsw migr smtx  srw syscl  usr sys  wt idl
  0    0   0    1    71   21   23    0    0    0    0    55    0   0   0  99
  2    0   0    1    71   21   22    0    0    0    0    54    0   0   0  99
CPU minf mjf xcal  intr ithr  csw icsw migr smtx  srw syscl  usr sys  wt idl
  0    0   0    0    61   16   25    0    0    0    0    57    0   0   0 100
  2    1   0    0    72   16   24    0    0    0    0    59    0   0   0 100

AIX Tools

The following sections list tools available on AIX systems.

HP Tools

The following sections list tools available on HP systems.

Allocate Sufficient Swap Space

Try to minimize swapping because it causes significant UNIX overhead. To check for swapping, enter the sar or vmstat commands. For information on the appropriate options to use with the sar or vmstat commands, see the man pages.

If your system is swapping and you must conserve memory:

• Avoid running unnecessary system daemon processes or application processes.

• Decrease the number of database buffers to free some memory.

• Decrease the number of UNIX file buffers, especially if you are using raw devices.

To determine how much swap space is in use, enter one of the following commands:

Platform	Command
AIX	lsps -a
HP	swapinfo -m
Linux	swapon -s
Solaris	swap -l
Tru64	swapon -s

To add swap space to your system, enter one of the following commands:

Platform	Command
AIX	chps or mkps
HP	swapon
Linux	swapon -a
Solaris	swap -a
Tru64	swapon -a

Set the swap space to between two and four times the system's physical memory. Monitor the use of swap space and increase it as required.

Control Paging

Paging might not present as serious a problem as swapping, because an entire program does not have to be stored in memory to run. A small number of page-outs might not noticeably affect the performance of your system.

To detect excessive paging, run measurements during periods of fast response or idle time to compare against measurements from periods of slow response.

Use the vmstat or sar command to monitor paging. See the man pages or your operating system documentation for information on interpreting the results for your platform. The following columns from the output of these commands are important on Solaris:

Column	Description
vflt/s	Indicates the number of address translation page faults. Address translation faults occur when a process references a valid page not in memory.
rclm/s	Indicates the number of valid pages that have been reclaimed and added to the free list by page-out activity. This value should be zero.

If your system consistently has excessive page-out activity, consider the following solutions:

• Install more memory.

• Move some of the work to another system.

• Configure the SGA to use less memory.

Adjust Oracle Block Size

A UNIX system reads entire operating system blocks from the disk. If the database block size is smaller than the UNIX file system buffer size, I/O bandwidth is inefficient. If you adjust the Oracle database block size to be a multiple of the operating system block size, you can increase performance by up to five percent.

The DB_BLOCK_SIZE initialization parameter sets the database block size. You can change the block size by recreating the database.

To see the current value of the DB_BLOCK_SIZE parameter, enter the SHOW PARAMETERS command in SQL*Plus.

Choose the Appropriate File System Type

Depending on the operating system that you use, you can choose from a range of file systems. File systems have different characteristics, and the techniques they use to access data can have a substantial impact on database performance. The following table lists typical file system choices and the platforms on which they are available:

File System	Platform	Description
S5	AIX, HP, Solaris	UNIX System V file system
UFS	AIX, HP, Solaris, Tru64	Unified file system, derived from BSD UNIX
VXFS	AIX, Solaris	Veritas file system
Raw	AIX, HP, Linux, Solaris, Tru64	Raw devices/volumes (no file system)
EXT2	Linux	Extended file system for Linux
AdvFS	Tru64	Advanced file system
CFS	Tru64	Cluster file system
JFS	AIX	Journaled file system
JFS2	AIX 5.1	Journaled file system

The suitability of a file system to an application is usually undocumented. For example, even different implementations of the Unified file system are hard to compare. Performance differences may vary from 0 to 20 percent, depending on the file system you choose.

If you choose to use a file system:

• Make a new file system partition to ensure that the hard disk is clean and unfragmented.

• Perform a file system check on the partition before using it for database files.

• Distribute disk I/O as evenly as possible.

• Separate log files from database files.

Guidelines for Using Raw Devices/Volumes

Raw devices/volumes have the following disadvantages when used on UNIX:

• Raw devices/volumes may not solve problems with file size writing limits.

  
  

  Note: To display current file size limits, enter the following command: $ ulimit -a

  
  

• Small client systems might not be able to use sufficiently large raw device/volume partitions.

• If a particular disk drive has intense I/O activity and performance would benefit from movement of an Oracle datafile to another drive, it is likely that no acceptably sized section exists on a drive with less I/O activity. It might not be possible to move files to other disk drives if you are using raw devices/volumes.

• Raw devices/volumes may be more difficult to administer than datafiles stored on a file system.

In addition to the factors described in this section, consider the following issues when deciding whether to use raw devices/volumes:

• Oracle9i Real Application Clusters installation

  Each instance of Oracle9i Real Application Clusters has it's own log files. Therefore, in addition to the partitions required for the tablespaces and control files, each instance requires a minimum of three partitions for the log files. All the files must be on disks that can be shared by all nodes of a cluster.

• Raw disk partition availability

  Use raw devices/volumes for Oracle files only if your site has at least as many raw disk partitions as Oracle datafiles. If disk space is a consideration and the raw disk partitions are already formatted, match datafile size to partition size as closely as possible to avoid wasting space.

  You must also consider the performance implications of using all of the disk space on a few disks as opposed to using less space on more disks.

• Logical volume manager

  The logical volume manager manages disk space at a logical level and hides some of the complexity of raw devices. With logical volumes, you can create logical disks based on raw partition availability. The logical volume manager controls fixed-disk resources by:

  • Mapping data between logical and physical storage

  • Allowing data to span multiple disks and to be discontiguous, replicated, and dynamically expanded

• Dynamic performance tuning

  You can optimize disk performance when the database is online by moving files from disk drives with high activity to disk drives with less activity. Most hardware vendors who provide the logical disk facility also provide a graphical user interface you can use for tuning.

• Mirroring and online disk replacement

  You can mirror logical volumes to protect against loss of data. If one copy of a mirror fails, dynamic resynchronization is possible. Some vendors also provide the ability to replace drives online in conjunction with the mirroring facility.

  For Oracle9i Real Application Clusters, you can use logical volumes for drives associated with a single UNIX system, as well as those that can be shared with more than one computer of a UNIX cluster. Shared drives allow for all files associated with the Oracle9i Real Application Clusters to be placed on these shared logical volumes.

Raw Device Setup

Keep the following items in mind when creating raw devices:

• When creating the volumes, ensure that the owner is oracle and the group is oinstall.

• The size of an Oracle datafile created in a raw partition must be at least two Oracle block sizes smaller than the size of the raw partition.

  
  

  See Also: Your operating system documentation for more information on creating raw devices, and "Setting Up Raw Devices" for more information on creating raw devices on Tru64 systems.

  
  

Trace Files

Each server and background process can write to an associated trace file. When a process detects an internal error, it writes information on the error to its trace file. The filename format of a trace file is processname_unixpid_sid.trc, where:

• processname is a three or four-character abbreviated process name identifying the Oracle9i process that generated the file (for example, pmon, dbwr, ora, or reco)

• unixpid is the UNIX process ID number

• sid is the instance system identifier

A sample trace filename is $ORACLE_BASE/admin/TEST/bdump/lgwr_1237_TEST.trc.

All trace files for background processes are written to the destination directory specified by the BACKGROUND_DUMP_ DEST initialization parameter. If you do not set this initialization parameter, the default directory is $ORACLE_HOME/rdbms/log.

All trace files for server processes are written to the destination directory specified by the USER_DUMP_DEST initialization parameter. Set the MAX_DUMP_FILE initialization parameter to at least 5000 to ensure that the trace file is large enough to store error information.

Alert Files

The alert_sid.log file stores significant database events and messages. Anything that affects the database instance or global database is recorded in this file. This file is associated with a database and is located in the directory specified by the BACKGROUND_DUMP_DEST initialization parameter. If you do not set this initialization parameter, the default directory is $ORACLE_HOME/rdbms/log.