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