Oracle SQL Hidden Optimizer Parameters

Despite the name Oracle, the Cost Based Optimizer (CBO) is not psychic, and it can never know the exact load on the system in advance.  Hence, the Oracle professional must adjust the CBO behavior, and most Oracle professionals adjust the CBO with two parameters: optimizer_index_cost_adj and optimizer_index_caching.

The parameter named optimizer_index_cost_adj controls the CBO’s propensity to favor index scans over full-table scans.  In a dynamic system, the ideal value for optimizer_index_cost_adj may change radically in just a few minutes, as the type of SQL and load on the database changes.

SQL Optimizer undocumented parameters

 These parameters control the internal behavior of the cost-based SQL optimizer.

§       _fast_full_scan_enabled: This enables or disables fast full index scans, if only indexes are required to resolve the queries.

§       _always_star_transformation: This parameter helps tune data warehouse queries, provided that the warehouse is designed properly.

§       _small_table_threshold: - This sets the size definition of a small table. A small table is automatically pinned into the buffers when queried. In Oracle 9i, this parameter defaults to two percent.

SEE CODE DEPOT FOR FULL SCRIPTS

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