Oracle Execution Plan (Explain) Description __oracle

If you want to analyze a performance problem with a SQL, you usually have to look at the SQL execution plan to see if there is a problem with every step of the SQL execution. If a SQL is normally performed well, but one day suddenly performance is poor, if the system resources and blocking reasons, then the basic can be concluded that the implementation of the plan.

Understanding the execution plan is a prerequisite for SQL optimization. SQL optimization here refers to the positioning of the SQL performance problem, which can be fixed after the problem is fixed.

A Three ways to view the execution plan

1.1 Setting Autotrace

Serial number	Command	Explain
1	SET Autotrace off	This is the default value, that is, close autotrace
2	SET Autotrace on EXPLAIN	Show only execution plans
3	SET Autotrace on STATISTICS	Show only statistics for execution
4	SET Autotrace on	Contains 2, 32 items
5	SET Autotrace traceonly	Similar to on, but does not display the execution result of the statement

Sql> set Autotrace on

Sql> select * from Dave;

ID NAME

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

8 Anqing

1 Dave.

2 BL

1 bl

2 Dave.

3 DBA

4 sf-express

5 DMM

8 rows have been selected.

Execution plan

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

Plan Hash value:3458767806

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |      SELECT STATEMENT |     |    8 |     64 | 2 (0) | 00:00:01 |

|  1 | TABLE ACCESS full|     DAVE |    8 |     64 | 2 (0) | 00:00:01 |

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

Statistical information

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

0 Recursive calls

0 db Block gets

4 consistent gets

0 physical Reads

0 Redo Size

609 Bytes sent via sql*net to client

416 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

8 Rows processed

Sql>

1.2 Using SQL

Sql>explain plan for SQL statement;

Sql>select plan_table_output from table (Dbms_xplan. DISPLAY (' plan_table '));

Example:

sql> EXPLAIN Plan for SELECT * from DAVE;

has been explained.

sql> SELECT plan_table_output from table (Dbms_xplan. DISPLAY (' plan_table '));

Or:

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

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

Plan Hash value:3458767806

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |      SELECT STATEMENT |     |    8 |     64 | 2 (0) | 00:00:01 |

|  1 | TABLE ACCESS full|     DAVE |    8 |     64 | 2 (0) | 00:00:01 |

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

8 rows have been selected.

Execution plan

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

Plan Hash value:2137789089

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |         SELECT STATEMENT |  | 8168 |    16336 | 29 (0) | 00:00:01 |

|  1 | COLLECTION iterator Pickler fetch|  DISPLAY | 8168 |    16336 | 29 (0) | 00:00:01 |

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

Statistical information

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

Recursive calls

DB Block gets

Consistent gets

0 physical Reads

0 Redo Size

974 Bytes sent via sql*net to client

416 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

8 Rows processed

Sql>

1.3 Using TOAD,PL/SQL Developer tools

Two Cardinality (base)/rows

The cardinality value represents the number of records that the CBO expects to return from a row source (row source), possibly a table, an index, or a subquery. In Oracle 9i's execution plan, cardinality is abbreviated as a card. In 10g, the card value is replaced by rows.

This is a 9i execution plan and we can see the keyword card:

Execution plan

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

0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=402)

1 0 TABLE ACCESS (full) ' TBILLLOG8 ' (cost=2 card=1 bytes=402)

Oracle 10g execution plan with the keyword replaced by rows:

Execution plan

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

Plan Hash value:2137789089

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |         SELECT STATEMENT |  | 8168 |    16336 | 29 (0) | 00:00:01 |

|  1 | COLLECTION iterator Pickler fetch|  DISPLAY | 8168 |    16336 | 29 (0) | 00:00:01 |

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

The value of cardinality is critical for the CBO to make the right execution plan. If the CBO obtains a cardinality value that is not accurate (usually without analyzing or analyzing the data), there will be deviations in the calculation of the execution plan cost, leading to the CBO making the execution plan wrong.

When there are subqueries in a multiple-table association query or SQL, the cardinality value of each associated table or subquery has a very large impact on the main query, or even the CBO relies on the cardinality value of each associated table or subquery to calculate the final execution plan.

For a multiple-table query, the CBO uses the number of rows returned by each association table (cardinality) to determine what access means to make a table association (such as a nested loops join or a hash join).

Three ways to detail a multiple table connection HASH join MERGE join NESTED LOOP

Http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

For subqueries, its cardinality determines whether the subquery accesses the data using an index or a full table scan.

Three Execution plan for SQL

The execution plan for generating SQL is a very important step in Oracle's hard parsing of SQL, and it worked out a scenario that tells Oracle how to access data in the execution of this SQL: index or full table scan, hash join or nested loops Join and so on. For example, a SQL database access to data by using the index is the most cost-effective, the result of the CBO's implementation plan is a full table scan, then the performance of this SQL is necessarily relatively poor.

Hard parsing and soft parsing of Oracle SQL

Http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

Example:

sql> SET autotrace traceonly; --Show only execution plans, do not display result sets

Sql> SELECT * from scott.emp a,scott.emp b where a.empno=b.mgr;

13 rows have been selected.

Execution plan

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

Plan Hash value:992080948

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |        SELECT STATEMENT |    |   13 |     988 | 6 (17) | 00:00:01 |

|  1 |        MERGE JOIN |    |   13 |     988 | 6 (17) | 00:00:01 |

|   2 | TABLE ACCESS by INDEX rowid|    EMP |   14 |     532 | 2 (0) | 00:00:01 |

|    3 | INDEX Full SCAN |    Pk_emp |       14 |     | 1 (0) | 00:00:01 |

|* 4 |        SORT JOIN |    |   13 |     494 | 4 (25) | 00:00:01 |

|* 5 | TABLE ACCESS Full |    EMP |   13 |     494 | 3 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

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

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

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

Statistical information

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

0 Recursive calls

0 db Block gets

One consistent gets

0 physical Reads

0 Redo Size

2091 Bytes sent via sql*net to client

416 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

Rows processed

Sql>

The picture is the execution plan that the Toad tool views. In Toad, the order of execution is clearly shown. But if it's not so direct inside the sqlplus. But we can also judge: generally according to the indentation length to judge, indent the largest first execution, if there are 2 lines indented the same, then first execute the above.

3.1 Explanation of the fields in the execution plan:

ID: An ordinal number, but not the order of execution. The implementation of the successive according to the indentation in judgment.

Operation: The contents of the current operation.

Rows: The cardinality,oracle of the current operation estimates the return result set of the current operation.

Cost (CPU): A numeric value (cost) computed by Oracle to illustrate the costs of SQL execution.

Time:oracle estimates the time of the current operation.

3.2 Predicate Description:

predicate information (identified by Operation ID):