Improving Oracle performance limits with the Smart Optimizer

oracle| Performance | optimization

Improving Oracle performance limits with the Smart Optimizer
The time spent preparing new SQL statements is the most important component of the execution time for Oracle SQL statements. But by understanding the mechanism within Oracle to generate execution plans, you can control the amount of time that Oracle spends evaluating the connection order and can improve query performance in general.
Preparing to execute an SQL statement
When the SQL statement enters Oracle's library cache, the following steps are performed before the statement is ready to execute:

1 grammar check: Check that the SQL statement is spelled correctly and word order.

2 Semantic Analysis: Verify the names of all tables and columns that are inconsistent with the data dictionary.

3) Contour Storage check: Check the data dictionary to determine if the SQL statement's profile already exists.

4 Build the Execution plan: Use the cost-based optimization rules and statistics in the data dictionary to determine the best execution plan.

5 Establish binary code: Based on execution plan, Oracle generates binary execution code.

Once the SQL statement has been prepared for execution, future execution will occur quickly because Oracle approves the same SQL statement and reuses the execution of those statements. However, for systems that generate special SQL statements or SQL statements that embed literal variables, the generation time of the SQL execution plan is important, and the previous execution plan is often not reusable. For queries that are connected to many tables, Oracle takes a significant amount of time to detect the appropriate sequence of connections to those tables.
Evaluate the connection order of the tables
The most expensive step in preparing an SQL statement is to build an execution plan, especially for queries with multiple table joins. When Oracle evaluates the connection order for a table, it must take into account all possible connections between tables. For example: Six tables are connected between 720 (6 factorial, or 6 * 5 * 4 * 3 * 2 * 1 = 720) of possible connection lines. When a query contains more than 10 tables, the problem of routing becomes more pronounced. For connections between 15 tables, the possible query arrangement that needs to be evaluated is more than 1 trillion (the exact number is 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 to be evaluated by the optimizer. Using this parameter, we will be able to prevent the optimizer from consuming an indefinite amount of time to evaluate all possible combinations of connections. If the number of tables in the query is less than the Optimizer_search_limit value, the optimizer examines all possible combinations of connections.

For example: A query with five table connections will have 120 (5!). = 5 * 4 * 3 * 2 * 1 = 120) A possible combination of connections, so if Optimizer_search_limit equals 5 (the default), the optimizer evaluates all 120 possibilities. The Optimizer_search_limit parameter also controls the threshold for calling a connection hint with an asterisk. When the number of tables in a query is more than optimizer_search_limit hours, the hint with an asterisk will be given precedence.
Another tool: Parameter optimizer_max_permutations
The initialization parameter optimizer_max_permutations defines the upper limit of the number of combinations considered by the optimizer and relies on the initial parameter optimizer_search_limit. The default value for Optimizer_max_permutations is 80,000.

Parameter Optimizer_search_limit and optimizer_max_permutations to determine the upper limit of the number of combinations considered by the optimizer: unless (the number of tables or combinations) exceeds the parameter optimizer_search_limit or Optimizer_max_permutations, or the optimizer will generate all possible combinations of connections. Once the optimizer stops the evaluation table's join combination, it chooses the lowest cost combination.
Specify the connection order using the ordered hint
You can set the maximum number of evaluations that the optimizer performs. But even with a highly valued ranking assessment, we still have an important opportunity to enable the optimizer to discard complex queries as early as possible. Recall the example of a 15-connection query that will have more than 1 trillion combinations of connections. If the optimizer stops after evaluating 80,000 combinations, it evaluates only 6e-06% of the possible combinations and may not have found the optimal connection order for this huge query.

The best way to resolve this problem in Oracle SQL is to manually specify the connection order of the tables. To create a minimal set of solutions as soon as possible, the rule followed is to combine tables, usually using the most restrictive where clause to join the table.

The following code is an example of a query execution plan that enforces nested circular joins on the associated query of the EMP table. Note that I have used the ordered hints to directly optimize the order of the table evaluation, and ultimately they appear on 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

This example requires the optimizer to concatenate the tables specified in the FROM clause of the SQL statement sequentially, specifying the driver table in the first table in the FROM clause. Ordered hints are often used to combine with other hints to ensure that multiple tables are connected in the correct order. Its use is more in reversing the number of connection tables in more than four data warehouse queries.

In another example, the following query uses the ordered hint to join the table in the specified order: EMP, Dept, Sal, and finally bonus. I further refine the execution plan by specifying an EMP to dept using a hash connection and using a nested loop connection with Sal to bonus.

Select/*+ ordered Use_hash (EMP, dept) use_nl (SAL, bonus) * *

From

Emp

Dept

Sal

Bonus

where ...
Practice Suggestions
In fact, a more efficient approach is to reduce the size of the Optimizer_max_permutations parameter in the product environment and always use a stable optimization plan or storage profile to prevent time-consuming queries that contain a large number of connections. Once you find the best connection order, you can make it persistent by adding ordered prompts to the current query and saving its storage profile by manually specifying the connection order for the tables.

When you intend to use the optimizer to stabilize the plan, you can make the execution plan persistent in the following ways, temporarily setting Optimizer_search_limit to the number of tables in the query, allowing the optimizer to consider all possible connection orders. You can then adjust the query by ordered the name of the table in the WHERE clause and using the hint to persist the changes with the storage outline. When you include more than four tables in a query, ordered hints and storage outlines will eliminate time-consuming tasks that evaluate the sequence of SQL connections, thereby increasing the speed of queries.

Once the optimal connection order is detected, we can use the ordered prompt to overload the Optimizer_search_limit and Optimizer_max_permutations parameters. The ordered prompts the table to connect in the order in which they appear in the FROM clause, so the optimizer does not join the description.

As an Oracle professional, you should know that there may be a significant startup delay when the SQL statement first enters the library cache. But smart Oracle DBAs and developers can dramatically reduce the time required to optimize and execute new queries by changing the table's search restriction parameters or by using the ordered prompt to manually specify the connection order of the tables.