Release 8.0.5
BETA
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Reference for LINUX 2.0.34 Release 8.0.5 BETA |
|
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 LINUX tools described in "LINUX Tools" in the next section.
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.
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.
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
The free 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 free command is shown below:
total used free shared buffers cached Mem: 63160 62488 672 42952 484 34228 -/+ buffers/cache: 27776 35384 Swap: 385516 16124 369392
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/.
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 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 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 free to monitor paging. The following columns are from the vmstat 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 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 SHMAX to 4294967295 (4 GB).
The size of the SGA can be estimated using the following steps:
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 LINUX. |
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 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.
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:
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 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
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. |
|
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 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
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.
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_pid.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.
