Initialization parameters should be tuned and configured according to Oracle's provided Operating System Specific installation documentation.
When tuning the Oracle cost-based optimizer, it is recommended that you modify database initialization parameters to favor index access over full table scans. OnBase queries are very selective in nature, and it is expected that an index will be used for the majority of queries issued by the application. If performance monitoring indicates an excessively high number of sequential scans on larger tables, please review your optimizer settings and make changes as necessary. It is recommended that you determine baseline metrics before making any changes. That way, positive or negative effects can be easily noted.
Listed below are the parameters that most affect system performance.
|
Parameter |
Description |
|---|---|
|
DB_BLOCK_SIZE |
This parameter specifies the size, in bytes, of Oracle database blocks. The value of this parameter cannot be changed after installation. Values range from 4 KB up to 32 KB. Tip:
Set DB_BLOCK_SIZE to 8192. |
|
DB_FILE_MULTIBLOCK_READ_COUNT |
This parameter specifies the maximum number of blocks to be read for an I/O. This parameter was frequently changed in 9i and earlier installations. With the introduction of Oracle's cost-based optimizer (CBO) and system statistics, it is recommended that you leave DB_FILE_MULTIBLOCK_READ_COUNT at the default value, and gather system statistics. When system statistics are captured, the actual multiblock I/O that occurs in the database is measured. That value is then used by the CBO. Tip:
Leave DB_FILE_MULTIBLOCK_READ_COUNT at the default value. This setting should to be monitored over time to ensure that optimal I/O throughput exists within the system. For example, for systems that have a high number of table scans, lowering DB_FILE_MULTIBLOCK_READ_COUNT may allow more index scans/seeks to occur, thereby reducing I/O. However, a lower value may also increase the number of I/O operations. |
|
OPTIMIZER_INDEX_CACHING |
This parameter is used to alter the behavior of cost-based optimization. This setting is an alternative to OPTIMIZER_INDEX_COST_ADJ, and it has a default value of 0. If system statistics are collected, this parameter has less importance as system statistics become a large factor in determining the behavior of the CBO. Tip:
Leave OPTIMIZER_INDEX_CACHING at the default value of 0. If system statistics are not being captured, this setting can be altered to a higher value to encourage index usage over full table scans. However, like DB_FILE_MULTIBLOCK_READ_COUNT, the effect on I/O can be positive or negative. |
|
OPEN_CURSORS |
This parameter specifies maximum number of cursors that any one session can have open. The default value is 50, but it is recommended that the parameter be set higher for most applications. There is no additional overhead if the value is set higher than needed. Tip:
Set OPEN_CURSORS to 300 and increase as necessary. |
|
OPTIMIZER_MODE |
This parameter determines the default behavior for optimizing queries for an Oracle instance. Options include ALL_ROWS, FIRST_ROWS, and FIRST_ROWS_n(deprecated options include CHOOSE and RULE). The default value for Oracle 10g and higher is ALL_ROWS. Tip:
Leave OPTIMIZER_MODE at the default value. |
|
CURSOR_SHARING |
This parameter determines which kinds of SQL statements can share the same cursors. Options include FORCE, SIMILAR, and EXACT. Forcing or encouraging statements to share cursors among similar (but not identical) statements can have unexpected performance results. Tip:
Set CURSOR_SHARING to EXACT(this is the default setting in 11g and higher). |
|
PROCESSES |
This parameter specifies the maximum number of processes that connect to the Oracle instance. To calculate the PROCESSES parameter, add the following values together:
|