Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Tips

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.



This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.