How Oracle improves SQL Execution Efficiency

Oracle provides a variety of methods to reduce the time spent profiling OracleSQL expressions. The profiling process will drag down the system performance when executing complex queries with a large number of execution plans. Now let's take a brief look at several of these methods. 1. Use ordered to prompt that Oracle must spend a lot of time analyzing the merging of multiple tables to determine the table combination.

Oracle provides a variety of methods to reduce the time spent profiling Oracle SQL expressions. The profiling process will drag down the system performance when executing complex queries with a large number of execution plans. Now let's take a brief look at several of these methods. 1. Use ordered to prompt that Oracle must spend a lot of time analyzing the merging of multiple tables to determine the table combination.

Oracle provides a variety of methods to reduce the time spent profiling Oracle SQL expressions. The profiling process will drag down the system performance when executing complex queries with a large number of execution plans. Now let's take a brief look at several of these methods.

1. ordered prompt

Oracle must spend a lot of time analyzing the merging of multiple tables to determine the optimal order of table merging. If an SQL expression involves seven or more tables for merging, it may take more than 30 minutes to parse the statement, because Oracle must evaluate all possible order of table merging. There will be more than 40,000 orders in eight tables. The hint of Ordered can be used together with other prompts to generate a proper merging order.

The Ordered prompt requires that tables listed in the SQL expression FROM be merged in the specified order. The first table in the FROM statement specifies the driving table ). The driver table should be the table that returns the minimum number of rows. The ordered prompt skips the time-consuming and resource-consuming profiling operations and accelerates the execution of Oracle SQL.

Listing A is as follows:

The following is a reference clip:

Listing

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. This query is forced to carry out A nested loop to merge with parallel queries on the emp table. Note that I have used the ordered prompt to guide Oracle to evaluate the table in the order listed in the WHERE clause.

2. Use theordered_predicates

The ordered_predicates prompt specifies in the WHERE clause of the query, and is used to specify the order in which the Boolean predicate is evaluated. Without ordered_predicates, Oracle uses the following steps to evaluate the order of SQL judgment:

The evaluation of a subquery is prior to the Boolean condition in the outer WHERE clause.

All Boolean conditions without built-in functions or subqueries are evaluated in the reverse order of the WHERE clause. That is, the last condition is evaluated first.

Boolean judgments with built-in functions are sorted in ascending order based on their estimated values.

You can use the ordered_predicates prompt to forcibly replace these default evaluation rules. The items in your WHERE clause will be evaluated in the order they appear in the query. When PL/SQL functions are used in the WHERE clause, the ordered_predicates prompt is usually used. If you know the most restrictive judgments and want Oracle to evaluate them first, it is also very useful in this case. Usage tips: you cannot use the ordered_predicates prompt to save the order in which the index key is evaluated.

3. Limit the number of table merge evaluations

The last way to improve SQL profiling performance is to forcibly replace an Oracle parameter, which controls the number of possible mergers evaluated by the consumption optimizer when evaluating a query.

The optimizer_search_limit parameter specifies the maximum number of table merging combinations, which will be evaluated when Oracle tries to determine the best way to merge multiple tables. This parameter helps prevent the optimizer from spending more time evaluating the possible merging sequence, rather than spending time searching for the optimal merging sequence. Optimizer_search_limit also controls the threshold value used to call the star join prompt. When the number of tables in the query is less than optimizer_search_limit (the default value is 5), the star prompt will be patronized.