The first test of oracle execution plan improves query performance using the Oracle execution plan mechanism

 

 

 

The first figure shows the execution plan for executing SQL statements directly on the physical table, and the second figure shows the items in the money table.
The execution plan made on the view shows that the efficiency has indeed improved.

Cardinality
: Indicates the number of records in a result set. The number of records may be a join.
Or select
Or group
By
The result set generated. It can be used as the next input in the execution plan. Therefore, in a more complex execution plan, you can see Cardinality.
And Cost
Changes in each step.

Cost


: This is the most mentioned concept in cost optimization, that is, executing an SQL statement.
The cost of a statement, which is usually represented by a numerical value. The common concept is that when the cost
The higher the execution cost, the higher the SQL
The longer the resource and time required by the statement. But cost
The specific value itself does not have a very clear meaning, usually its value represents an SQL
The overall situation of resources that may be consumed (cpu
, Io
, Memory, etc.), but it does not correspond to a specific unit. We generally think that if we optimize it, we can find the cost
The SQL statement is reduced.
The actually consumed resources will be reduced.

The text after ______ is taken from the Internet:

The time consumed when preparing to use the Oracle execution plan mechanism to improve query performance new SQL statements is the most important component of the Oracle SQL statement execution time. However, by understanding the internal execution plan generation mechanism of Oracle, you can control the number of times that Oracle spends evaluating the connection sequence and improve query performance in general.

Prepare to execute SQL statements

After an SQL statement enters the Oracle database cache, the following steps are performed before the statement is ready to be executed:

1) syntax check: Check whether SQL statements are correctly spelled and sorted.
2) semantic analysis: verifies the names of all tables and columns that are inconsistent with the data dictionary.
3) contour Storage check: Check the data dictionary to determine whether the outline of the SQL statement already exists.
4) generate an execution plan: use cost-based optimization rules and statistical tables in the data dictionary to determine the best execution plan.
5) create Binary Code: Based on the execution plan, Oracle generates binary Execution Code.

Once an SQL statement is prepared for execution, subsequent execution will happen soon, because Oracle recognizes the same SQL statement and reuse the execution of those statements. However, for the generation of special
SQL statements, or systems embedded with SQL statements of text variables, the generation time of the SQL Execution Plan is very important, and the previous execution plan cannot be reused. Queries that connect many tables
Oracle needs to spend a lot of time checking the proper order of connecting these tables.

Evaluation Table connection Sequence

During SQL statement preparation, the most costly step is to generate an execution plan, especially for queries with multiple table connections. When Oracle evaluates the connection sequence of a table, it must consider the order between tables.
All possible connections. For example, the connection between six tables is 720 (6 factorial, or 6*5*4*3*2*1 =
720) possible connection lines. When a query contains connections of more than 10 tables, the problem of sorting becomes more significant. For connections between 15 tables, the possible query arrangement to be evaluated will exceed 1
Trillions (accurate numbers are 1,307,674,368,000.

Use the optimizer_search_limit parameter to set the limit.

By using the optimizer_search_limit parameter, you can specify the maximum number of connection combinations that the optimizer uses to evaluate. With this parameter, we will be able to prevent Optimization
It consumes an indefinite amount of time to evaluate all possible connection combinations. If the number of tables in the query is smaller than the value of optimizer_search_limit, the optimizer checks all possible connections.
Integration.

For example, there will be 120 (5! = 5*4*3*2*1 =
120) possible connection combinations, So If optimizer_search_limit is equal to 5 (default), the optimizer evaluates all 120 possibilities.
The optimizer_search_limit parameter also controls the threshold value for calling the connection prompt with an asterisk. When the ratio of the number of tables in the query is
Optimizer_search_limit hour, the asterisk prompt will be given priority.

Another tool: optimizer_max_permutations

The initialization parameter optimizer_max_permutations defines the maximum number of combinations that the optimizer_search_limit considers. The default value of optimizer_max_permutations is 80,000.

The optimizer_search_limit and optimizer_max_permutations parameters are used together to determine the number of combinations considered by the optimizer.
Limit: Unless (the number of tables or combinations) exceeds the value of optimizer_search_limit or
Value set by optimizer_max_permutations. Otherwise, the optimizer generates all possible connection combinations. Once the optimizer stops the join combination of the evaluation table, it selects the most cost
Low combination.

Use the ordered prompt to specify the connection Sequence

You can set the maximum number of evaluations performed by the optimizer. However, even with a highly valuable ranking evaluation, we still have an important opportunity for the optimizer to give up complex queries as soon as possible. Recall
There are 15 connection query examples. There will be more than 1 trillion connection combinations. If the optimizer stops after evaluating 80,000 combinations, it only evaluates 0.000006% of the possible groups.
And perhaps no optimal connection sequence for this huge query.

The best way to solve this problem in Oracle SQL is to manually specify the table connection sequence. To create the smallest solution set as soon as possible, the rule followed here is to combine tables. Generally, tables are connected using the WHERE clause with the strictest restrictions.

The following code is an example of a query execution plan. In this example, nested loop connections are enforced in the join query of the EMP table. Note that I have used the ordered prompt to directly optimize the evaluation sequence of the table, and ultimately they are displayed 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

In this example, the optimizer is required to connect to the table specified in the from clause of the SQL statement in sequence, and the first table in the from clause specifies the driver table. The ordered prompt is usually used together with other prompts to ensure that multiple tables are connected in the correct order. It is used to query data warehouses with more than four tables.

In another example, the following query uses the ordered prompt to connect tables in the specified order: EMP, DEPT, Sal, and bonus. I specify EMP to dept to use Hash connections and Sal to bonus to use nested loop connections to further refine the execution plan.

select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */ from emp, dept, sal, bonus where . . .

Practical suggestions

In fact, a more efficient approach is to reduce the size of the optimizer_max_permutations parameter in the product environment, and always use a stable optimization plan or storage wheel.
To prevent time-consuming queries that contain a large number of connections. Once the optimal connection sequence is found, you can add the ordered prompt to the current query and save its storage profile for these table workers.
Specify the connection order to make it persistent.

When you plan to use the optimizer to stabilize the plan, you can use the following method to make the execution plan persistent and temporarily set optimizer_search_limit to the number of tables in the query.
To allow the optimizer to consider all possible connection sequence. Then, by re-arranging the table name in the WHERE clause and using the ordered prompt to make the change persistent together with the storage outline
Full query. When a query contains more than four tables, the ordered prompt and storage profile exclude time-consuming tasks for evaluating SQL connection sequence parsing, thus improving the query speed.

Once the optimal connection sequence is detected, we can use the ordered prompt to overload optimizer_search_limit and
The optimizer_max_permutations parameter. The ordered prompt requires the table to be connected in the order they appear in the FROM clause, so the optimizer is not added
Description.

As an Oracle professional, you should know that there may be a major startup delay when the SQL statement is first written into the cache. However, smart Oracle DBAs and developers can change the table search restriction parameters or use the ordered prompt to manually specify the table connection sequence, significantly reducing the time required to optimize and execute new queries.