Analyze Oracle's execution plan (explain plan) and optimize the practice of SQL

Oracle-based application system many performance problems are caused by poor application SQL performance, so, SQL performance optimization is very important, analysis and optimization of SQL performance we generally look at the SQL implementation plan, this article on how to understand the implementation plan, And how to optimize SQL by analyzing the execution plan.

I. What is the implementation plan (explain plans)

Execution plan: A description of the execution process or access path of a query statement in Oracle.

Second, how to view the execution plan

1.set Autotrace on

2.explain plan for SQL statement;

Select Plan_table_output from Table (Dbms_xplan.display ());

3. Through 3rd party tools, such as Plsql Developer (F5 View implementation plan), Toad, etc.;

Third, read the implementation plan

1. Explanation of the fields in the execution plan

2. SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;
6. 13 rows have been selected.
14. Execution plan
18. ----------------------------------------------------------
22. Plan Hash value:992080948
26. ---------------------------------------------------------------------------------------
30. | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
34. ---------------------------------------------------------------------------------------
38. | 0 |        SELECT STATEMENT |    |   13 |     988 | 6 (17) | 00:00:01 |
42. |  1 |        MERGE JOIN |    |   13 |     988 | 6 (17) | 00:00:01 |
46. |   2 | TABLE ACCESS by INDEX rowid|    EMP |   14 |     532 | 2 (0) | 00:00:01 |
50. |    3 | INDEX Full SCAN |    Pk_emp |       14 |     | 1 (0) | 00:00:01 |
54. |* 4 |        SORT JOIN |    |   13 |     494 | 4 (25) | 00:00:01 |
58. |* 5 | TABLE ACCESS Full |    EMP |   13 |     494 | 3 (0) | 00:00:01 |
62. ---------------------------------------------------------------------------------------
70. predicate information (identified by Operation ID):
74. ---------------------------------------------------
78. 4-access ("A". ") EMPNO "=" B "." MGR ")
82. Filter ("A". ") EMPNO "=" B "." MGR ")
86. 5-filter ("B". " MGR "is not NULL)
94. Statistical information
98. ----------------------------------------------------------
102. 0 Recursive calls
106. 0 db Block gets
110. One consistent gets
114. 0 physical Reads
118. 0 Redo Size
122. 2091 Bytes sent via sql*net to client
126. 416 Bytes received via sql*net from client
130. 2 sql*net roundtrips To/from Client
134. 1 Sorts (memory)
138. 0 Sorts (disk)
142. Rows processed
146. SQL>

Explanation of the above Execution Plan column fields:

Id: Executes the sequence, but not the order of execution. The execution is based on the operation in the judgment (using the most right-most first execution principle to see the hierarchical relationship, at the same level if an action does not have a child ID is the first to execute. Generally according to the indentation length to determine, indent the largest first execution, if there are 2 lines indented the same, then first execute the above. ）

For example: The execution sequence of the above execution plan is: 3--"2--" 5--"4--" 1

Operation: The contents of the current operation.

Name: Action Object

Rows: This is the previous cardinality (cardinality) of the 10g version, and Oracle estimates the number of returned result set rows for the current operation.

Bytes: Indicates the number of bytes returned after performing this step.

Cost (CPU): Represents the execution costs of executing to this step and is used to describe the cost of SQL execution.

Time:oracle estimates the time of the current operation.

2. Predicate Description:

predicate information (identified by Operation ID):

---------------------------------------------------

4-access ("A". ") EMPNO "=" B "." MGR ")

Filter ("A". ") EMPNO "=" B "." MGR ")

5-filter ("B". " MGR "is not NULL)

Access: The value that represents this predicate condition will affect the access brutishness (full table scan or index) of the data.

Filter: The value that represents the predicate condition does not affect the data access path, only the filtering effect.

The main note in predicates is access, which is to consider the conditions of the predicate, using the correct access path.

Four, dynamic analysis

If you have the following hints in the execution plan:

Note

------------

-dynamic sampling used for the statement

This hints at the technology currently used by the user CBO and requires the user to take these factors into account when analyzing the plan. When this prompt appears, the current table uses dynamic sampling. We thus infer that the table may not have been analyzed.

There are two scenarios:

(1) If the table has not been analyzed, the CBO can obtain the analysis data by dynamic sampling, or execute the plan correctly.

(2) If the table is analyzed, but the analysis information is too old, then the CBO will not use dynamic sampling, but instead use these old analysis data, which may lead to the wrong execution plan.

V. Table Access method

1.Full table Scan (FTS) Full table scanning

2.Index Lookup Index Scan

There is 5 methods of index lookup:

Index unique Scan--indexes only scanning

Method for Looking-a single key value via a unique index. Always returns a single value, you must supply at LEAST the leading column of the index to access data via the index.

Index range Scan--indexed local scans

Index range Scan is a method for accessing a range values of a particular column. At LEAST the leading column of the index must is supplied to access data via the index. Can is used for range operations (e.g. > < <> >= <= between).

Index full Scan--indexed global scan

Full index scans is only available in the CBO as otherwise we is unable to determine whether a full scan would be a good Idea or not. We choose an index full Scan if we have statistics this indicate that it was going to being more efficient than a full table Scan and a sort. For example we could do a full index scan if we do an unbounded scan of an index and want the data to is ordered in the Dex order.

Index fast full Scan--indexing quick global scan, often occurs without order by

Scans all the block in the index, Rows is not returned in sorted order, introduced in 7.3 and requires v733_plans_enabled =true and CBO, may be hinted using index_ffs hint, uses multiblock I/O, can be executed in parallel, can is used to access Second column of concatenated indexes. This is because we were selecting all of the index.

Index skip Scan---------------indexed skip

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column (s) during the search.

3.Rowid Physical ID Scan

This is the quickest access method available. Oracle retrieves the specified block and extracts the rows it is interested in.--rowid scan is the fastest way to access data

Six, table connection method

Please refer to another article: An explanation of Oracle table connection method

Http://www.fengfly.com/plus/view-210420-1.html

Seven, operator

1.sort --Sort, very resource-intensive

There is a number of different operations that promote sorts:

(1) Order BY clauses (2) Group by (3) Sort Merge join–-These three will produce a sort operation

2.filter --filtering, such as not in, min function, etc. easy to produce

Have a number of different meanings, used to indicate partition elimination, could also indicate an actual filter step where One row source is filtering, another, functions such as min could introduce filter steps into query plans.

3.view -views, mostly generated by inline views (possibly deep into the view EOG table)

When a view cannot is merged into the main, query you'll often see a projection view operation. This indicates, the ' view ' would be selected from directly as opposed to being broken-to-joins on the base table S. A number of constructs make A view non mergeable. Inline views is also non mergeable.

4.partition View --Partitioned view

Partition views is a legacy technology that were superceded by the partitioning option. This section of the article are provided as reference for such legacy systems.

Attached: Oracle Optimizer (Optimizer)

The Oracle Database Optimizer (Optimizer) is an optimization tool for SQL analysis and execution that specifies the execution plan for SQL, which is responsible for ensuring that SQL execution is the most efficient, such as when the optimizer determines how Oracle accesses the data and is a full table scan Table Scan), index range scanning (index range scan) or full index quick Scan (index fast scan:index_ffs); For table Association queries, it is responsible for determining how tables are correlated in a way, such as Hash_ John is still a nested LOOPS or merge JOIN. These factors directly determine the efficiency of SQL execution, so the optimizer is the core of SQL execution, it makes the execution plan is good or bad, directly determines the efficiency of SQL execution.

There are two types of Oracle optimizer:

RBO (rule-based Optimization): rule-based Optimizer

CBO (cost-based Optimization): Cost-based Optimizer

Starting with Oracle 10g, RBO has been deprecated, but we can still use it hint way.

In Oracle 10g, the CBO has an optional operating mode of 2 types:

(1) first_rows (n)

When Oracle executes SQL, priority is given to returning the first N records in the result set at the fastest speed, while others do not need to be returned at the same time.

(2) All_rows--default value in 10g

Oracle executes the SQL as quickly as it can, returning all the result sets, the difference between it and First_rows (n) is that the All_rows emphasizes that the SQL is executed at the fastest speed and all the result sets are fed back, and First_ ROWS (N) focuses on returning the execution time of the first N records.

Three ways to modify the CBO pattern:

(1) SQL statement:

Sessions level:

Sql> alter session set Optimizer_mode=all_rows;

(2) Modify the Pfile parameters:

Optimizer_mode=rule/choose/first_rows/all_rows

(3) statement level set with hint (/* + ... */)

Select/*+ First_rows (Ten) */name from table;

Select/*+ all_rows */name from table;

Analyze Oracle's execution plan (explain plan) and optimize the practice of SQL