Table 12. Recommended Variable Settings for Oracle Databases
|
|
|
|
Always_anti_join |
HASH |
N/A |
|
Cursor_space_for_time |
TRUE |
TRUE |
Use only if you do not have many concurrent connections. |
Db_block_lru_latches |
Set to number of CPUs |
N/A |
|
Db_block_size |
32 k |
32 k |
|
Db_cache_size |
|
8.92 MB |
|
Db_file_direct_io_count |
64 |
N/A |
|
Db_file_multiblock_read_count |
32 |
32 |
|
DB_files |
|
100 |
|
db_writer_processes |
Number of CPUs |
Number of CPUs |
|
Default Pctincrease |
0 |
0 |
|
Dml_locks |
|
1000 |
|
Hash_area_size |
20 MB |
N/A |
Setting WORKAREA_SIZE_POLICY = AUTO and PGA_AGGREGATE_TARGET to about one-third of the available memory takes care of HASH and SORT areas in Oracle 9i. |
Hash_multiblock_io_count |
0.5 * ( db_file_multiblock_Read_count) |
N/A |
Make sure that you are not using MTS. |
Initial Extent |
20 MB |
20 MB |
|
Log_buffer |
16 MB |
16 MB |
|
Log_checkpoint_interval |
|
100000 |
|
Next Extent |
20 MB |
20 MB |
|
Open_cursors |
2000 |
2000 |
|
Optimizer_index_caching |
Unset |
|
Use Unset as the value for this parameter to avoid nested loop index joins (as favored by the optimizer). Unset is not an allowable value for Oracle 9i. For Oracle 9i, the allowable values are in the range 1 to 100. |
Optimizer_index_cost_adjustment |
Choose appropriate value based on data shape and performance monitoring |
Choose appropriate value based on data shape and performance monitoring |
This parameter can be used to tune the performance of a system in cases in which the optimizer may be choosing too few or too many index access paths. For example, setting it to 50 percent will make the index access path look half as expensive as normal. The legal range of values for this parameter is 1 to 10000 percent. The default for this parameter is 100 percent, which makes the optimizer cost index access paths at the regular cost. Also see Additional Suggestions for Optimizing Oracle Performance in Siebel Data Warehouse. |
Optimizer_max_permutations |
2000 |
2000 |
May be applicable to other schemas in the database. This means longer parsing times for the access paths, but once these are parsed, they should remain in the shared_pool, provided they do not get aged out. See the notes for Cursor_space_for_time. |
Optimizer_mode |
ALL_ROWS |
CHOOSE |
|
Pga_aggregate_target |
N/A |
About one-third of available memory |
|
Processes |
|
510 |
|
Query_rewrite_integrity |
USE_STALE |
TRUSTED |
USE_STALE is not an allowable value for Oracle 9i. For Oracle 9i, the allowable values are: - STALE_TOLERATED
- ENFORCED
- TRUSTED
|
Query_rewrite_enabled |
|
True |
|
Rollback_segments |
- Initial Extent 50 MB
- Next Extent 50 MB
- Optimum Extent 50 MB
|
N/A |
For Oracle 9i, use undo_management = AUTO undo_tablespace = UNDO undo_retention = 60000 |
SGA_max_size |
|
1024 MB |
Allocate one-third to one-half of available memory and adjust as needed based on performance monitoring. |
Shared_pool_size |
150 MB |
512 MB |
|
Shared_pool_reserved_size |
|
200 MB |
|
Sort_area_retained_size |
10 MB |
N/A |
Setting WORKAREA_SIZE_POLICY = AUTO and PGA_AGGREGATE_TARGET to about one-third of the available memory takes care of HASH and SORT areas in Oracle 9i. |
Sort_area_size |
10 MB |
N/A |
Setting WORKAREA_SIZE_POLICY = AUTO and PGA_AGGREGATE_TARGET to about one-third of the available memory takes care of HASH and SORT areas in Oracle 9i. |
Sort_multiblock_read_count |
4 |
N/A |
|
Star_transformation_enabled |
TRUE |
TRUE |
|
Statistics_level |
|
Normal |
|
Undo_management |
N/A |
Auto |
|
Undo_retention |
N/A |
10000 |
|
Undo_tablespace |
N/A |
Undo |
|
Workarea_size_policy |
N/A |
Auto |
|