Three Methods of Improving the Execution Efficiency of SQL by Oracle
- 2021-09-25 00:01:52
- OfStack
Oracle provides a variety of ways to reduce the time spent parsing Oracle SQL expressions, which can drag down system performance when executing complex queries with a large number of execution plans. Now let's briefly look at some of these methods.
1. Use ordered hints
Oracle must spend a lot of time parsing multiple table merges to determine the optimal order of table merging. If an SQL expression involves seven or more table merges, it sometimes takes more than 30 minutes to parse, because Oracle must evaluate all possible order of table merges. Eight tables will have more than 40,000 orders. Ordered This hint (hint) and other hint 1 are used to produce a suitable merging order.
The Ordered hint asks the tables listed in the SQL expression FROM to be merged in the specified order, and the first table in the FROM specifies the driver table (driving table). The driver table should be a table that returns the minimum number of rows. Using the ordered hint skips time-consuming and resource-intensive profiling operations and speeds up the execution of Oracle and SQL.
Listing A is as follows:
The following is a reference fragment:
Listing A
select /*+ ordered use_nl(bonus)
parallel(e, 4) */ e.ename, hiredate, b.comm from emp e, bonus b
where e.ename = b.ename ;
Listing A is an example of a complex query that is forced into a nested loop to merge with a parallel query on an emp table. Note that I have used the ordered hint to guide Oracle to evaluate tables in the order listed in the WHERE clause.
2. Use theordered_predicates
The ordered_predicates hint is specified in the WHERE clause of the query and is used to specify the order in which Boolean decisions (Boolean predicate) are evaluated. In the absence of ordered_predicates, Oracle uses the following steps to evaluate the order of SQL decisions:
The evaluation of the subquery precedes the Boolean condition in the outer WHERE clause.
All Boolean conditions without built-in functions or subqueries are evaluated in the reverse order in the WHERE clause, i.e. the last decision is evaluated first.
Boolean decisions with built-in functions for each decision are arranged incrementally based on their expected evaluation values.
You can use the ordered_predicates hint to force these default evaluation rules, and the items in your WHERE clause will be evaluated in the order in which they appear in the query. The ordered_predicates hint is typically used when the PL/SQL function is used in the WHERE clause of a query. It is also useful if you know the most restrictive judgments and want Oracle to evaluate them first. Usage Tip: You cannot use the ordered_predicates Tip to save the order in which the key is evaluated.
3. Limit the number of table consolidation evaluations
The last way to improve the profiling performance of SQL is to forcibly replace a parameter of Oracle, which controls the number of possible merges evaluated by the consumption-based optimizer when evaluating a query.
The optimizer_search_limit parameter specifies the maximum number of table merge combinations, which will be evaluated when Oracle attempts to determine the best way to merge multiple tables. This parameter helps prevent the optimizer from spending more time evaluating the possible merge order rather than looking for the best merge order. optimizer_search_limit also controls the threshold for invoking star join hints, which are patronized when the number of tables in the query is lower than optimizer_search_limit (its default value is 5).
1. Use ordered hints
Oracle must spend a lot of time parsing multiple table merges to determine the optimal order of table merging. If an SQL expression involves seven or more table merges, it sometimes takes more than 30 minutes to parse, because Oracle must evaluate all possible order of table merges. Eight tables will have more than 40,000 orders. Ordered This hint (hint) and other hint 1 are used to produce a suitable merging order.
The Ordered hint asks the tables listed in the SQL expression FROM to be merged in the specified order, and the first table in the FROM specifies the driver table (driving table). The driver table should be a table that returns the minimum number of rows. Using the ordered hint skips time-consuming and resource-intensive profiling operations and speeds up the execution of Oracle and SQL.
Listing A is as follows:
The following is a reference fragment:
Listing A
select /*+ ordered use_nl(bonus)
parallel(e, 4) */ e.ename, hiredate, b.comm from emp e, bonus b
where e.ename = b.ename ;
Listing A is an example of a complex query that is forced into a nested loop to merge with a parallel query on an emp table. Note that I have used the ordered hint to guide Oracle to evaluate tables in the order listed in the WHERE clause.
2. Use theordered_predicates
The ordered_predicates hint is specified in the WHERE clause of the query and is used to specify the order in which Boolean decisions (Boolean predicate) are evaluated. In the absence of ordered_predicates, Oracle uses the following steps to evaluate the order of SQL decisions:
The evaluation of the subquery precedes the Boolean condition in the outer WHERE clause.
All Boolean conditions without built-in functions or subqueries are evaluated in the reverse order in the WHERE clause, i.e. the last decision is evaluated first.
Boolean decisions with built-in functions for each decision are arranged incrementally based on their expected evaluation values.
You can use the ordered_predicates hint to force these default evaluation rules, and the items in your WHERE clause will be evaluated in the order in which they appear in the query. The ordered_predicates hint is typically used when the PL/SQL function is used in the WHERE clause of a query. It is also useful if you know the most restrictive judgments and want Oracle to evaluate them first. Usage Tip: You cannot use the ordered_predicates Tip to save the order in which the key is evaluated.
3. Limit the number of table consolidation evaluations
The last way to improve the profiling performance of SQL is to forcibly replace a parameter of Oracle, which controls the number of possible merges evaluated by the consumption-based optimizer when evaluating a query.
The optimizer_search_limit parameter specifies the maximum number of table merge combinations, which will be evaluated when Oracle attempts to determine the best way to merge multiple tables. This parameter helps prevent the optimizer from spending more time evaluating the possible merge order rather than looking for the best merge order. optimizer_search_limit also controls the threshold for invoking star join hints, which are patronized when the number of tables in the query is lower than optimizer_search_limit (its default value is 5).