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 


 

 

 


 

 

 

 
 

Oracle optimizer search limit & permutations tips

Oracle Tips by Burleson Consulting

Feb 11, 2015

The most important component of Oracle SQL execution time is the time spent preparing a new SQL statement for execution. But by understanding the internal mechanisms of the generation of the execution plan, you can control the amount of time Oracle spends evaluating table join order, and boost the performance of queries in general.  For complete tips on SQL tuning with the optimizer, see my book "Oracle Tuning: The Definitive Reference".

Preparing the SQL statement for execution

When a SQL statement enters the Oracle library cache, the following steps must occur before the statement is ready to execute:
 

  1. Syntax check - The SQL statement is checked for proper spelling and word order.

  2. Semantic parse - Oracle verifies all of the tables and column names against the data dictionary.

  3. Stored Outline check - Oracle checks the data dictionary to see if a stored outline exists for the SQL statement.

  4. Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.

  5. Generate execution plan - Oracle uses cost-based optimizer algorithms and statistics in the data dictionary to determine the optimal execution plan.

  6. Create binary code - Oracle generates a binary executable based on the execution plan.

Once a SQL statement is prepared for execution, subsequent executions will happen very fast, because Oracle recognizes identical SQL statements and re-uses executables for those statements.

However, for systems that generate ad hoc SQL or SQL with embedded literal values, SQL execution plan generation time is significant, and previous execution plans often can?t be used. For those queries that join many tables, Oracle can spend a significant amount of time determining the proper order to join the tables together.


Evaluating table join order

The most expensive step in the SQL preparation process is the generation of the execution plan, particularly when dealing with a query with multiple joins. When Oracle evaluates table join orders, it must consider every possible combination of tables. For example, a six-way table join has 720 (permutations of 6, or 6 * 5 * 4 * 3 * 2 * 1 = 720) possible ways that the tables can be joined together. This permutation issue becomes even more pronounced when you have more than 10 joined tables in a query: For a 15-way table join, there are over one trillion (1,307,674,368,000 to be exact) possible query permutations that must be evaluated.

Set a limit with the optimizer_search_limit parameter

You can control this situation using the optimizer_search_limit parameter, which specifies the maximum number of table join combinations that will be evaluated by the optimizer. The default value of five means that 5 factorial (5!) possibilities will be considered.

Use of this parameter prevents the optimizer from spending an inordinate amount of time evaluating every possible table join combination. If the number of tables in the query is less than the optimizer_search_limit, the optimizer examines all possible table join combinations.

For example, a query joining five tables would have 120 (5! = 5 * 4 * 3 * 2 * 1 = 120) possible table join combinations, so if the optimizer_search_limit is five (the default) the optimizer would evaluate all 120 of these possibilities. The optimizer_search_limit parameter also controls the threshold for invoking a star join hint. A star hint will be honored when the number of tables in the query is less than the optimizer_search_limit.

The optimizer_max_permutations parameter

The optimizer_max_permutations initialization parameter defines the upper boundary for the maximum number of permutations considered by the optimizer, and is dependent on the optimizer_search_limit initialization parameter. The default value for optimizer_max_permutations is 80,000.

The optimizer_search_limit and optimizer_max_permutations parameters work together to place an upper limit on the number of permutations the optimizer will consider: The optimizer will generate possible table join permutations until the value specified by optimizer_search_limit or optimizer_max_permutations is exceeded. Once the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost.

Specify a join order with the ordered hint

You can set an upper limit on the number of evaluations the optimizer will perform. But even with a very high value of allowed permutation evaluations, there is a significant chance that the optimizer may give up too early on complex queries. Recall my example of the 15-join query with over one trillion possible join combinations. If the optimizer were to stop after evaluating 80,000 combinations, it would only have evaluated 0.000006 percent of the possible permutations, and would probably not have located the optimal join order for this large query.

One of the best ways around this problem in Oracle SQL is to manually specify the table join order. The rule to follow here is join the tables together so that the smallest solution set is created as quickly as possible, usually by joining into the table with the most restrictive WHERE clause first.

The code below is an example of a query execution plan that is forced to perform a nested loop join with a parallel query on a table called emp. Note that I have invoked the ordered hint to direct the optimizer to evaluate the tables in the order they are presented in the WHERE clause.
 
select /*+ ordered use_nl(bonus) parallel(e, 4) */
   e.ename,
   hiredate,
   b.comm.
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

This example requests that the optimizer join the tables in the order that they are specified in the SQL statement?s FROM clause, with the first table in the  FROM clause specifying the driving table. Ordered hints are commonly used in conjunction with other hints to ensure that multiple tables are joined in their proper order. Its use is also very common in tuning data warehouse queries that join more than four tables together.

As another example, the following query uses an ordered hint to join the tables in a specific order: emp, dept, sal, and finally, bonus. I further refine the execution plan by specifying that the emp to dept join should use a hash join and the sal to bonus join should use a nested loop join.
 
select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
from
   emp,
   dept,
   sal,
   bonus
where . . .

 

Practical suggestions for optimizer permutations

In reality, it's more efficient to reduce the size of the optimizer_max_permutations parameter in production environments and always use optimizer plan stability or stored outlines to prevent time-consuming reparsing of queries with large numbers of table joins. Once the best table join order has been found, you can make it permanent by manually specifying the join order for the tables by adding the ordered hint to the query in question and saving it?s stored outline.

When you plan to use optimizer plan stability to make the execution plan permanent in this fashion, it is acceptable to temporarily set the optimizer_search_limit up to the number of tables in your query to allow all possible table join orders to be considered. You could then tune the query by reordering the table names in the WHERE clause, and use the ordered hint with stored outlines to make the change permanent. In queries involving four or more tables, the ordered hint and stored outlines eliminate the time-consuming task of evaluating SQL parses for table join orders, thus improving the speed of the query.

Once the optimal join order has been determined, using the ordered hint overrides the optimizer_search_limit and optimizer_max_permutations parameters. The ordered hint requests that the tables be joined in the order that they appear in the FROM clause of the query, so the optimizer doesn?t enter the picture.

As an Oracle professional, you know that there may be a significant start-up delay the first time that a SQL statement enters the library cache. But a savvy Oracle DBA and developer can change the table search limit parameters or specify the table join order manually using the ordered hint, dramatically reducing the time needed to optimize and execute a new query.
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 
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