10 Database Tuning Considerations

To achieve optimal performance in Oracle Application Server, for applications that use the database, the database tables you access need to be designed with performance in mind, and you need to monitor and tune the database server to assure that the system is performant. This chapter describes some of the init.ora parameters that may need to be tuned in a backend Oracle Database Server.

This chapter covers the following:

Tuning init.ora Database Parameters
Tuning Redo Logs Location and Sizing

See Also:

Oracle9i Database Performance Tuning Guide and Reference

Tuning init.ora Database Parameters

Table 10-1 shows tuning information for several the init.ora database initialization parameters.

Table 10-1 Important init.ora Tuning Parameters

init.ora Parameter	Description
`DB_BLOCK_SIZE`	Sets the database block size. OLTP applications usually benefit from smaller block sizes, DSS applications usually benefit from larger block sizes. This parameter can only be set when the database is created, and defaults to the minimum value of 2K. See Also: table 15-4, "Block Size Advantages and Disadvantages" in the Oracle9i Database Performance Tuning Guide and Reference
`DB_CACHE_SIZE`	Sets the size of the buffer cache in the SGA. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA. The `V$DB_CACHE_ADVICE` view provides predictions of behavior using various buffer cache sizes. Note that the initialization parameter `DB_CACHE_ADVICE` must be set to `ON` before `V$DB_CACHE_ADVICE` will gather statistics. The parameter `DB_BLOCK_BUFFERS` has been deprecated. See Also: "Memory Configuration and Use" in the Oracle9i Database Performance Tuning Guide and Reference.
`JAVA_POOL_SIZE`	If you are using Java stored procedures, then this parameter should be set depending on the actual requirements of memory for the Java environment. The `V$SGASTAT` view will show the current java pool allocation.
`PGA_AGGREGATE_TARGET`	Specifies the target aggregate PGA memory available to all server processes attached to the instance.See Also: the chapter, "Memory Configuration and Use" in the Oracle9i Database Performance Tuning Guide and Reference for information on PGA memory management.
`PROCESSES`	Sets the maximum number of operating system processes that can be connected to Oracle concurrently. The value of this parameter must be 6 or greater (5 for the background processes plus 1 for each user process). For example, if you plan to have 50 concurrent users, set this parameter to at least 55. Many other initialization parameter values are deduced from this value.
`SHARED_POOL_SIZE`	Sets the size of the shared pool in the SGA. The main components of the shared pool are the library cache and the dictionary cache. A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used library and dictionary data is cached. The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the `RELOADS` column in the `V$LIBRARYCACHE` view. In an application that reuses SQL effectively, on a system with an optimal shared pool size, the `RELOADS` statistic will have a value near zero. Another key statistic is the amount of free memory in the shared pool at peak times. The amount of free memory can be queried from `V$SGASTAT`, looking at the free memory for the shared pool. Optimally, free memory should be as low as possible, without causing any reloads on the system. See Also: the chapter, "Memory Configuration and Use" in the Oracle9i Database Performance Tuning Guide and Reference.
`UNDO_TABLESPACE`, `UNDO_MANAGEMENT`	Undo space can be managed with either rollback segments or undo tablespaces. Good performance can be achieved by either method, however, the use of rollback segments for managing undo space will be deprecated in a future release. Oracle strongly recommends that you use automatic undo management (`UNDO_MANAGEMENT` `= AUTO`) and manage undo space using an `UNDO_TABLESPACE`. For backward compatibility reasons, the default value of `UNDO_MANAGEMENT` is `MANUAL`. See Also: Oracle9i Database Administrator’s Guide for additional information on undo space management.

Tuning Redo Logs Location and Sizing

Managing the database I/O load balancing is a non-trivial task. However, tuning the redo log options can provide performance improvement for applications running in an Oracle Application Server environment, and in some cases, you can significantly improve I/O throughput by moving the redo logs to a separate disk.

The size of the redo log files can also influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Small log files can increase checkpoint activity and reduce performance. Because the recommendation on I/O distribution for high performance is to use separate disks for the redo log files, there is no reason not to make them large. A potential problem with large redo log files is that these are a single point of failure if redo log mirroring is not in effect.It is not possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes. Set the initialization parameter LOG_CHECKPOINTS_TO_ALERT = true to have checkpoint times written to the alert file.

The complete set of required redo log files can be created during database creation. After they are created, the size of a redo log size cannot be changed. However, new, larger files can be added later, and the original (smaller) ones can subsequently be dropped.

See Also:

The chapters, "Building a Database for Performance" and "I/O Configuration and Design" in the Oracle9i Database Performance Tuning Guide and Reference