Полезная информация

Oracle8 Administrator's Reference
Release 8.0.5 for Intel-LINUX

A66585-02

Library

Product

Contents

Index

Prev Next

3
Tuning Oracle8 on LINUX

The Importance of Tuning

Oracle8 is a highly optimizable software product. Frequent tuning optimizes system performance and prevents data bottlenecks. Although this chapter is written from the perspective of single-processor systems, most of the performance tuning tips provided here are also valid when using the Oracle parallel options.

Before Tuning the System

Before tuning the system, observe its normal behavior using the LINUX tools described in "LINUX Tools" in the next section.

See Also:

Oracle8 Parallel Server Concepts and Administration.
Oracle8 Tuning.

 

LINUX Tools

LINUX provides performance monitoring tools that can be used 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:

LINUX tools are described in the operating system documentation.

 

vmstat

The vmstat utility reports process, virtual memory, disk, paging, and CPU activity on LINUX, depending on the switches you supply with the command. The following statement displays a summary of system activity eight times, at five second intervals:

% vmstat -n 5 8

Sample output from the vmstat command is shown in Figure 3-1.

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.

Figure 3-1 Output from the vmstat -n command

 procs                  memory    swap        io    system         cpu
 r b w  swpd  free  buff cache  si  so   bi   bo   in   cs  us  sy  id
 0 0 0 16124   964   524 29904   2  14   13   21  208    3   9   4  87
 1 0 0 16124   648   524 30140   0   0    1    0  806 1763   5   8  87
 1 0 0 16124   608   524 29904   0   0    0    0  856 1894   5   7  87
 0 0 0 16124   612   524 29624   0   0    0    5  734 1586   5   8  88
 2 0 0 16124  1656   520 28296   0   0  221    0  687 1395  14  10  77
 0 0 0 16124   840   520 29060   0   0   38    0  621 1287   3   4  93
 0 0 0 16124   856   520 29196   0   0    0    0  647 1395   4   6  91
 1 0 0 16124   708   520 29288   0   0    1    0  618 1287   3   4  93

free

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

SQL Scripts

utlbstat and utlestat SQL Scripts

The utlbstat and utlestat SQL scripts are used to monitor Oracle database performance and tune the Shared Global Area (SGA) data structures. For information regarding these scripts, see the Oracle8 Server Tuning. On LINUX, the scripts are located in $ORACLE_HOME/rdbms/admin/.

Tuning Memory Management

Start the memory tuning process by tuning 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

Swapping causes significant LINUX overhead and should be minimized. Use
free or vmstat -n on LINUX to check for swapping.

If your system is swapping and you need to conserve memory:

Procedures for adding swap space vary between LINUX implementations. On LINUX use free to determine how much swap space is currently in use. Use free to add swap space to your system. Consult your LINUX 2.0.33 documentation for further information

Start with swap space two to four times your system's random access memory (RAM). Use a higher value if you plan to use CASE, Oracle Applications, or Oracle Office. Monitor the use of swap space and increase it as necessary.

Control Paging

Paging may not present as serious a problem as swapping, because an entire program does not have to reside in memory in order to run. A small number of page-outs may 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 vmstat or free to monitor paging. The following columns are from the vmstat output are important:

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

Hold the SGA in a Single Shared Memory Segment

Although this performance gain is minor, you cannot start the database without configuring sufficient shared memory.

You may need to reconfigure the LINUX kernel to increase shared memory. The LINUX kernel parameters for shared memory include SHMMAX, SHMMNI, and SHMSEG. In order to ensure that the SGA resides in a single shared memory segment, set the value of SHMMAX to an Oracle recommended value.

The size of the SGA can be estimated using the following steps:

  1. Multiply DB_BLOCK_BUFFERS by DB_BLOCK_SIZE.
  1. Add the result of Step 1 to SORT_AREA_SIZE.
  2. Add the result of Step 2 to SHARED_POOL_SIZE.
  3. Add the result of Step 3 to LOG_BUFFER.

You can also use the LINUX utility ipcs to monitor the status of shared memory.

See Also:

"Configure LINUX Kernel for Oracle" in Chapter 2 of the Oracle8 Installation Guide for Intel-LINUX.

 

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 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. Solaris supports Asynchronous I/O to both raw and filesystem datafiles.

I/O Slaves

I/O Slaves are specialized processes whose only function is to perform I/O. They are new with Oracle8, and replace Multiple DBWRs (in fact, they are a generalization of Multiple DBWRs and can be deployed by other processes as well), and can operate whether or not asynchronous I/O is available. I/O Slaves come with a new set of initialization parameters which allow a degree of control over the way they operate. These are shown in Table 3-1.

Table 3-1 Initialization Parameters for 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

 

0

 

LGWR_IO_SLAVES

 

0 - 999

 

0

 

ARCH_IO_SLAVES

 

0 - 999

 

0

 

DB_WRITER_PROCESSES

 

1-10

 

1

 

There may be times when the use of asynchronous I/O is not desirable or not possible. The first two parameters in Table 3-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 will be deployed unless specifically set.

DBWR_IO_SLAVES should only be set to greater than 0 if ASYNC I/O (that is, DISK_ASYNCH_IO, or TAPE_ASYNCH_IO) has been disabled, otherwise DBWR will become a bottleneck. In this case the optimal value on LINUX for DBWR_IO_SLAVES should be 4. In the case of LGWR_IO_SLAVES, it is not recommended to deploy more than 9 slaves.

DB_WRITER_PROCESSES replaces the 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 will be used, regardless of the setting for DB_WRITER_PROCESSES.

Monitoring Disk Performance

To monitor disk performance, use vmstat.

An important vmstat column for disk performance is %wio, the percentage of CPU time waiting on blocked I/O.

Key indicators are:

Disk Performance Issues

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

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

Keep All Oracle Users/Processes at the Same Priority

Oracle is designed to operate with all users and background processes operating at the same priority level. Changing priorities causes 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 may suffer poor response time.

Use Processor Affinity/Binding on Multi-Processor Systems

In a multi-processor environment, use processor affinity/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 since it is always active, and let background processes flow between CPUs. Some platforms employ process binding automatically.

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

If you need to transfer large amounts of data between the user and Oracle8 (for example, using export/import), it is efficient to use single-task architecture. To make the single-task import (impst), export (expst), and SQL*Loader (sqlldrst) executables, use the ins_rdbms.mk makefile, which can be found 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 expst impst sqlldrst


Note:

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

 

Tuning Oracle Resource Contention

Tune LINUX Kernel Parameters

You can improve performance by keeping the LINUX kernel as small as possible. The LINUX 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 LINUX implementations dynamically adjust those parameters at run time, even though they are present in the LINUX configuration file.

Look for memory mapped video drivers, networking drivers, and disk drivers. They can often be de-installed, yielding more memory for use by other processes.


WARNING:

Remember to make a backup copy of your LINUX kernel. See your hardware vendor documentation for additional details.

 

Tuning Block Size and File Size


WARNING:

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

 

Specifying Oracle Block Size

On LINUX, the default Oracle block size is 2KB and the maximum block size is 16KB.

You can set the actual block size to any multiple of 2KB up to 16KB, inclusive.

The optimal block size is typically the default, but varies with the applications. To create a database with a different Oracle block size, add the following line to the initsid.ora file:

db_block_size=new_block_size

Tuning the LINUX Buffer Cache Size

To take full advantage of raw devices, adjust the size of the Oracle8 buffer cache and, if memory is limited, the LINUX buffer cache.

The LINUX buffer cache is provided by the operating system. It holds blocks of data in memory while they are being transferred from memory to disk, or vice versa.

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

When moving to raw devices, increase the size of the Oracle8 buffer cache. If the amount of memory on the system is limited, make a corresponding decrease in the LINUX buffer cache size.

The LINUX command vmstat may help you determine which buffer caches should be increased or decreased.

Adjusting Cache Size

Using Trace and Alert Files

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

Trace File Names

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

Table 3-2 Format Key to Process Name

processname

 

is a three- or four-character process name showing which Oracle8 process the trace file is from (for example, PMON, DBWR, ORA, or RECO)

 

sid

 

is the instance system identifier

 

pid

 

is the LINUX process ID number

 

.trc

 

is a file name extension appended to all trace file names

 

A sample trace file name is lgwr_TEST_1237.trc.

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 value is $ORACLE_HOME/rdbms/log.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index