Oracle Optimization of Data Warehouse Queries Oracle Tips by Burleson Consulting

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

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 join 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 5, 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 join permutations until the higher of these two values is met. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining six tables will exceed the optimizer_search_limit but still may spend expensive time evaluating all 6! (720) possible table join orders because the optimizer_max_permutations parameter has not been exceeded with its default value of 80,000.

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 using 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 their specified order 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.