Data Management

Boost Oracle performance with intelligent optimizer limits

The biggest performance hit when executing SQL is the time it takes Oracle to optimize the query. Find out three ways to improve the performance of the optimizer for queries with multiple joins.

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.

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. Generate execution plan—Oracle uses cost-based optimizer algorithms and statistics in the data dictionary to determine the optimal execution plan.
  5. 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. 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.

Another tool: 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) */
   emp e,
   bonus b
   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) */
where . . .


Practical suggestions
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.

Editor's Picks