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