Skip Headers
Oracle® Database Administrator's Reference
10g Release 1 (10.1) for hp OpenVMS Alpha
Part No. B13738-01
  Go To Table Of Contents
Go To Index


8 Tuning Oracle Database 10g

This chapter describes how to configure an Oracle Database 10g installation to optimize its performance. This chapter contains the following sections:

8.1 Introduction to Tuning

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 Real Application Clusters (RAC).

See Also:

Oracle Real Application Clusters Concepts and Administration and Oracle Database Performance Tuning Guide and Reference

Performance bottlenecks are often caused by the following factors:

8.2 Oracle SQL Tuning Tools

A variety of tools are provided for tuning SQL. These tools include the V$ performance views, the EXPLAIN PLAN command, the SQL TRACE facility, the TKPROF facility, the Autotrace report, and the STATSPACK scripts.

See Also:

Oracle Database 10g Database Performance Tuning Guide and Reference

8.3 Tuning Memory Management

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:

8.3.1 Allocate Sufficient Swap Space

Try to minimize swapping because it causes significant 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.

    See Also:

    • The operating system documentation for more information about the $ show memory command

    • The OpenVMS INSTALL utility Help for information about installing additional page and swap files

    • OpenVMS System Manager's Guide for information about managing page and swap files

8.3.2 Control Paging

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.

If the system consistently has excessive page-out activity, then consider the following solutions:

  • Install more memory.

  • Move some of the work to another system.

  • Decrease the number of database buffers to free some memory.

8.3.3 Adjust Oracle Block Size

The OpenVMS system reads entire operating system blocks from the disk. If the database block size is smaller than the 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.

The DB_BLOCK_SIZE initialization parameter sets the database block size databasewide. 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.

8.4 Tuning Disk I/O

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.

8.5 Monitoring Disk Performance

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.

8.6 Tuning CPU Usage

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.

8.7 System Global Area

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.

Set the following initialization parameters to control the size of the 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.

8.7.1 Determine the Size of the SGA

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:


    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.