Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris
Part Number A90347-02
 Contents |
 Index |
| Oracle9i Administrator's Reference Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris Part Number A90347-02 |
|
The more your Oracle9i 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 Oracle9i installation to optimize its performance. It contains the following sections:
For more information on system tuning, see the Oracle9i Database Performance Guide and Reference. For additional tuning information see the following appendices:
See Also:
Oracle9i 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-node computer systems, most of the performance tuning tips provided here are also valid when using Oracle9i Real Application Clusters and features available with Oracle9i.
Before tuning the system, observe its normal behavior using the tools described in "Operating System Tools".
Performance bottlenecks are often caused by the following:
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 is caused by poor memory management, poor distribution of tablespaces and files across disks, or a combination of both.
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.
Contention is also common for Oracle resources such as locks and latches.
Several operating system tools are available to help you 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, context switching, and I/O for the entire system.
The following sections provide information on common tools:
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:
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 indicate the number of swap-ins and swap-outs per second, respectively. Swap-ins and swap-outs should always be zero.
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 commands display 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
Use the iostat command to report 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.
Use the swap, swapinfo, swapon or lsps command to report information on swap space usage. A shortage of swap space can cause slow response times or even cause the system to stop responding. The following table lists the appropriate command to use for your platform:
| Platform | Command |
|---|---|
|
AIX |
|
|
HP |
|
|
Linux |
|
|
Solaris |
|
|
Tru64 |
|
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
On Linux systems, use the free command to view information on swap space, memory, and buffer usage. A shortage of swap space can result in the system hanging and slow response time.
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
The following sections list tools available on AIX systems.
The AIX System Management Interface Tool (SMIT) provides a menu-driven interface to various system administrative and performance tools. Using SMIT, you can navigate through large numbers of tools and focus on the jobs you want to execute.
The AIX Base Operation System (BOS) contains performance tools that are historically part of UNIX systems or are required to manage the implementation-specific features of AIX. The following table lists the most important BOS tools:
The AIX Performance Toolbox (PTX) contains tools for monitoring and tuning system activity locally and remotely. PTX consists of two main components, the PTX Manager and the PTX Agent. The PTX Manager collects and displays data from various systems in the configuration by using the xmperf utility. The PTX Agent collects and transmits data to the PTX Manager by using the xmserd utility. The PTX Agent is also available as a separate product called Performance Aide for AIX.
Both PTX and Performance Aide include the following separate monitoring and tuning tools:
The following sections list tools available on HP systems.
The following table lists the tools that you can use for additional performance tuning on HP 9000 Series HP-UX systems:
The following HP-UX performance analysis tools are available:
This HP-UX utility is an online diagnostic tool that measures the system's activities. GlancePlus displays how system resources are being used. It displays dynamic information about the system's I/O, CPU, and memory usage in a series of screens. You can also use the utility to monitor how individual processes are using resources.
HP Programmer's Analysis Kit (HP PAK) currently consists of two tools, Puma and Thread Trace Visualizer (TTV):
libpthread_tr.sl, in a graphical format. It allows you to view how threads are interacting and to find where threads are blocked waiting for resources.
HP PAK is bundled with the HP FORTRAN 77, HP Fortran90, HP C, HP C++, HP ANSI C++, and HP Pascal compilers.
Oracle provides a variety of tools for tuning SQL, including the V$ performance views, the EXPLAIN PLAN command, the SQL TRACE facility, the TKPROF facility, and the Autotrace report.
Start the memory tuning process by measuring paging and swapping space to determine how much memory is available. After you have determined your system's memory usage, tune the Oracle buffer cache.
The Oracle buffer manager ensures that the more frequently accessed data is cached longer. If you monitor the buffer manager and tune the buffer cache, you can have a significant influence on Oracle9i performance. The optimal Oracle9i buffer size for your system depends on the overall system load and the relative priority of Oracle over other applications.
Try to minimize swapping because it causes significant UNIX overhead. To check for swapping, enter one of the following commands:
sar or vmstat commands. See the man pages for information on the appropriate options to use with these commands.
swapinfo -m command.
If your system is swapping and you must conserve memory:
To determine how much swap space is in use, enter one of the following commands:
| Platform | Command |
|---|---|
|
AIX |
|
|
HP |
|
|
Linux |
|
|
Solaris |
|
|
Tru64 |
|
To add swap space to your system, enter one of the following commands:
| Platform | Command |
|---|---|
|
AIX |
|
|
HP |
|
|
Linux |
|
|
Solaris |
|
|
Tru64 |
|
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.
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 this command are important on Solaris:
If your system consistently has excessive page-out activity, consider the following solutions:
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.
Balance I/O evenly across all available disks to reduce disk access times. For smaller databases and those not using RAID, ensure that different datafiles and tablespaces are distributed across the available disks.
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:
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:
To monitor disk performance, use the sar -b and sar -u commands.
Table 2-1 describes the columns of the sar -b command output that are significant for analyzing disk performance.
An important sar -u column for analyzing disk performance is %wio, the percentage of CPU time waiting on blocked I/O.
Key indicators are:
bread, bwrit, pread, and pwrit columns indicates the level of activity of the disk I/O subsystem. The higher the sum, the busier the I/O subsystem. The larger the number of physical drives, the higher the sum threshold number can be. A good default value is no more than 40 for two drives and no more than 60 for four to eight drives.
%rcache column value should be greater than 90 and the %wcache column value should be greater than 60. Otherwise, the system may be disk I/O bound.
%wio column value is consistently greater than 20, the system is I/O bound.
This section provides information on tuning CPU usage.
Oracle is designed to operate with all users and background processes operating at the same priority level. Changing priority levels 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.
You can improve performance by keeping the UNIX kernel as small as possible. The UNIX kernel typically pre-allocates physical memory, leaving less memory available for other processes such as the Oracle processes.
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 can be removed, freeing more memory for use by other processes.
To take full advantage of raw devices, adjust the size of the Oracle9i buffer cache and, if memory is limited, the operating system buffer cache.
The operating system buffer cache holds blocks of data in memory while they are being transferred from memory to disk, or from disk to memory.
The Oracle9i buffer cache is the area in memory that stores the Oracle database buffers. Because Oracle9i can use raw devices, it does not need to use the operating system buffer cache.
If you use raw devices, increase the size of the Oracle9i buffer cache. If the amount of memory on the system is limited, make a corresponding decrease in the operating system buffer cache size.
Use the sar command to determine which buffer caches you must increase or decrease.
The following sections provide information on using raw devices/volumes.
|
Note: For additional raw device/volume tuning information, see the following appendices:
|
Raw devices/volumes have the following disadvantages when used on UNIX:
In addition to the factors described in this section, consider the following issues when deciding whether to use raw devices/volumes:
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.
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.
The logical volume manager manages disk space at a logical level. With logical volumes, you can create logical disks based on raw partition availability. The logical volume manager controls fixed-disk resources by:
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.
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.
Keep the following items in mind when creating raw devices:
oracle and the group is oinstall.
Refer to your operating system documentation for more information on creating raw devices. See "Setting Up Raw Devices" for more information on creating raw devices on Tru64 systems.
See Also:
This section describes the trace (or dump) and alert files that Oracle9i creates to diagnose and resolve operating problems.
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:
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. 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.
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.
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|