Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Control SQL parse time with _optimizer_search_limit

Oracle Tips by Burleson Consulting
February 26, 2003

 

The _optimizer_search_limit parameter has become a hidden parameter starting in Oracle 8.1.7.  As you may know, all parameters starting with an ?underscore? are hidden, and Oracle does not recommend changing them unless you are an expert.

The _optimizer_search_limit hidden parameter specifies the maximum number of table join combinations that will be evaluated by the CBO when deciding the best way to join multiple tables. The reason is to prevent the optimizer from spending an inordinate amount of time on every possible join ordering. The _optimizer_search_limit parameter also controls the threshold for invoking a star join hint, and a star hint will be honored when the number of tables in the query is less than the _optimizer_search_limit. The default value is 5.  This equates to 5!, or 120 join permutations.

Most SQL tuning experts always use stored outlines or the ordered hint for any query involving four or more tables. This eliminates the time-consuming evaluation of the SQL parses for table join orders, and it improves the speed of the query. However, when tuning the SQL to determine the best table join order, it is wise to leave _optimizer_search_limit to its default value so that all possible table join orders are considered.

If the number of tables in the query is less than _optimizer_search_limit, the optimizer examines all possible table join combinations. The number of joins orders is the factorial value of the number of tables in the query. For example, a query joining five tables would have 5! = 5 * 4 * 3 * 2 * 1 = 120 possible combinations of table join orders. The number of possible evaluations is the factorial of the optimizer_search_limit, so with the default value for _optimizer_search_limit of five, the cost-based optimizer will evaluate up to 120 table join orders.

The _optimizer_search_limit and optimizer_max_permutations parameters work together, and the optimizer will generate possible table joins permutations until the value specified by _optimizer_search_limit or optimizer_max_permutations is exceeded. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining nine tables together will exceed the optimizer_search_limit but still may spend expensive time attempting to evaluate all 362,880 possible table join orders (nine factorial) until the optimizer_max_permutations parameter has exceeded its default limit of 80,000 table join orders.

However, when tuning a SQL statement when you plan to use optimizer plan stability to make the execution plan permanent, it is acceptable to temporarily set the _optimizer_search_limit up to the number of tables in your query, tune the query by reordering the table names in the where clause, and then use the ordered hint with stored outlines to make the change permanent.

Remember, the use of the ordered hint overrides the _optimizer_search_limit and optimizer_max_permutations parameters. This is because the ordered hint requests that the tables be joined in the order that they appear in the from clause of the query. The ordered hint is the way most SQL tuning professionals disable table join evaluation, once the optimal join order has been determined.

[http://dba-oracle.com/include_SQL_Tuning_book.htm]

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational