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




Initialization Parameters that Affect Cost-Based Optimizer Behavior
Oracle Tips by Burleson

There are many initialization parameters that affect the behavior of the cost-based optimizer and SQL hints. Often, there are prerequisite initialization parameters that are required in order for a hint to be invoked. For example, the star_transformation_enabled initialization parameter must be set to TRUE or a star hint will have no effect on the execution plan for the SQL.

Other initialization parameters affect the threshold for invoking a hint. For example, the hash_area_size initialization parameter controls the threshold for the invocation of a use_hash hint. In any case, all of the following initialization parameters affect the costing that determines the execution plan for the CBO.

Initialization Parameters That Affect Table Joins

While we will be going into great detail on tuning table joins in Chapter 16, you need to understand the relationship between the initialization parameters and table join behavior. The following initialization parameters can be used to direct the CBO to use different table join techniques.

Limiting the Number of SQL Query Permutations

Oracle provides initialization parameters to control the amount of work performed by the cost-based optimizer when evaluating a query. While parsing is quite fast for simple queries, complex queries with more than six tables can parse for many minutes while Oracle evaluates every possible table join combination.

Sometimes, data warehouse DBAs are perplexed when they find that a 15-way table join takes 30 minutes to parse! This is because there are 15 factorial possible permutations of the query and over one trillion (1,307,674,368,000) query permutations.

While the ultimate solution is to employ stored outlines to remove the parsing phase, Oracle has two important initialization parameters that work together to control the number of possible execution plans generated by the Oracle optimizer.

TIP: If you do not have complex SQL queries that join five or more tables together, you need not be concerned with the optimizer_search_limit or optimizer_max_permutations parameters. These only apply when Oracle is computing possible table join combinations for queries with large numbers of tables.

The optimizer_search_limit Parameter

The optimizer_search_limit 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.

TIP: 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 a high 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.

Note: 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.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


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. 

Hit Counter