Introduction to three practical solutions for Oracle to improve SQL Execution Efficiency

If you want to improve the execution efficiency of SQL operations in Oracle, you must first consider optimizing the actual application business logic, in addition, Oracle provides practical application methods at the technical level. The following articles mainly show you three useful methods for improving SQL Execution efficiency in Oracle:

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, 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 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. 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. Three Ways to Improve SQL Execution efficiency in Oracle: Using 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 in Oracle is to forcibly replace a parameter in Oracle. This parameter controls when evaluating a query, the number of possible merge values evaluated by the consumption-based optimizer.

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.

The above are just some tips for Oracle DBAs to optimize the SQL query performance of Oracle database applications.