Release 2 (8.1.6) for Sun SPARC Solaris
A77184-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Reference Release 2 (8.1.6) for Sun SPARC Solaris A77184-01 |
|
Oracle8i 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 parallel options and features available with Oracle8i.
Before tuning the system, observe its normal behavior using the Solaris tools described in "Solaris Tools" in the next section.
Solaris 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.
The vmstat utility reports process, virtual memory, disk, paging, and CPU activity on Solaris, 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
Sample output from the vmstat command is shown in Figure 2-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.
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
Sample output from the sar -p command is shown in Figure 2-2.
The iostat utility reports terminal and disk activity depending on the switches you supply with the command. The report from iostat does not include disk request queues, but it shows which disks are busy. This information is valuable when you need to balance I/O loads.
The following statement displays terminal and disk activity five times, at 5 second intervals:
$ iostat 5 5
Sample output from the iostat command is shown in Figure 2-3.
The swap -l utility reports information about swap space usage. A shortage of swap space can result in the system hanging and slow response time. Sample output from the swap -l command is shown in Figure 2-4.
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. Sample output from the mpstat command is shown in Figure 2-5.
The utlbstat and utlestat SQL scripts are used to monitor Oracle database performance and tune the System Global Area (SGA) data structures. For information regarding these scripts, see Oracle8i Designing and Tuning for Performance. On Solaris, the scripts are located in $ORACLE_HOME/rdbms/admin/.
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.
Swapping causes significant UNIX overhead and should be minimized. Use sar -w or vmstat -S on Solaris to check for swapping.
If your system is swapping and you need to conserve memory:
On Solaris use swap -l to determine how much swap space is currently in use. Use swap -a to add swap space to your system. Consult your Sun SPARC Solaris 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 Oracle Developer, Oracle Applications, or Oracle InterOffice. Monitor the use of swap space and increase it as necessary.
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 sar -p to monitor paging. The following columns from sar -p output 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:
You will not be able to start the database without sufficient shared memory. You can reconfigure the UNIX kernel to increase shared memory. For more information, see "Controlling the System Global Area" in Chapter 1.
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.
Oracle offers solutions to prevent database writer (DBWR) activity from becoming a bottleneck:
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 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), and they can operate whether or not asynchronous I/O is available. They are allocated from LARGE_POOL_SIZE if set, otherwise they are allocated from shared memory buffers. I/O Slaves come with a set of initialization parameters that allow a degree of control over the way they operate, shown in Table 2-1.
| Parameter | Range of Values | Default Value |
|---|---|---|
|
DISK_ASYNCH_IO |
TRUE/FALSE |
TRUE |
|
TAPE_ASYNCH_IO |
TRUE/FALSE |
TRUE |
|
BACKUP_TAPE_IO_SLAVES |
TRUE/FALSE |
FALSE |
|
DBWR_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 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 will be deployed unless specifically set.
DBWR_IO_SLAVES should only be set to greater than 0 if DISK_ASYNCH_IO, or TAPE_ASYNCH_IO has been disabled, otherwise DBWR will become a bottleneck. In this case, the optimal value on Solaris 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 will be used, regardless of the setting for DB_WRITER_PROCESSES.
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/Os to that disk or by I/Os with long average seek times. Ideally, disk request queues should be at or near zero.
Sun SPARC Solaris allows a choice of file systems. File systems have different characteristics, and the techniques they use to access data can have a substantial impact on database performance. Typical file system choices are:
s5: the UNIX System V File System
ufs: the UNIX File System, derived from BSD UNIX
vxfs: the Veritas File System
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.
To monitor disk performance, use sar -b and sar -u.
Important sar -b columns for disk performance are listed in Table 2-2.
|
|
blocks read and blocks written (important for file system databases) |
|
|
partition reads and partition writes (important for raw partition database systems) |
An important sar -u column for disk performance is %wio, the percentage of CPU time waiting on blocked I/O.
Key indicators are:
bread, bwrit, pread, and pwrit indicates the state of the disk I/O subsystem. The higher the sum, the greater the potential for disk I/O bottlenecks. 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 should be greater than 90 and %wcache should be greater than 60. Otherwise, the system may be disk I/O bound.
%wio is consistently greater than 20, the system is I/O bound.
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.
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.
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.
If you need to transfer large amounts of data between the user and Oracle8i (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 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
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. They can often be de-installed, yielding more memory for use by other processes.
On Solaris, the default Oracle block size is 2 KB and the maximum block size is
16 KB. You can set the actual block size to any multiple of 2 KB up to 16 KB, 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 before creating the database:
db_block_size=new_block_size
|
Note:
The value you choose for |
To take full advantage of raw devices, adjust the size of the Oracle8i buffer cache and, if memory is limited, the Solaris buffer cache.
The Solaris 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 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 Solaris buffer cache.
When moving to raw devices, increase the size of the Oracle8i buffer cache. If the amount of memory on the system is limited, make a corresponding decrease in the Solaris 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.
The Solaris command sar can help you determine which buffer caches should be increased or decreased. The sar command options are shown in Table 2-3.
This section describes the trace (or dump) and alert files Oracle8i creates to diagnose and resolve operating problems.
The format of a trace file name is processname_sid_unixpid.trc, where:
A sample trace file name is lgwr_TEST_1237.trc.
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.
Raw devices/volumes have the following disadvantages when used on Solaris:
In addition to the factors discussed under "Disadvantages of Raw Devices/Volumes", you should consider the following issues when deciding whether to use raw devices/volumes.
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 Solaris 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.
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.
If the raw disk partitions are already formatted, match tablespace size to partition size as closely as possible to avoid wasting space.
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.
Disk performance can be optimized 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.
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.
For Oracle Parallel Server, you can use logical volumes for drives associated with a single UNIX machine, as well as those that can be shared with more than one machine of a UNIX cluster. The latter allows for all files associated with the Oracle Parallel Server to be placed on these shared logical volumes.
Keep in mind the following items when creating raw devices:
oracle and oinstall, respectively.
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|