This chapter describes how to configure an Oracle Database 10g installation to optimize its performance. This chapter contains the following sections:
Oracle Database 10g is a highly optimizable software product. Frequent tuning optimizes system performance and prevents bottlenecks. Although this chapter is written from the perspective of single-instance installations, most of the performance tuning tips provided here are also valid when using Oracle RAC.
See Also:Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide and Oracle Database Performance Tuning Guide and Reference
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 factors.
Although the HP OpenVMS kernel usually allocates CPU resources effectively, many processes compete for CPU cycles, and this can cause contention. If you installed Oracle Database 10g in a multiprocessor environment, then there may be a different level of contention on each CPU.
Contention is also common for Oracle resources such as locks and latches.
Various tools are provided for gathering statistics, analyzing performance and tuning performance. Automatic Workload Repository takes snapshots of the system every 60 minutes. Active Session History collects samples of active sessions. New Time Model offers a unique way to store statistics and metrics. Automatic Database Diagnostic Monitor is a powerful self-diagnostic engine that analyzes the system, identifies the major problem in the system, and recommends corrective action. These tools include the
V$ performance views and the
A variety of tools are provided for tuning SQL, such as SQL Tuning Advisor and SQL Access Advisor. These tools include the
V$ performance views, the
EXPLAIN PLAN command, the
SQL TRACE facility, the
TKPROF facility, the Autotrace report, and the
Start the memory tuning process by measuring paging and swapping space to determine how much memory is available. After you have determined the memory usage of the system, 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, then you can have a significant influence on Oracle Database 10g performance. The optimal Oracle Database 10g buffer size for the system depends on the overall system load and the relative priority of Oracle over other applications.
This section contains the following topics:
Try to minimize swapping because it causes significant HP OpenVMS overhead. To check for swapping, enter the following command:
$ SHOW MEMORY/FILES
If the system is swapping and you must conserve memory, then:
Avoid running unnecessary system daemon processes or application processes.
Decrease the number of database buffers to free some memory.
The operating system documentation for more information about the
$ SHOW MEMORY command
The HP OpenVMS
INSTALL utility Help for information about installing additional page and swap files
HP OpenVMS System Manager's Guide for information about managing page and swap files
Paging may 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 may not noticeably affect the performance of the system.
To detect excessive paging, run measurements during periods of fast response or idle time to compare against measurements from periods of slow response.
Install more memory.
Move some of the work to another system.
Decrease the number of database buffers to free some memory.
The HP OpenVMS system reads entire operating system blocks from the disk. If the database block size is smaller than the HP OpenVMS file system buffer size, then I/O bandwidth is inefficient. If you adjust the Oracle Database block size to be a multiple of the operating system block size, then you can increase performance by up to five percent.
DB_BLOCK_SIZE initialization parameter sets the database block size across the database. To see the current value of the
DB_BLOCK_SIZE parameter, or to create new tablespaces with a different size, enter the
SHOW PARAMETER command in SQL*Plus.
Balance I/O evenly across all available disks to reduce disk access time. For smaller databases and databases that do not use RAID, ensure that different data files and tablespaces are distributed across the available disks.
To monitor disk performance, use the
$ MONITOR DISK command.
The average value from
$ MONITOR DISK/ITEM=QUEUE should not exceed 1.0. If it does or if the MAX value is high, then the system may experience an I/O bottleneck.
Oracle Database 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. Oracle Database does not support changing the priority of user and background processes.
For example, if the log writer process (LGWR) gets a low priority, then it is not run frequently enough and LGWR becomes a bottleneck. In contrast, if LGWR has a high priority, then the response time for user processes may be poor.
The System Global Area (SGA) is the Oracle structure that is located in shared memory. It contains static data structures, locks, and data buffers. Sufficient shared memory must be available to each Oracle process to address the entire SGA.
Alternatively, set the
SGA_TARGET initialization parameter to enable Oracle to automatically tune the SGA size.
Exercise caution when setting values for these parameters. When values are set too high, too much of the physical memory is devoted to shared memory, resulting in poor performance.
Oracle Database installations configured with Shared Server require a higher setting for the
SHARED_POOL_SIZE initialization parameter, or a custom configuration that uses the
LARGE_POOL_SIZE initialization parameter. If you installed the database with Oracle Universal Installer, then the value of the
SHARED_POOL_SIZE parameter is set automatically by Oracle Database Configuration Assistant. However, if you created a database manually, then increase the value of the
SHARED_POOL_SIZE parameter in the parameter file by 1 KB for each concurrent user.
You can determine the SGA size in one of the following ways:
Enter the following SQL*Plus command to display the size of the SGA for a running database:
SQL> SHOW SGA
The result is shown in bytes.
Determine the size of the SGA when you start the database instance. The SGA size is displayed next to the heading Total System Global Area.
Install Oracle image as an HP OpenVMS resident image to increase the performance of Oracle. To install the
ORACLE.EXE image resident, the following changes need to be made to HP OpenVMS and Oracle.
Note:Make valid backups of all HP OpenVMS and Oracle software before any changes are implemented.
Oracle recommends you to do initial testing in a non-production environment whenever possible. For queries, contact your local support organization.
The following system changes are recommended for improved Oracle performance:
Increase the current size of the
GH_RSRVPGCNT. The size of the parameters
GH_RSRVPGCNT need to be increased by 8192, and
GH_RES_DATA to its current maximum value of 2048. This increase needs to accommodate the size of the image being installed resident.
Oracle recommends using
AUTOGEN and the
MODPARAMS.DAT file for making these changes. These values should be changed by making use of the
ADD_parameter feature of
See Also:Refer to:
HP OpenVMS System Services Reference Manual for more information about
HP OpenVMS System Manager's Manual, Volume 2: Tuning, Monitoring, and Complex Systems for information about using
AUTOGEN to adjust system parameters
Note:These parameters are not dynamic. You must restart your system for the new values to take effect.
You must also install the HP OpenVMS image
SYS$LIBRARY:DSMTSHR.EXE with shared address space before the Oracle image is installed resident. HP OpenVMS installs this image by default, but not with shared address space.
There are two ways to implement this:
INSTALL REPLACE command as shown:
$ INSTALL RELACE SYS$LIBRARY:DISMNTSHR.EXE/SHARED=ADDRESS
This command would need to be rerun after any system restart and before the Oracle image is installed (which typically occurs during execution of Oracle startup procedures).
Edit the file
SYS$MANAGER:VMS$IMAGES_MASTER.DAT, which contains the list of installed images for HP OpenVMS, and add the
/SHARED=ADDRESS qualifier for the
DISMNTSHR.EXE entry in the file. This is done before running
AUTOGEN to ensure that
DISMNTSHR.EXE is installed with the
/SHARED=ADDRESS option after a system restart or after running
AUTOGEN. You may re-edit this file if it is included in a system patch or upgrade. Caution should be taken when editing this file, so that this is the only change made.
Changes to the command procedure which links the
ORACLE.EXE image and the procedure which installs that image need to be made for the Oracle image to be installed resident. These changes are shown below:
INSTALL CREATE ORA_ROOT:[BIN]ORACLE.EXE/OPEN/HEADER_RES/SHARE
INSTALL CREATE ORA_ROOT:[BIN]ORACLE.EXE/OPEN/HEADER_RES/SHARE=ADDRESS/RESIDENT