Oracle's approach to improving SQL execution efficiency

Oracle offers a number of ways to reduce the time spent parsing Oracle SQL expressions, which can drag down the system's performance when performing complex queries with a large number of execution plans. Now let's take a brief look at several of these methods.

1. Use ordered Tips

Oracle must spend a significant amount of time parsing multiple table merges to determine the best order for table merging. If the SQL expression involves seven or more table merges, it can sometimes take more than 30 minutes to parse, because Oracle must evaluate all possible sequences of tables merging. Eight tables will have 40,000 different orders. Ordered this hint (hint) with other prompts can produce an appropriate merging order.

Ordered this hint requires that the table listed in the SQL expression FROM clause be merged in the order specified, and the first table in the FROM clause specifies the driver table (driving table). The driver table should be a table that returns a minimum number of rows. Using the ordered hint skips time-consuming and resource-intensive profiling operations and accelerates Oracle SQL execution.

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 forces a nested loop to merge with parallel queries against the EMP table. Note that I have used the ordered hint to direct Oracle to evaluate the table in the order listed in the WHERE clause.

2. Use Theordered_predicates

The ordered_predicates hint is specified in the query's WHERE clause and is used to specify the order in which Boolean judgments (Boolean predicate) are evaluated. In the absence of Ordered_predicates, Oracle uses the following steps to evaluate the order of SQL judgments:

The evaluation of a subquery is preceded by a Boolean condition in the outer where clause.

All Boolean conditions without a built-in function or subquery are evaluated in the reverse order in which they are in the WHERE clause, that is, the last Judgment is evaluated first.

Boolean judgments with built-in functions are arranged incrementally according to their expected evaluation values.

You can use Ordered_predicates hints to force the substitution of these default evaluation rules, and the items in your WHERE clause are evaluated in the order in which they appear in the query. In the case where the Pl/sql function is used in the query's WHERE clause, a ordered_predicates hint is usually used. It is also useful in this case if you know the most restrictive judgments and want Oracle to first evaluate those judgments. Usage Hint: You cannot use the Ordered_predicates prompt to save the order in which the index keys are judged and evaluated.

3, limit the number of forms combined assessment

The last way to improve the performance of SQL profiling is to force a replacement for Oracle's parameter, which controls the number of possible merges that are evaluated by the consumption optimizer when evaluating a query.

Optimizer_search_limit This 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 spending time looking for the best consolidation order. Optimizer_search_limit also controls the thresholds used to invoke the star join hint, when the number of tables in the query is lower than Optimizer_search_limit (the default value is 5), the star tip is patronized.