2
Tuning Oracle9i on HP OpenVMS Alpha

The more your Oracle9i applications increase in complexity, the more you must tune the system to optimize performance and prevent bottlenecks. This chapter describes how to configure yourOracle9i installation to optimize its performance.

It contains the following sections:

• "Importance of Tuning"

• "Oracle SQL Tuning Tools"

• "Tuning Memory Management"

• "Tuning Disk I/O"

• "Monitoring Disk Performance"

• "Tuning CPU Usage"

• "Using Trace and Alert Files"

2.1 Importance of Tuning

Oracle9i 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, most of the performance tuning tips provided here are also valid when using Oracle9i Real Application Clusters.

See Also: For more information on tuning, refer to the Oracle9i Real Application Clusters Concepts and Administration and Oracle9i Database Performance Tuning Guide and Reference.

2.1.1 Types of Performance Bottlenecks

Performance bottlenecks are often caused by the following:

• Memory contention

  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

  Disk I/O contention is caused by poor memory management, poor distribution of tablespaces and files across disks, or a combination of both.

• CPU contention

  Although the OpenVMS kernel usually allocates CPU resources effectively, many processes compete for CPU cycles and this can cause contention. If you installed Oracle9i in a multiprocessor environment, there might be a different level of contention on each CPU.

• Oracle resources contention

  Contention is also common for Oracle resources such as locks and latches.

2.2 Oracle SQL Tuning Tools

Oracle provides a variety of tools for tuning SQL, including the V$ performance views, the EXPLAIN PLAN command, the SQL TRACE facility, the TKPROF facility, the Autotrace report and the STATSPACK.

See Also: the Oracle9i Database Performance Tuning Guide and Reference for information on how to use the SQL tuning tools.

2.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 your system's memory usage, 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, you can have a significant influence on Oracle9i performance. The optimal Oracle9i buffer size for your system depends on the overall system load and the relative priority of Oracle over other applications.

2.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 your system is swapping and you must conserve memory:

• 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 on the $ show memory command. To install additional Page and Swap files, refer to the VMS INSTALL Utility. For additional comprehensive information on managing Page and Swap files refer to the OpenVMS System Manager's Guide.

2.3.2 Control Paging

Paging might 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 might 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.

If your system consistently has excessive page-out activity, 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.

2.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, I/O bandwidth is inefficient. If you adjust the Oracle database block size to be a multiple of the operating system block size, you can increase performance by up to five percent.

The DB_BLOCK_SIZE initialization parameter sets the database block size database-wide.

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.

2.4 Tuning Disk I/O

Balance I/O evenly across all available disks to reduce disk access times. For smaller databases and those not using RAID, ensure that different data files and tablespaces are distributed across the available disks.

2.5 Monitoring Disk Performance

To monitor disk performance, use the $Monitor Disk command.

The average value from $monitor disk/item=queue should not exceed 0.8-1.0. If it does or if the MAX value is high, you suffer from an I/O bottleneck.

2.6 Tuning CPU Usage

This section provides information on tuning CPU usage.

2.6.1 Keep All Oracle Users/Processes at the Same Priority

Oracle 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 does not support changing the priority of user and background processes.

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.

2.7 Using Trace and Alert Files

This section describes the trace (or dump) and alert files that Oracle9i creates to diagnose and resolve operating problems.

2.7.1 Trace Files

Each server and background process can write to an associated trace file. When a process detects an internal error, it writes information on the error to its trace file. The filename format of a trace file is:

nodename_sid_processtype_processname_number.trc, 

where:

nodename is the name of the machine where an instance is running, and

sid is the instance system identifier,

processtype is the FG (foreground) or BG (background),

processname is the process that generates it; and

number is a unique 3 digit numeric identifier.

A sample trace filename is:

ORA_ROOT:[ADMIN.<dbname>.BDUMP]NODEA_PROD_BG_PMON_002.TRC

All trace files for background processes are written to the destination directory specified by the BACKGROUND_DUMP_ DEST initialization parameter. All trace files for server processes are written to the destination directory specified by the USER_DUMP_DEST initialization parameter.

Set the MAX_DUMP_FILE_SIZE initialization parameter to at least 10000 to ensure that the trace file is large enough to store error information.

2.7.2 Alert Files

The <nodename>_sid_alert.log file stores significant database events and messages. Anything that affects the database instance or global database is recorded in this file. This file is associated with a database and is located in the directory specified by the BACKGROUND_DUMP_DEST initialization parameter.