Oracle8i Administrator's Reference
Release 3 (8.1.7) 64 Bit for SGI IRIX

Part Number A87435_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 SGI IRIX tools described in the next section.

See Also:

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

SGI IRIX Tools

SGI IRIX 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. SGI IRIX tools are described in the operating system documentation. 

sar

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

$ sar -p 10 10

The following example shows sample output from the 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.00T
14:15:05    0.00    0.00    0.00    0.60    1.00    0.00T
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
 
Average     0.00    0.00    0.00    0.07    0.16    0.00
i

swap

Use the following command to report information on swap space usage. A shortage of swap space can result in the system hanging and slow response time:


$ swap -l

    1 /dev/swap
                   0,402    0      0  1048576  1026528  1048576        0
    2 /dev/dsk/dks0d1s3
                   0,408    0      0  7168000  7147520  7168000        0
    3 /dev/dsk/dks0d1s4
                   0,411    0      0  7168000  7151488  7168000        0
    4 /dev/dsk/dks0d1s5
                   0,414    0      0  2662400  2642240  2662400        0

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 SGI IRIX, 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 SGI IRIX to check for swapping.

If your system is swapping and you must conserve memory:

On SGI IRIX 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 SGI IRIX 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 for SGI IRIX

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. SGI IRIX supports asynchronous I/O to both raw partitions, XLV or XVM volumes, and 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 SGI IRIX 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

SGI IRIX 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:

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 as 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 the Post-Wait Driver

Oracle processes usually use semaphores to coordinate access to shared resources. If a shared resource is locked, a process suspends and waits for the shared resource to become available.

One way to improve shared resource coordination is to use a post-wait driver instead of semaphores. A post-wait driver is a faster, less expensive synchronization mechanism than a semaphore. The Oracle Post-Wait driver implements an optimized mechanism of inter-process communication, without the overhead of signal handlers or semaphores. It improves performance for Oracle8i.

To enable the Oracle Post-Wait driver, set the ORA_USE_PW environment variable to 1 before starting the database:

$ ORA_USE_PW= 1


Note:

If you intend to run more than four Oracle databases on your system, or if you intend to have more than 512 back-end server processes for a database, you must modify the /var/sysgen/master.d/postwait system file. If you modify this file, you must re-generate the UNIX kernel. See the SGI IRIX operating system documentation for information on re-generating the UNIX kernel. 


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 SGI IRIX, the default Oracle block size is 2 KB and the maximum block size is 32 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 Table 1-5 for more information on file size limits. 


Tuning the SGI IRIX Buffer Cache Size

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

The SGI IRIX 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 SGI IRIX 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 SGI IRIX 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 SGI IRIX buffer cache activity 

-w 

Reports the SGI IRIX swapping activity 

-u 

Reports CPU utilization 

-r 

Reports memory utilization 

-p 

Reports the SGI IRIX paging activity 

Adjusting Cache Size

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

Tuning Oracle8i on IRIX Systems

The following sections describe the IRIX_CPU_AFFNITY and IRIX_SCHEDULER initialization parameters.

IRIX_CPU_AFFINITY

The IRIX_CPU_AFFINITY initialization parameter enables you to specify processor affinity on an IRIX system. This parameter provides more control over system resources. The syntax is as follows:

IRIX_CPU_AFFINITY="NAME=MASK [NAME=MASK...]"

In the preceding example:

MASK

is a single CPU number or a range of CPU numbers or the special keyword 'ANY' which denotes the sysmp(2) option MP_RUNANYWHERE. Specify a range of CPU numbers in the form start_number:end_number where the start_number is less than the end_number.

NAME

is one or more of the following values:

Value  Description 

ALL 

Apply the MASK to all Oracle8i processes not included by a previously specified NAME=MASK combination. 

OTHER 

Apply the MASK to all Oracle8i processes not covered by the sys NAME. 

SYS 

Apply the MASK to any Oracle8i system processes not covered by another specified NAME=MASK combination. 

PMON 

Apply the MASK to the Process Monitor process. 

DBWR 

Apply the MASK to the Database Writer process. 

SMON 

Apply the MASK to the System Monitor process. 

CKPT 

Apply the MASK to the Checkpoint process. 

ARCH 

Apply the MASK to the Archiver process. 

RECO 

Apply the MASK to the Recovered process. 

LOCK 

Apply the MASK to the Lock Manager process. 

JOBQ 

Apply the MASK to the Job Queue process. 

AQTM 

Apply the MASK to the Advanced Queueing Time Monitor process. 

IOS 

Apply the MASK to the IO Slave processes. 


Note:

The default value is ALL=ANY 


Examples

The following specification of the IRIX_CPU_AFFINITY parameter assigns the PMON, SMON, and CKPT processes to CPU 1, the DBWR process to CPU 2, and the LOCK process is to CPU 3:

IRIX_CPU_AFFINITY="pmon=1 dbwr=2 smon=1 lock=3 ckpt=1 other=4:7"

All other Oracle8i processes are assigned in round-robin fashion (based on Oracle process id) to CPU 4 through 7.

The following specification of the IRIX_CPU_AFFINITY parameter assigns all the Oracle8i system processes to CPUs 0 through 4 in round-robin fashion based on the Oracle process id and assigns all other Oracle8i processes to CPUs 5 through 19 in round-robin fashion based on the Oracle process id:

IRIX_CPU_AFFINITY="sys=0:4 other=5:19"

IRIX_SCHEDULER

The IRIX_SCHEDULER initialization parameter allows you to specify certain IRIX scheduler options.

Default value is none.

The syntax is as follows:

IRIX_SCHEDULER="NAME=MASK [NAME=MASK...]"

In the preceding example:

MASK

is a numeric value valid for the particular NAME value

NAME

is one or more of the following values

Value  Description 

renice 

Apply the MASK as the schedctl(2) RENICE value. 

slice 

Apply the MASK as the schedctl(2) SLICE value. 

The $ORACLE_HOME/bin/oracle executable might require you to set super user ID root privilege, depending on the scheduler options used.

See the schedctl man page for the details of the schedctl command for information on the LOCK_SGA parameter.

Examples

The following examples demonstrate using the IRIX_SCHEDULER parameter:

IRIX_SCHEDULER="slice=50"

IRIX_SCHEDULER="renice=39 slice=50"

Lock the SGA in Physical Memory

The primary function of the SGA is to cache database information. If the SGA begins paging to disk, caching becomes an overhead rather than a benefit. If you have installed Oracle8i on SGI IRIX, you can use the LOCK_SGA parameter to lock the memory pages associated with the SGA. Such locking prevents paging and helps asynchronous I/O on raw files to work efficiently. Locked memory is not available for use by other applications.

This option should be used only if there is enough physical memory on the system to support the Oracle instance, the applications, and other users. If the amount of physical memory on the system is insufficient, performance degradation may occur due to increased memory paging or swapping.

To lock the SGA:

  1. Use a text editor to open the initsid.ora file.

  2. Enter the following on a blank line in the initsid.ora file:

    LOCK_SGA=TRUE
    
    
    
  3. Save and close the file.

  4. Log in as root.

  5. Make sure that the MAXLKMEM system parameter is set to at least 2050. Type the following at the command prompt:

    $ systune maxlkmem 2050
    
    
    

The following message appears:

maxlkmem=current_setting
Do you really want to change maxlkmem to 2050? 

  • If the current setting is 2050, type N to preserve the setting, otherwise type Y to change the setting to 2050.

    Any changes made in this way do not affect the kernel default setting for this parameter. For more information, type man systune(1M) at the command prompt or consult your system administrator.

    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 SGI IRIX:

    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:

    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 SGI Logical Volume Manager, see the XVM Volume Manager Administrator's Guide and the IRIX Admin: Disks and Filesystems 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.

    Some vendors also provide the ability to replace drives online in conjunction with the mirroring facility.

    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