Table 7. Recommended Variable Settings for Oracle Databases
|
|
|
|
|
always_anti_join |
HASH |
N/A |
N/A |
|
cursor_sharing |
Exact |
Exact |
Exact |
Specifies the kind of SQL statements can share the same cursors. |
cursor_space_for_time |
FALSE |
FALSE |
FALSE |
Enabling CURSOR_SPACE_FOR_TIME results in holding pins on cursors and their associated frames/buffers for longer periods of time. Cursor_space_for_time requires at least a 50% increase in the size of the shared pool because of the frames/buffers. If the Stats pack reports show that the waits for library cache latch gets is significant, and the latch gets are due to pin requests, then cursor space for time can be used to improve performance. |
db_block_lru_ latches |
1 or (# of CPUs / 2), whichever is greater |
N/A |
N/A |
Specifies the upper bound of the number of LRU latch sets. Set this parameter to a value equal to the desired number of LRU latch sets. It is advisable to set this parameter if you want to use multiple database writers via the init.ora. There must be at least one latch per DB Writer gatherer process. |
db_block_size |
32768 |
32768 |
32768 |
|
db_cache_size |
Use db_block_buffers parameter. |
The value depends on physical memory available, as well as other memory consuming processes running on the server. |
Use sga_target parameter. |
Specifies the size of the DEFAULT buffer pool for buffers with the primary block size. It has a direct impact on query performance. A low value might result in slow query performance, and a high value might deprive other server processes of the memory. For example, the DB_CACHE_SIZE value for an Oracle Analytics Warehouse Database running alone on an 8Gb RAM server can be set to 2Gb. |
db_file_direct_ io_count |
64 |
N/A |
N/A |
|
db_file_ multiblock_read_count |
32 |
32 |
32 |
Specifies the maximum number of blocks read in one I/O operation during a sequential scan. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high. The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum. |
db_files |
512 |
512 |
512 |
Specifies the maximum number of data files that can be opened for a database. |
db_writer_ processes |
1 or (# of CPUs / 4), whichever is greater. |
1 or (# of CPUs / 4), whichever is greater. |
1 or (# of CPUs / 4), whichever is greater. |
Specifies the initial number of database writer processes for an instance. It cannot be greater than DB_BLOCK_LRU_LATCHES parameter. DB_WRITER_PROCESSES is useful for systems that modify data heavily. |
Default pctincrease |
0 |
0 |
0 |
This parameter is specified at tablespace / object level, not in init.ora. |
dml_locks |
1000 |
1000 |
1000 |
|
hash_area_size |
2 * sort_area_size |
Use pga_aggregate_target parameter. |
Use pga_aggregate_target parameter. |
Specifies the upper limit for a single hash operation. Setting WORKAREA_SIZE_POLICY = AUTO and defining PGA_AGGREGATE_TARGET takes care of HASH and SORT areas. NOTE: PGA_AGGREGATE_TARGET does not work for MTS / Shared server configuration in 9i.
|
hash_join_ enabled |
TRUE |
TRUE |
N/A |
Specifies whether the optimizer should consider using a hash join as a join method. |
hash_multiblock_io_count |
The value range is between 1 and 65,536 / DB_BLOCK_SIZE. |
N/A |
N/A |
Specifies the number of sequential blocks that a hash join reads and writes in one IO. The maximum value for HASH_MULTIBLOCK_IO_COUNT varies by operating system. It is always less than the operating system's maximum I/O size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE). Make sure that you are not using MTS. For Oracle 8i you need not set or change the value of this parameter, because Oracle computes the value individually for every query. |
Initial extent |
20 Mb |
20 Mb |
20 Mb |
This parameter is specified at tablespace / object level, not in init.ora |
java_pool_size |
20M |
20M |
Covered by SGA_TARGET |
Specifies the size (in bytes) of the Java pool from which the Java memory manager allocates most Java state during runtime execution. |
large_pool_size |
16M |
16M |
Covered by SGA_TARGET |
|
log_buffer |
Default is 128 * Number of CPUs. |
Default is 128 * Number of CPUs. |
Default is 128 * Number of CPUs. |
Specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Larger values for LOG_BUFFER reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, a value 65536 or higher is reasonable. |
log_checkpoint_ interval |
|
100000 |
100000 |
|
log_checkpoint_ timeout |
|
1800 |
1800 |
|
Next Extent |
20 Mb |
20 Mb |
20 Mb |
This parameter is specified at tablespace / object level, not in init.ora. |
open_cursors |
1000 |
1000 |
1000 |
Specifies the maximum number of open cursors (handles to private SQL areas) that a session can have at once. |
optimizer_index_caching |
unset |
Refer to "Notes" column. |
Refer to "Notes" column. |
Specifies the behavior of cost-based optimization to favor nested loops joins and IN-list iterators. For 8i: use Unset as the value for this parameter to avoid nested loop index joins (as favored by the optimizer). For 9i and 10g: DBA can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. |
optimizer_index_cost_adj |
Choose appropriate value based on data shape and performance monitoring. |
Choose appropriate value based on data shape and performance monitoring. |
Choose appropriate value based on data shape and performance monitoring. |
Specifies how you 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. See also Additional Suggestions for Optimizing Oracle Performance in Oracle Business Analytics Warehouse. |
optimizer_max_ permutations |
|
|
|
Specifies the number of permutations of the tables that the optimizer will consider in queries with joins. Such a restriction ensures that the parse time for the query stays within acceptable limits. The default value is: 2000 if Parameter OPTIMIZER_FEATURES_ENABLE >= 9.0.0 and 80000 if Parameter OPTIMIZER_FEATURES_ENABLE < 9.0.0 |
optimizer_mode |
ALL_ROWS |
CHOOSE |
CHOOSE |
|
pga_aggregate_ target |
N/A |
The value depends on physical memory available, as well as other memory consuming processes running on the server. |
The value depends on physical memory available, as well as other memory consuming processes running on the server. |
Specifies the amount of target aggregate PGA memory available to all server processes attached to the instance. Setting WORKAREA_SIZE_POLICY = AUTO and defining PGA_AGGREGATE_TARGET takes care of HASH and SORT areas. NOTE: PGA_AGGREGATE_TARGET does not work for MTS / Shared server configuration in 9i. For example, PGA_AGGREGATE_TARGET value for an Oracle Analytics Warehouse Database running alone on an 8Gb RAM server can be set to 2Gb.
|
processes |
510 |
510 |
510 |
|
query_rewrite_ enabled |
|
TRUE |
TRUE |
|
query_rewrite_ integrity |
USE_STALE |
TRUSTED |
TRUSTED |
|
rollback_ segments |
|
|
|
See also undo_retention. |
session_cached_ cursors |
500 |
500 |
500 |
|
sga_max_size |
N/A |
The value cannot be less than the sum of all SGA components (pools, buffer and *_cache_size). |
The value cannot be less than the sum of SGA_TARGET and all manual pools and buffers. |
Specifies the maximum size of SGA for the lifetime of the instance. |
sga_target |
N/A |
N/A |
The value depends on physical memory available, as well as other memory consuming processes running on the server. |
Specifies the size following SGA components: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE and JAVA_POOL_SIZE. The STATISTICS_LEVEL should be set to TYPICAL or ALL. Applicable to Oracle 10g only. |
shared_pool_ reserved_size |
10% of shared_pool_size |
10% of shared_pool_size |
10% of shared_pool_size |
Specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory. The value cannot exceed 50% of shared_pool_size. It the value is not set, then its default is 5%. |
shared_pool_size |
500Mb |
500Mb |
Covered by SGA_TARGET |
Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. |
sort_area_ retained_size |
This value is instance specific. DBA has to define its value basing on the volume of concurrent sort operations and available physical RAM. |
Use pga_aggregate_target parameter |
Use pga_aggregate_target parameter |
Specifies the amount of memory which that is allocated to store sorted data after sort operation is over. The data that does not fit into SORT_AREA_RETAINED_SIZE memory would be written to a temporary tablespace. This memory is released back to the UGA (not to the operating system) after the last row is fetched from the sort space. |
sort_area_size |
This value is instance specific. DBA has to define its value basing on the volume of concurrent sort operations and available physical RAM. |
Use pga_aggregate_target parameter |
Use pga_aggregate_target parameter |
Specifies the upper limit for a single sort operation. In a database with many concurrent sort operations the server could use as much as number of concurrent sorts * sort_area_size bytes of RAM. Depending on the database load, specifically sort operations, you need to determine the parameter value that will not consume all physical memory at the peak loads and at the same time allocate reasonable memory for performing sort operations. Setting WORKAREA_SIZE_POLICY = AUTO and defining PGA_AGGREGATE_TARGET takes care of HASH and SORT areas. NOTE: PGA_AGGREGATE_TARGET does not work for MTS / Shared server configuration in 9i.
|
sort_multiblock_ read_count |
The value is instance specific. Suggested value is 2. |
N/A |
N/A |
Specifies the number of database blocks to read each time a sort performs a read from a temporary segment. Any increase in I/O throughput obtained by increasing SORT_MULTIBLOCK_READ_COUNT needs to be balanced with a possible increase in total amount of I/O performed due to an increase in the number of merge passes. |
Star_transforma tion_enabled |
TRUE |
TRUE |
TRUE |
|
Statistics_level |
|
Typical |
Typical |
|
timed_statistics |
TRUE |
TRUE |
TRUE |
On most platforms, enabling timed statistics has minimal effect on performance. It can be enabled/disabled dynamically at both the system and session level. |
Undo_ management |
N/A |
Auto |
Auto |
|
Undo_retention |
N/A |
The suggested value range is from 30000 to 90000. |
The suggested value range is from 30000 to 90000. |
Specifies (in seconds) the amount of committed undo information to retain in the database. To calculate the value: 1. Identify the longest query that will run in the instance and set the undo_retention to that query execution time. 2. Set autoextend for UNDO tablespace. 3. After the initial load is complete, you might want to reduce UNDO_RETENTION to a smaller value. |
Undo_tablespace |
N/A |
Undo |
Undo |
|
Workarea_size_ policy |
N/A |
Auto |
Auto |
|