Tuning Oracle Databases

To achieve optimal performance on your Oracle database, the database needs to be tuned to work with VxFS. This section describes some of the Oracle parameters that you can tune to improve your Oracle database performance when using Quick I/O.

Sequential Table Scans

Quick I/O performs all I/O as direct I/O. In the case of single-threaded sequential scans (common in decision support system (DSS) workloads), using buffered reads can yield better performance. Because the file system detects these sequential reads and performs read-aheads, the next few blocks that Oracle requests are readily available in the system buffer cache and are simply copied to the Oracle system global area (SGA). Because access from memory is inherently faster than access from disk, this achieves a significant reduction in response time.

To handle large sequential scans when using Quick I/O, use the Oracle parallel query process to break the single large scan into multiple smaller scans. This is done by setting the Oracle parameters PARALLEL_MAX_SERVERS, PARALLEL_MIN_SERVERS, and SORT_AREA_SIZE suitably.

Asynchronous I/O

Quick I/O and Oracle Disk Manager support kernel asynchronous I/O, which reduces CPU utilization and improves transaction throughput. Enabling the following parameters lets Oracle take advantage of asynchronous I/O and avoids having to configure multiple DBWR slaves:

• If you are using Quick I/O datafiles with Oracle8i, set DISK_ASYNCH_IO to TRUE in init.ora.
• If you are using ODM on Oracle9i, you do not need to change any init.ora parameters.

Your Oracle Installation Guide provides detailed instructions on implementing asynchronous I/O on your system.

Tuning Buffer Cache

The UNIX buffer cache plays an important role in performance when using HFS or JFS in buffered I/O mode. When using Quick I/O, however, the database cache must be tuned as if raw devices are being used. You can allocate more memory to the database buffer cache because Quick I/O bypasses the file system cache to improve database performance. Memory pages normally allocated to the file system cache can be allocated to the database buffer cache (SGA). With Oracle9i, you can adjust the SGA size without shutting down the database.

Setting Oracle Block Reads During Sequential Scans

The DB_FILE_MULTIBLOCK_READ_COUNT parameter specifies the maximum number of blocks Oracle reads in one I/O operation during a sequential scan. When the file system is created on a striped volume, set this parameter to a value that is a multiple of the full stripe size divided by DB_BLOCK_SIZE. Using a full stripe size allows the read operations to take advantage of the full bandwidth of the striped disks during sequential table scan.

Set the DB_FILE_MULTIBLOCK_READ_COUNT to a value that is a multiple of (read_pref_io*read_nstream)/DB_BLOCK_SIZE, but the value should not exceed the value of max_direct_iosz/DB_BLOCK_SIZE.

Use the vxtunefs command to display the value of read_pref_io, read_nstream, and max_direct_iosz, for example:

The vxtunefs command displays output similar to the following:

Setting Slave Parameters

Quick I/O and ODM provide support for asynchronous I/O, eliminating the need for multiple logwriter slaves or database writer slaves. This parameter is set to 0 by default.

It is not necessary to set the DBWR_IO_SLAVES settings if you are using Quick I/O. The number of DBWR writer processes is set within DB_WRITER_PROCESSES, which performs asynchronous I/O.

Configuring Memory Allocation

Never configure Oracle to make use of more memory than is physically available on the system. Oracle may have to compete with other processes for system memory resources, and all of these potential processes must be considered when sizing and allocating memory. In the ideal configuration, a system that is dedicated to Oracle simplifies the tuning and monitoring issues and ensures best performance.