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


 

HTML Text

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.

 

SEE CODE DEPOT FOR FULL SCRIPTS


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:

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

 


”call

  
 

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.