Use smart optimization restrictions to improve Oracle Performance

The most important component of Oracle SQL running time is the time spent preparing new SQL statements for execution. However, if you understand the Inherent Mechanism of executable plan generation, you can control the time spent by Oracle in evaluating the table connection sequence and improve query performance in general.
　　
Prepare the SQL statements for execution
　　
　　
After an SQL statement enters the Oracle database cache and is executed, the following events will occur in sequence:
　　
Syntax check -- check whether the spelling and Word Order of the SQL statement are correct.
Semantic parsing-Oracle verifies all tables and columns based on the data dictionary ).
Saved outline check-Oracle check dictionary to check whether a saved outline (stored outline) exists for the SQL statement ).
Generate execution plan-Oracle determines how to generate the optimal execution plan based on a cost-based optimization algorithm and statistical data in the data dictionary.
Generate binary code-Oracle generates executable binary code based on the execution plan.
Once you begin preparing to execute the SQL statement, the above process will be executed soon, because oracle can identify the same SQL statement and reuse the corresponding executable code for the same SQL statement. However, when the system that generates the ad hoc SQL statement and the literal value embedded in the SQL statement become quite long, in addition, previous execution plans are often unavailable for reuse. For queries that involve many tables, it may take a long time for Oracle to determine the order of connecting these tables.
　　
Evaluate the order of join tables
The time to generate an executable plan is often the largest overhead part of the SQL preparation process, especially when queries with connections to multiple tables are processed. When Oracle evaluates the join sequence of a table, it must consider each possible sort. For example, when six tables need to be connected, Oracle needs to consider 720 (6 in number, that is, 6 × 5 × 4 × 3 × 2 × 1 = 720) sort possible connections. When the number of tables to be connected exceeds 10, This sorting problem becomes very prominent: if there are 15 tables to be connected, in this case, the possible query order is more than 1 trillion (the exact value is 1,307,674,368,000 ).
　　
Set the limit in the optimizer_search_limit Parameter
You can use the optimizer_search_limit parameter to control the occurrence of the above problem. This parameter is used to specify the maximum number of table join orders evaluated by the optimizer. This parameter prevents the optimizer from spending extra time evaluating all possible table join orders. If the number of tables in the query is less than or equal to optimizer_search_limit, the optimizer checks the connection mode of all possible tables.
　　
For example, a total of 120 types of queries involving five tables (5! = 5 × 4 × 3 × 2 × 1 = 120) possible connection sequence, so if the value of optimizer_search_limit is set to 5 (default ), then the optimizer will consider all the 120 possible connection sequence. The optimizer_search_limit parameter is also used to control the threshold value of star join hint. When the number of tables involved in the query is less than the set value of optimizer_search_limit, the start connection indicator is set.
Another tool: optimizer_max_permutations Parameter
The optimizer_max_permutations initial parameter is used to set the upper bound of the optimizer's optimization range (that is, the maximum number of table join sequences to be considered). It depends on the initial parameter optimizer_search_limit. The default value of optimizer_max_permutations is 8000.
　　
　　
　　
The optimizer_search_limit and optimizer_max_permutations parameters are used together to set the upper limit of the number of permutation values that the optimizer considers. The optimizer_search_limit or optimizer_max_permutations optimizer_permutations is generated continuously until the number of table connections reaches the maximum value. Once the optimizer stops generating a new possible connection arrangement, it selects the smallest sort from it.
　　
Specify a sort of connections with sorted indicators
You can set the upper limit of the number of arrays evaluated by the optimizer. However, in complex cases, the Optimizer may have stopped optimizing even if a large number of allowed arrays are not found. Let's look back at the example above (more than 1 trillion types of tables need to be connected ). If you set the optimizer to consider 80,000 types of sorting, this only takes 0.000006% of all possibilities into consideration, and the Optimizer may not reach the optimal arrangement.
　　
The best way to solve this problem in Oracle SQL is to manually specify a table connection sequence. The general principle to be followed here is that the table connection sequence should be established as soon as possible so that the query plan can be established. Generally, the where limit clause is used in SQL statements.
　　
The following uses a parallel query of a table named EMP as an example. The code in this example forces the query plan to execute a nested loop join ). Note that I used the sorted indicator to guide the optimizer to evaluate the join sequence of the table given 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
;
　　
The preceding example requires the optimizer to connect tables according to the sequence specified by the from clause in the SQL statement, and specify the first table in the from clause as the driving table ). Sorted indicators are often used together with other instructions to ensure that multiple tables are connected in the appropriate order. This is often the case when a data warehouse query involves more than four tables.
　　
Here is another example. In this example, we use an ordered hint to sort the table in a specific order (first EMP, then Dep and Sal, finally, bonus) is connected. To further improve the execution plan, I specify the connections from the EMP table to the dept table to use Hash connections, and the Sal table to the bonus table to use nested loop connections.
　　
Select/* + ordered use_hash (EMP, Dept) use_nl (SAL, bonus )*/
From
EMP,
Dept,
Sal,
Bonus
Where...
　　
Practical application suggestions
In practical applications, reduce the optimizer_max_permutations parameter and use the saved optimization plan or saved outline (This avoids re-parsing the actual query cost when many tables are involved in the query) it will be more efficient. Once the best table connection sequence is found, you can manually specify the table connection sequence (indicated by sorting) and save the outline, so that the table connection sequence is permanently saved.
　　
　　
　　
When executing a new query, You can first set optimizer_search_limit to the number of tables involved in the query, so that the optimizer will find the best one from all the join orders. When you execute this query later, you can sort the table names in the best connection order in the WHERE clause, and set the saved instructions and saved outlines, in this way, tables can be connected in the optimal order without the need to repeat the various possible sorting. In this way, the query speed will be significantly improved.
　　
The priority of sorted indicators is higher than that of optimizer_search_limit and optimizer_max_permutations. If the sorted indicator is set, the table is connected in the order given by the from clause in the query command. In this way, the optimizer does not optimize the join sequence of the table.
　　
As an oracle professional, you should know that there is an obvious start delay in the import of SQL statements into the cache. However, smart Oracle Database administrators and Oracle developers can change the table search restriction parameters or use sorted instructions to manually specify the table connection sequence, this greatly reduces the time required to optimize and execute new queries.