Oracle8i Administrator's Reference
Release 3 (8.1.7) 64 Bit for Fujitsu Siemens ReliantUNIX

Part Number A89874-01
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

2
Tuning Oracle8i

The more your Oracle8i applications increase in complexity, the more you must tune the system to optimize performance and prevent data bottlenecks. This chapter describes how to configure your Oracle8i installation to optimize its performance. It contains the following sections:

The Importance of Tuning

Oracle8i is a highly-optimizable software product. Frequent tuning optimizes system performance and prevents data bottlenecks. Before tuning the system, observe its normal behavior using the Fujitsu Siemens ReliantUNIX tools described in the next section.

See Also:

For more information on tuning, see the Oracle8i Designing and Tuning for Performance guides. 

Fujitsu Siemens ReliantUNIX Tools

Fujitsu Siemens ReliantUNIX provides performance monitoring tools that you can use to assess database performance and determine database requirements. In addition to providing statistics for Oracle processes, these tools provide statistics for CPU usage, interrupts, swapping, paging, and context switching for the entire system.

See Also:

For more information on UNIX tools, see Oracle8i Designing and Tuning Performance. Fujitsu Siemens ReliantUNIX tools are described in the operating system documentation. 

vmstat

The vmstat utility reports process, memory, disk, and CPU activity on Fujitsu Siemens ReliantUNIX, depending on the switches you supply with the command. The following command displays a summary of system activity 8 times, at 5 second intervals:

$ vmstat -S 5 8

The following example shows output from the vmstat command:


procs      memory             page                      cpu(s)
r b w    avm    fre    si  so  pi po fr sr     in  syc   cs us sy id io
2 0 0 1548736 2346672     0   0  76  0 12  0    123 1505 1334 26  8 60  7
0s00: 0     0s01: 63    Total: 63

r b w    avm    fre    si  so  pi po fr sr     in  syc   cs us sy id io
2 0 0 1746400 2346608     0   0   0  0  0  0      8 55006 4507  2 28 70  0

0s00: 0     0s01: 1     Total: 1
r b w    avm    fre    si  so  pi po fr sr     in  syc   cs us sy id io
2 0 0 1645248 2346512     0   0   0  0  0  0     11 54336 4506  2 27 71  0

0s00: 0     0s01: 2     Total: 2
r b w    avm    fre    si  so  pi po fr sr     in  syc   cs us sy id io
2 0 0 1544096 2346512     0   0   0  0  0  0     18 54402 4487  2 26 72  0

0s00: 0     0s01: 1     Total: 1
r b w    avm    fre    si  so  pi po fr sr     in  syc   cs us sy id io
2 0 0 1643968 2346512     0   0   0  0  0  0     19 54151 4466  2 27 71  0

0s00: 0     0s01: 3     Total: 3

The w column (under procs) shows the number of potential processes that have been swapped out (written to disk). If the value is not zero, swapping is occurring and your system has a memory shortage problem. The si and so columns indicate the number of swap-ins and swap-outs per second, respectively. Swap-outs should always be zero.

sar

The sar command is used to monitor swapping, paging, disk, and CPU activity, depending on the switches you supply with the command. The following statement displays a summary of paging activity ten times, at 10 second intervals:

$ sar -p 10 10

The following example shows output from thee sar -p command.

14:14:55  atch/s  pgin/s ppgin/s  pflt/s  vflt/s slock/s
14:15:05    0.00    0.00    0.00    0.60    1.00    0.00
14:15:15    0.00    0.00    0.00    0.10    0.60    0.00
14:15:25    0.00    0.00    0.00    0.00    0.00    0.00
14:15:35    0.00    0.00    0.00    0.00    0.00    0.00
14:15:45    0.00    0.00    0.00    0.00    0.00    0.00
14:15:55    0.00    0.00    0.00    0.00    0.00    0.00
14:16:05    0.00    0.00    0.00    0.00    0.00    0.00
14:16:15    0.00    0.00    0.00    0.00    0.00    0.00
14:16:25    0.00    0.00    0.00    0.00    0.00    0.00
14:16:35    0.00    0.00    0.00    0.00    0.00    0.00
 
Average     0.00    0.00    0.00    0.07    0.16    0.00

dkstat

The dkstat utility reports disk I/O statistics collected for each disk since boot-time. These statistics include scaled profiles of block size, disk queue length, seek distance, cylinder use, partition use, disk response time, sort queue time and response time, load average and total usage characteristics.

For instance, the following statement would display disk I/O statistics five times, and at five second intervals.

$ dkstat 5 5

The following example shows output from the dkstat command:

Average Disk Transfers (sampled every 5 seconds)

    0s011    0s040    0s042    0s043    0s044
        7        0        0        1        0
        1        0        0        2        0
        1        1        0        7        0
        1        0        0        1        0
        0        1        0        1        0

mpstat

The mpstat utility reports per-processor statistics. Each row of the table represents the activity of one processor. The first row summarizes all activity since the last system re-boot; each subsequent row summarizes activity for the preceding interval. All values are events per second unless otherwise noted. The arguments are for time interval between statistics and number of iterations.

The following example shows output from the mpstat command:

$ /usr/ucb/mpstat 1 2

  procs        memory         page       --------------Cpu 0-11---------------
r   b   w    avm    fre  pi  po  si  so  int cint fint  syc   cs  id us sy io
0   0   0 1297136 3580576 0   0   0   0    0    3    1   12    3 100  0  0  0
                          0   0   0   0    0    0    0    0    0 100  0  0  0
                          0   0   0   0    3    0    2   53    2 100  0  0  0
                          0   0   0   0    6    0    0    0    0 100  0  0  0
                          0   0   0   0    6    0    1    7    2 100  0  0  0
                          0   0   0   0    6    0    0    5   12  99  0  1  0
                          0   0   0   0    4    0    0    0    0 100  0  0  0
                          0   0   0   0    0    0    0    2    1 100  0  0  0
                          0   0   0   0    0    0    3  106    7  99  0  1  0
                          0   0   0   0    0    0    0    0    0 100  0  0  0
                          0   0   0   0    0    0    3   29    3 100  0  0  0
                          0   0   0   0    0    0    3   25    2  99  1  0  0
  procs        memory         page       --------------Cpu 0-11---------------
r   b   w    avm    fre  pi  po  si  so  int cint fint  syc   cs  id us sy io
1   0   0 984576 3580512  0   0   0   0    5    4    0    7    3 100  0  0  0
                          0   0   0   0    0    1    0    0    0 100  0  0  0
                          8   0   0   0    0    1    2   46    4 100  0  0  2
                          0   0   0   0    0    5    0   10    4 100  0  1  0
                          0   0   0   0    0    1    1  101   20 100  0  0  0
                          0   0   0   0    0    1    1  959  153  93  3  4  0
                          0   0   0   0    2    1    1    7    1 100  0  0  0
                          0   0   0   0    6    1    0    2    1 100  0  0  0
                          0   0   0   0    6    1    1    5    1 100  0  0  0
                          0   0   0   0    6    1    2   18    2 100  0  0  0
                          0   0   0   0    6    1    2   22    2 100  0  0  0
                          0   0   0   0    6    0    2   22    3  97  1  2  0

swap

The swap -l utility reports information on swap space usage. A shortage of swap space can result in the system hanging and slow response time.

The following example shows output from the swap -l command.

swapfile             dev  swaplo blocks   free

/dev/dsk/c0t3d0s1 32,25 8 197592 162136


SQL Scripts

Oracle8i release 3 (8.1.7) includes a set of packages for database tuning called STATPACKS. For more information on STATPACKS, see Oracle8i Designing and Tuning Performance.

The utlbstat.sql and utlestat.sql scripts are used to monitor Oracle database performance and tune the System Global Area (SGA) data structures. For more information on these scripts, see Oracle8i Designing and Tuning for Performance. On Fujitsu Siemens ReliantUNIX, the scripts are located in the $ORACLE_HOME/rdbms/admin/directory.

Tuning Memory Management

Start the memory tuning process by measuring paging and swapping space to determine how much memory is available.

The Oracle buffer manager ensures that the more frequently accessed data is cached longer. Monitoring the buffer manager and tuning the buffer cache can have a significant influence on Oracle performance. The optimal Oracle buffer size for your system depends on the overall system load and the relative priority of Oracle over other applications.

Allocate Sufficient Swap Space

Try to minimize swapping because it causes significant UNIX overhead. Use the
sar -w command on Fujitsu Siemens ReliantUNIX to check for swapping.

If your system is swapping and you must conserve memory:

On Fujitsu Siemens ReliantUNIX use the swap -l command to determine how much swap space is currently in use. Use the swap -a command to add swap space to your system. Consult your Fujitsu Siemens ReliantUNIX documentation for further information.

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 sar -p command to monitor paging. The following columns from the output of this command are important:

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:

Hold the SGA in a Single Shared Memory Segment

You cannot start the database without sufficient shared memory. If necessary, reconfigure the UNIX kernel to increase shared memory.

See Also:

For more information on the SGA, see "System Global Area" and the Oracle8i Installation Guide Release 3 (8.1.7) 64 Bit Fujitsu Siemens ReliantUNIX

Tuning Disk I/O

I/O bottlenecks are the easiest performance problems to identify. Balance I/O evenly across all available disks to reduce disk access times. For smaller databases and those not using the Parallel Query option, ensure that different datafiles and tablespaces are distributed across the available disks.

Tune the Database Writer to Increase Write Bandwidth

Oracle offers asynchronous I/O, multiple DBWR processes, and I/O slaves as solutions to prevent database writer (DBWR) activity from becoming a bottleneck.

Asynchronous I/O

Asynchronous I/O allows processes to proceed with the next operation without having to wait after issuing a write and therefore improves system performance by minimizing idle time. Fujitsu Siemens ReliantUNIX supports asynchronous I/O to raw partitions, but does not support asynchronous I/O to filesystem datafiles.

I/O Slaves

I/O slaves are specialized processes whose only function is to perform I/O. They replace the Oracle7 feature, Multiple DBWRs. In fact, they are a generalization of Multiple DBWRs and can be deployed by other processes as well. They can operate whether or not asynchronous I/O is available. They are allocated memory from the LARGE_POOL_SIZE parameters, if set, otherwise they are allocated memory from shared memory buffers. I/O slaves include a set of initialization parameters that allow a degree of control over the way they operate.

Table 2-1 lists the initialization parameters that control the operation of asynchronous I/O and I/O slaves.

Table 2-1 Initialization Parameters for Asynchronous I/O and I/O Slaves  
Parameter  Range of Values  Default Value 

DISK_ASYNCH_IO 

TRUE/FALSE 

TRUE 

TAPE_ASYNCH_IO 

TRUE/FALSE 

TRUE 

BACKUP_DISK_IO_SLAVES 

TRUE/FALSE 

FALSE 

BACKUP_TAPE_IO_SLAVES 

TRUE/FALSE 

FALSE 

DBWR_IO_SLAVES 

0 - 999 

ARCH_IO_SLAVES 

0 - 999 

DB_WRITER_PROCESSES 

1-10 

There might be times when the use of asynchronous I/O is not desirable or not possible. The first two parameters in Table 2-1, DISK_ASYNCH_IO and TAPE_ASYNCH_IO, allow asynchronous I/O to be switched off respectively for disk and tape devices. Because the number of I/O slaves for each process type defaults to zero, no I/O slaves are deployed unless set.

Set the DBWR_IO_SLAVES parameter to greater than 0 if DISK_ASYNCH_IO or TAPE_ASYNCH_IO is disabled, otherwise DBWR becomes a bottleneck. In this case, the optimal value on Fujitsu Siemens ReliantUNIX for DBWR_IO_SLAVES is 4.

DB_WRITER_PROCESSES replaces the Oracle7 parameter DB_WRITERS and specifies the initial number of database writer processes for an instance. If you use DBWR_IO_SLAVES, only one database writer process is used, regardless of the setting for DB_WRITER_PROCESSES.

See "Customizing the initsid.ora File" for information on other initialization parameters.

Choose the Appropriate File System Type

Fujitsu Siemens ReliantUNIX allows a choice of file systems. File systems have different characteristics, and the techniques they use to access data can have a substantial affect on database performance. Typical file system choices are:

The suitability of a file system to an application is usually undocumented. Even different ufs file systems are hard to compare because implementations differ. Although ufs is often the high-performance choice, performance differences vary from 0 to 20 percent, depending on the file system chosen.

Monitoring Disk Performance

To monitor disk performance, use the sar -d command. Table 2-2 describes important sar -d output fields.

Table 2-2 Important sar -d Output Fields
Field  Description 

%busy 

Percentage of time device was busy performing I/O requests 

avque 

Average number of I/O requests outstanding 

r+w/s 

Number of read and write I/O requests for each second 

avwait 

Average time in milliseconds that I/O requests wait idly on queue 

avserv 

Average time in milliseconds to service I/O requests (which for disks includes seek, rotational latency, and data transfer time) 

High values for %busy, avque, avwait, and avserv can indicate I/O bottlenecks. Correlate busy devices from the sar -d output with the Oracle datafiles stored on those devices. Determine which Oracle datafiles are causing the I/O bottleneck and spread these datafiles across additional devices.

Disk Performance Issues

Oracle block sizes should either match disk block sizes or be a multiple of disk block size.

If possible, do a file system check on the partition before using it for database files, then make a new file system to ensure that it is clean and unfragmented. Distribute disk I/O as evenly as possible, and separate log files from database files.

Tuning CPU Usage

The following sections describe how to tune CPU usage.

Keep All Oracle Users and Processes at the Same Priority

Oracle is designed to operate with all users and background processes operating at the same priority level. Changing priorities cause unexpected effects on contention and response times.

For example, if the log writer process (LGWR) gets a low priority, it is not executed frequently enough and LGWR becomes a bottleneck. On the other hand, if LGWR has a high priority, user processes can suffer poor response time.

Use Processor Affinity and Binding on Multi-Processor Systems

In a multi-processor environment, use processor affinity and binding if it is available on your system. Processor binding prevents a process from migrating from one CPU to another, allowing the information in the CPU cache to be better utilized. You can bind a server shadow process to make use of the cache because it is always active, and let background processes flow between CPUs.

Use a Client/Server Configuration

If your system is CPU-bound, move applications to a separate system to reduce the load on the CPU. For example, you can off-load foreground processes such as Oracle Forms to a client system to free CPU cycles on the database server system.

Use Single-Task Linking for Large Exports/Imports and SQL*Loader Jobs

If you must transfer large amounts of data between the user and Oracle8i (for example, using the export and import utilities), it is efficient to a use single-task architecture. To make the single-task import (impst), export (expst), and SQL*Loader (sqlldrst) executables, use the ins_rdbms.mk makefile in the $ORACLE_HOME/rdbms/lib directory. The following example makes the impst, expst, and sqlldrst executables:

$ cd $ORACLE_HOME/rdbms/lib 
$ make -f ins_rdbms.mk singletask


Note:

Linking Oracle executables as a single-task allows a user process to directly access the entire SGA. Running single-task executables requires more memory because the Oracle executable text is no longer shared between the front-end and background processes. 


Tuning Oracle Resource Contention and UNIX Kernel Parameters

You can improve performance by keeping the UNIX kernel as small as possible. The UNIX kernel typically pre-allocates physical RAM, leaving less memory available for other processes such as Oracle.

Traditionally, kernel parameters such as NBUF, NFILE, and NOFILES were used to adjust kernel size. However, most UNIX implementations dynamically adjust those parameters at run time, even though they are present in the UNIX configuration file.

Look for memory-mapped video drivers, networking drivers, and disk drivers that could be de-installed, freeing more memory for use by other processes.


Note:

Remember to make a backup copy of your UNIX kernel. See your operating system documentation for additional information. 


Tuning and Specifying Block Size and File Size

This section describes how you can improve the performance of Oracle8i by optimizing the size of Oracle blocks for the files in your database.


Note:

To change block size, you must create a new database. To determine the most efficient configuration, experiment with block size before transferring your data to the new database. 


On Fujitsu Siemens ReliantUNIX, the default Oracle block size is 2 KB and the maximum block size is 16 KB. You can set the block size to any multiple of 2 KB up to a maximum of 16 KB, inclusive.

The optimal block size is typically the default size, however, it depends on the applications. To create a database with a different Oracle block size, add the following line to the initsid.ora file before creating the database:

db_block_size=new_block_size


Note:

The value that you choose for the DB_BLOCK_SIZE parameter determines the maximum size of certain types of Oracle files. See "Oracle-Specific File Size Limits"1-5 for more information on file size limits. 


Tuning the Fujitsu Siemens ReliantUNIX Buffer Cache Size

To take full advantage of raw devices, adjust the size of the Oracle8i buffer cache and, if memory is limited, the Fujitsu Siemens ReliantUNIX buffer cache:

The Fujitsu Siemens ReliantUNIX buffer cache holds blocks of data in memory while they are being transferred from memory to disk, or vice versa.

The Oracle8i buffer cache is the area in memory that stores the Oracle database buffers. Since Oracle8i can use raw devices, it does not need to use the Fujitsu Siemens ReliantUNIX buffer cache.

If you use raw devices, you must increase the size of the Oracle8i buffer cache. If the amount of memory on the system is limited, make a corresponding decrease in the Fujitsu Siemens ReliantUNIX buffer cache size. It is possible to increase or decrease the Oracle8i Buffer Cache by modifying the DB_BLOCK_BUFFERS parameter in the initsid.ora file and restarting the instance.

Use the sar command to determine which buffer caches you must increase or decrease. Table 2-3 shows the options of the sar command.

Table 2-3 sar Command options  
Option  Description 

-b 

Reports the Fujitsu Siemens ReliantUNIX buffer cache activity 

-w 

Reports the Fujitsu Siemens ReliantUNIX swapping activity 

-u 

Reports CPU utilization 

-r 

Reports memory utilization 

-p 

Reports the Fujitsu Siemens ReliantUNIX paging activity 

Adjusting Cache Size

To adjust the cache size, perform one of the following:

Using Trace and Alert Files

This section describes the trace (or dump) and alert files Oracle8i creates to diagnose and resolve operating problems.

Trace File Names

The file name format of a trace file is processname_sid_unixpid.trc, where:

processname

Is a three- or four-character process name showing which Oracle8i process the trace file is from (for example, pmon, dbwr, ora, or reco)

sid

Is the instance system identifier

unixpid

Is the UNIX process ID number

.trc

Is a filename extension appended to all trace file names

Alert Files

The alert_sid.log file is associated with a database and is located in the directory specified by the initsid.ora parameter
BACKGROUND_DUMP_DEST. The default directory is
$ORACLE_HOME/rdbms/log.

Using Raw Devices/Volumes

This section describes the use of raw devices on Oracle8i.

Disadvantages of Raw Devices/Volumes

Raw devices/volumes have the following disadvantages when used on Fujitsu Siemens ReliantUNIX:

Guidelines for Using Raw Devices/Volumes

In addition to the disadvantages described in the previous section, you should consider the following issues when deciding whether to use raw devices/volumes:

Oracle8i Parallel Server Installation

Each instance of OPS has individual 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 Fujitsu Siemens ReliantUNIX cluster.

UNIX clusters do not provide access to a shared file system between all nodes of a cluster. As a result, all files associated with a database must be built on raw devices/volumes.

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 the raw disk partitions are already formatted, match datafile size to partition size as closely as possible to avoid wasting space.

Logical Volume Manager

With logical volumes, you can create logical disks based on raw partition availability. Because logical disks can be moved to more than one disk, the disk drives do not have to be reformatted to obtain logical disk sizes.

See Also:

To review the latest features of the Fujitsu Siemens ReliantUNIX Logical Volume Manager, see the XVM Volume Manager Administrator's Guide.

 

Dynamic Performance Tuning

You can optimize disk performance when the database is online by moving hot spots to cooler drives.

Most hardware vendors who provide the logical disk facility also provide a graphical user interface that can be used 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 re-synchronization is possible.

For Oracle Parallel Server, 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 system of a UNIX cluster. The latter allows for all files associated with the Oracle Parallel Server to be placed on these shared logical volumes.

Raw Device Setup

Consider the following items when creating raw devices:


Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index