How to use a smart optimizer to improve Oracle Performance

The time spent preparing new SQL statements is the most important part of the execution time of Oracle SQL statements. However, by understanding the internal execution plan generation mechanism of Oracle, you can control the number of times that Oracle spends evaluating the connection sequence and improve query performance in general.

Prepare to execute SQL statements

After an SQL statement enters the Oracle database cache, the following steps are performed before the statement is ready to be executed:

1) syntax check: Check whether SQL statements are correctly spelled and sorted.
2) semantic analysis: verifies the names of all tables and columns that are inconsistent with the data dictionary.
3) contour Storage check: Check the data dictionary to determine whether the outline of the SQL statement already exists.
4) generate an execution plan: use cost-based optimization rules and statistical tables in the data dictionary to determine the best execution plan.
5) create Binary Code: Based on the execution plan, Oracle generates binary Execution Code.

Once an SQL statement is prepared for execution, subsequent execution will happen soon, because Oracle recognizes the same SQL statement and reuse the execution of those statements. However, for systems that generate special SQL statements or SQL statements embedded with text variables, the generation time of the SQL Execution Plan is very important, and the previous execution plan cannot be reused. For queries that connect many tables, Oracle needs to spend a lot of time checking the proper order of these tables.

Evaluation Table connection Sequence

During SQL statement preparation, the most costly step is to generate an execution plan, especially for queries with multiple table connections. When Oracle evaluates the table connection sequence, it must consider all possible connections between tables. For example, six tables are connected by a 7206 factorial, or 6*5*4*3*2*1 = 720. When a query contains connections of more than 10 tables, the problem of sorting becomes more significant. For the connection between 15 tables, the possible query arrangement to be evaluated will exceed 1 trillion accurate numbers (1,307,674,368,000.

Use the optimizer_search_limit parameter to set the limit.

By using the optimizer_search_limit parameter, you can specify the maximum number of connection combinations that the optimizer uses to evaluate. With this parameter, we will be able to prevent the optimizer from consuming an indefinite amount of time to evaluate all possible connection combinations. If the number of tables in the query is smaller than the value of optimizer_search_limit, the optimizer checks all possible join combinations.

For example, if there are five tables connected, there will be 1205 queries! = 5*4*3*2*1 = 120) possible connection combinations, So If optimizer_search_limit is equal to 5 by default), The optimizer_search_limit evaluates all 120 possibilities. The optimizer_search_limit parameter also controls the threshold value for calling the connection prompt with an asterisk. When the number of tables in the query is less than optimizer_search_limit, the asterisk (*) prompt is given priority.

Another tool: optimizer_max_permutations

The initialization parameter optimizer_max_permutations defines the maximum number of combinations that the optimizer_search_limit considers. The default value of optimizer_max_permutations is 80,000.

The optimizer_search_limit and optimizer_max_permutations parameters are used together to determine the maximum number of combinations considered by the optimizer_search_limit parameter (unless the number of tables or combinations) exceeds the value set by the optimizer_max_permutations parameter, otherwise, the optimizer generates all possible connection combinations. Once the optimizer stops the join combination of the evaluation table, it selects the lowest cost combination.

Use the ordered prompt to specify the connection Sequence

You can set the maximum number of evaluations performed by the optimizer. However, even with a highly valuable ranking evaluation, we still have an important opportunity for the optimizer to give up complex queries as soon as possible. Recall that there are more than 1 trillion connection combinations in 15 connection queries. If the optimizer stops after evaluating the 80,000 combinations, it only evaluates the 0.000006% possible combinations and may not find the optimal connection sequence for this huge query.

The best way to solve this problem in Oracle SQL is to manually specify the table connection sequence. To create the smallest solution set as soon as possible, the rule followed here is to combine tables. Generally, tables are connected using the WHERE clause with the strictest restrictions.

The following code is an example of a query execution plan. In this example, nested loop connections are enforced in the join query of the emp table. Note that I have used the ordered prompt to directly optimize the evaluation sequence of the table, and ultimately they are displayed 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

In this example, the optimizer is required to connect to the table specified in the FROM clause of the SQL statement in sequence, and the first table in the FROM clause specifies the driver table. The ordered prompt is usually used together with other prompts to ensure that multiple tables are connected in the correct order. It is used to query data warehouses with more than four tables.

In another example, the following query uses the ordered prompt to connect tables in the specified order: emp, dept, sal, and bonus. I specify emp to dept to use Hash connections and sal to bonus to use nested loop connections to further refine the execution plan.

select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */ 　from 　emp, dept, sal, bonus where . . .

Practical suggestions

In fact, a more efficient approach is to reduce the optimizer_max_permutations parameter size in the product environment, and always use a stable optimization plan or storage profile to prevent time-consuming queries containing a large number of connections. Once the optimal connection sequence is found, you can add the ordered prompt to the current query and save its storage profile to manually specify the connection sequence for these tables, to make it persistent.

When you plan to use the optimizer to stabilize the plan, you can use the following method to make the execution plan persistent and temporarily set optimizer_search_limit to the number of tables in the query, this allows the optimizer to consider all possible connection sequence. Then, adjust the query by reorganizing the table name in the WHERE clause and using the ordered prompt to make the change persistent together with the storage outline. When a query contains more than four tables, the ordered prompt and storage profile exclude time-consuming tasks for evaluating SQL connection sequence parsing, thus improving the query speed.

Once the optimal connection sequence is detected, we can use the ordered prompt to overload the optimizer_search_limit and optimizer_max_permutations parameters. The ordered prompt requires the table to be connected in the order they appear in the FROM clause, so the optimizer does not add a description.

As an Oracle professional, you should know that there may be a major startup delay when the SQL statement is first written into the cache. However, smart Oracle DBAs and developers can change the table search restriction parameters or use the ordered prompt to manually specify the table connection sequence, significantly reducing the time required to optimize and execute new queries.