Oracle Parameter Tuning

Oracle10g has more than 250 initialization parameters, and these parameters govern the overall behavior of the Oracle instance.  When set to suboptimal values, Oracle parameters can cause serious performance problems.  This is especially true for the parameters that govern the SQL optimizer.

The following is a list of some of the most important parameters for Oracle10g SQL tuning:

§       optimizer_mode

§       optimizer_index_cost_adj

§       optimizer_index_caching

§       optimizer_percent_parallel

There are parameters that control the sizes of the SGA regions and the proper configuration and settings for these parameters can have a profound impact on database performance. These parameters include:

§       db_cache_size

§       db_keep_cache_size

§       shared_pool_size

§       pga_aggregate_target

§       log_buffer

§       query_rewrite_enabled

§       cursor_sharing

§       db_file_multiblock_read_count

§       hash_multiblock_io_count

If the Oracle Automatic Memory Management (AMM) is utilized, Oracle will automatically change the relative sizes of the SGA regions, such as db_cache_size, pga_aggregate_target and shared_pool_size, based on current demands; however, the overall parameters for the instance must be set.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm