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 you may need to tune in a backend Oracle Database Server.

This chapter covers the following:

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

10.1 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 8-3, "Block Size Advantages and Disadvantages" in the Oracle Database Performance Tuning Guide.
`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 Oracle Database Performance Tuning Guide 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.
`SGA_TARGET`	Setting this parameter to a nonzero value enables Automatic Shared Memory Management. Set this parameter to the amount of memory that you want dedicated for the SGA. In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools: Database buffer cache Shared pool Large pool Java pool Oracle strongly recommends the use of automatic memory management, both to simplify configuration and to improve performance. Automatic Shared Memory Management was introduced with the Oracle Database 10g (10.1). For prior versions, you must manually configure the SGA memory pools. See Also: The section, "Automatic Shared Memory Management" in the Chapter,"Memory Configuration and Use" in the Oracle Database Performance Tuning Guide for additional information on SGA management.
`STREAMS_POOL_SIZE`	Specifies (in bytes) the size of the Streams pool. The Streams pool contains captured events. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the Streams pool size is set to zero, then SGA memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.See Also Oracle Streams Concepts and Administration for detailed information on setting this parameter.
`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: Oracle Database Performance Tuning Guide for additional information on undo space management.

10.2 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.