Release 8.0.4
A56101-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Reference for Sun SPARC Solaris 2.x Release 8.0.4 A56101-01 |
|
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, observe its normal behavior using the Solaris 2.x tools described in "Solaris 2.x Tools" in the next section.
| See Also:
Oracle8 Parallel Server Concepts and Administration. |
Solaris 2.x 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:
Solaris 2.x tools are described in the operating system documentation. |
The vmstat utility reports process, virtual memory, disk, paging, and CPU activity on Solaris 2.x UNIX, 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 -S 5 8
Sample output from the vmstat command is shown in Figure 3-1.
Output from vmstat command
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 ten second intervals:
$ sar -p 10 10
Sample output from the sar -p command is shown in Figure 3-2.
The iostat utility reports terminal and disk activity. 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 five second intervals:
$ iostat 5 5
Sample output from the iostat command is shown in Figure 3-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 3-4.
The mpstat utility reports per-processor statistics. Each row of the table represents the activity of one processor. The first table summarizes all activity since boot; each subsequent table summarizes activity for the preceding interval. All values are rates (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 3-5.
Output from mpstat command
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 Solaris 2.x, 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 2.x to check for swapping.
If your system is swapping and you need to conserve memory:
Procedures for adding swap space vary between UNIX implementations. 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 operating system 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.
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:
Although this performance gain is minor, you cannot start the database without configuring sufficient shared memory.
You may need to reconfigure the UNIX kernel to increase shared memory. The UNIX 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 SHMAX to 4294967295 (4 GB).
The size of the SGA can be estimated using the following steps:
You can also use the UNIX utility ipcs to monitor the status of shared memory.
| See Also:
"Configure UNIX Kernel for Oracle" in Chapter 2 of the Oracle8 Installation Guide for Sun SPARC Solaris 2.x. |
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 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.
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 Solaris 2.x 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.
IOSTAT 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.
Solaris 2.x 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 3-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. Some platforms employ process binding automatically.
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 program, 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
.
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.
| WARNING:
Remember to make a backup copy of your UNIX kernel. See your hardware vendor documentation for additional details. |
| 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. |
On Solaris 2.x, 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
To take full advantage of raw devices, adjust the size of the Oracle8 buffer cache and, if memory is limited, the Solaris 2.x buffer cache.
The Solaris 2.x 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 Solaris 2.x 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 Solaris 2.x buffer cache size.
The Solaris 2.x command sar may help you determine which buffer caches should be increased or decreased. The sar command syntax is shown in Table 3-3.
This section describes the trace (or dump) and alert files the Oracle Server 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 value is $ORACLE_HOME/rdbms/log.
Raw devices have the following disadvantages when used on Solaris 2.x:
These factors should be considered when deciding on raw devices:
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 2.x 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.
Use raw devices for Oracle files if your site has at least as many raw disk partitions as Oracle tablespaces.
If the raw disk partitions are already formatted, match tablespace size to partition size as closely as possible to avoid wasting space.
When creating raw disk partitions, observe these guidelines:
With logical volumes, you can create logical disks based on raw partition availability, because logical disks can be moved on 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.
Mirroring of logical volumes is possible and should be used 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 Parallel Server: Logical volumes are available 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.
| WARNING:
Do not attempt to set up raw devices without the help of an experienced system administrator and specific knowledge about the machine you are using. |
To set up raw devices on your system:
A free partition is one that is not used for a Solaris 2.x file system. That means that the partition follows these restrictions:
/etc/mount command.
To find out whether a partition is free, obtain a complete map of the starting locations and sizes of the partitions on the device and check for free space. Note that some partitions may contain file systems that are currently not mounted and are not listed in the /etc/mount output.
| Attention:
Make sure that the partition does not start at Cylinder 0. |
Begin by verifying that the disk is partitioned. If not, use the operating system format utility to partition it.
Next, make sure that the partition is owned by the oracle software owner. If necessary, use chown to change its ownership on the block and character files for the device. For example:
$ chown oracle /devices/iommu@f,e0000000/ sbus@f,e0001000/espdma@f,400000/esp@f,800000/ sd@5,0:a $ chown oracle /devices/iommu@f,e0000000/ sbus@f,e0001000/espdma@f,400000/esp@f,800000/ sd@5,0:a,raw
Use chmod to make the partition accessible only by the oracle software owner. For example:
$ chmod 600 /devices/iommu@f,e0000000/ sbus@f,e0001000/espdma@f,400000/esp@f,800000/ sd@5,0:a $ chmod 600 /devices/iommu@f,e0000000/ sbus@f,e0001000/espdma@f,400000/esp@f,800000/ sd@5,0:a,raw
$ ln -s /devices/iommu@f,e0000000/sbus@f,e0001000\
/espdma@/f,400000/esp@f,800000/sd@5,0:a,raw /oracle_data/datafile.dbf
Make sure you use the character special device, not the block special device. If this is correct, the following command,
$ ls -Ll datafile
should return,
crw------- oracle dba datafile
(the flags used in the above command are: L = show symbolic links, and l = long listing).
| Note:
This symbolic link must be set up on each node of the Parallel Server. Check that no two symbolic links point to the same raw device. |
From Server Manager, use the SQL statement CREATE DATABASE to create the database using the specified raw partition.
| Note:
The size of an Oracle datafile created in a raw partition must be at least two Oracle block sizes smaller than the size of the raw partition. |
$ svrmgrl
SVRMGR> create database sid SVRMGR> logfile '/oracle_data/log1.dbf' size 100K,
'oracle_data/log2.dbf' size 100K SVRMGR> datafile '/oracle_data/datafile.dbf' size 10000K
reuse;
If you want to add the partition to a tablespace in an existing Oracle database instead, enter:
$ svrmgrl
SVRMGR> alter tablespace tablespace_name add datafile '/dev/rdsk/c0t1d0s6' size 10000K reuse;
You can use the same procedure to set up a raw device for the redo log files.
