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