Explain the Oracle Execution Plan (2)

Now let's talk about the meaning of each parameter in the Oracle execution plan.

The following is an example.

Here is a supplement.: Trace types:

Serial number	Command	Explanation
1	SET AUTOTRACE OFF	This is the default value, that is, disable Autotrace.
2	SET AUTOTRACE ON EXPLAIN	Show only execution plans
3	SET AUTOTRACE ON STATISTICS	Only statistics of execution are displayed.
4	SET AUTOTRACE ON	Contains 2 or 3 items
5	SET AUTOTRACE TRACEONLY	Similar to ON, but does not display statement execution results

I likeSet autotrace traceonly,Our examples are based on this method in the future.

1. SQL>Select*FromAdministrative departmentsWhereA. department_idIn(SelectB. department_idFromEmployees BWhereB. Fig = 205 );
4. Execution Plan
5. ----------------------------------------------------------
6. Plan hash value: 2782876085
8. Bytes ----------------------------------------------------------------------------------------------
9. | Id | Operation |Name|Rows| Bytes | Cost (% CPU) |Time|
10. Bytes ----------------------------------------------------------------------------------------------
11. | 0 |SELECTSTATEMENT | 1 | 27 | 2 (0) | 00:00:01 |
12. | 1 | nested loops | 1 | 27 | 2 (0) | 00:00:01 |
13. | 2 |TABLEACCESSBY INDEXROWID | EMPLOYEES | 1 | 7 | 1 (0) | 00:00:01 |
14. | * 3 |INDEX UNIQUESCAN | EMP_EMP_ID_PK | 1 | 0 (0) | 00:00:01 |
15. | 4 |TABLEACCESSBY INDEXROWID | ments | 27 | 540 | 1 (0) | 00:00:01 |
16. | * 5 |INDEX UNIQUESCAN | DEPT_ID_PK | 1 | 0 (0) | 00:00:01 |
17. Bytes ----------------------------------------------------------------------------------------------
19. Predicate Information (identifiedByOperation id ):
20. ---------------------------------------------------
22. 3-access ("B"."EMPLOYEE_ID"= 205)
23. 5-access (""."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
26. Statistics
27. ----------------------------------------------------------
28. 1 recursive cballs
29. 0 db block gets
30. 4 consistent gets
31. 0 physical reads
32. 0 redoSize
33. 749 bytes sent via SQL * NetToClient
34. 492 bytes encoded ed via SQL * NetFromClient
35. 2 SQL * Net roundtripsTo/FromClient
36. 0 sorts (memory)
37. 0 sorts (disk)
38. 1RowsProcessed

Let's look at one row:

I. Table

1. Plan hash value:

1. Plan hash value: 2782876085

This row is the hash value of this statement. We know that the execution plan generated by oracle for each statement is placed in the share pool. For the first time, it is hard parsed to generate a hash value. When you execute this statement again, compare the hash value. If the statement is the same, do not perform hard parsing.

2. Operation)

Here there are more things, that is, to break down the SQL, let me take a look at the SQL, the first step of this SQL section is employee_id = 25, here we have built a primary key above the employee_id, by default, a unique index is created for a primary key. "=" Is used here for restrictions, so the unique scan mode is adopted. For other methods, see explain the Oracle Execution Plan (1 ).

Another knowledge point is to know the table link operation. For more information, see my other article ()

3. Name (operated object)

For example, in the second row of operation (table access by index rowid) in the above example, the TABLE object here is EMPLOYEES.

4. Row, also called Cardinality (explain the plan window in plsqldev)

Here is the number of rows in the data query. For example, if there are 4th rows in the previous example, the rows in the orders table will be scanned for 27 rows, and then the rows in the subquery (select B. department_id from employees B where B. the value of employee_id = 205) is compared. If you use = (Note: most of the time it cannot be replaced by =, here is a special case), it will be different.

1. SQL>Select*FromAdministrative departmentsWhereA. department_id = (SelectB. department_idFromEmployees BWhereB. Fig = 205 );
4. Execution Plan
5. ----------------------------------------------------------
6. Plan hash value: 3449260133
8. Bytes -----------------------------------------------------------------------------------------------
9. | Id | Operation |Name|Rows| Bytes | Cost (% CPU) |Time|
10. Bytes -----------------------------------------------------------------------------------------------
11. | 0 |SELECTSTATEMENT | 1 | 20 | 2 (0) | 00:00:01 |
12. | 1 |TABLEACCESSBY INDEXROWID | ments | 1 | 20 | 1 (0) | 00:00:01 |
13. | * 2 |INDEX UNIQUESCAN | DEPT_ID_PK | 1 | 0 (0) | 00:00:01 |
14. | 3 |TABLEACCESSBY INDEXROWID | EMPLOYEES | 1 | 7 | 1 (0) | 00:00:01 |
15. | * 4 |INDEX UNIQUESCAN | EMP_EMP_ID_PK | 1 | 0 (0) | 00:00:01 |
16. Bytes -----------------------------------------------------------------------------------------------
18. Predicate Information (identifiedByOperation id ):
19. ---------------------------------------------------
21. 2-access (""."DEPARTMENT_ID"= (SELECT/* + */"B"."DEPARTMENT_ID" FROM "EMPLOYEES"
22. "B" WHERE "B"."EMPLOYEE_ID"= 205 ))
23. 4-access ("B"."EMPLOYEE_ID"= 205)
26. Statistics
27. ----------------------------------------------------------
28. 0 recursive cballs
29. 0 db block gets
30. 4 consistent gets
31. 0 physical reads
32. 0 redoSize
33. 749 bytes sent via SQL * NetToClient
34. 492 bytes encoded ed via SQL * NetFromClient
35. 2 SQL * Net roundtripsTo/FromClient
36. 0 sorts (memory)
37. 0 sorts (disk)
38. 1RowsProcessed

5. Byte

Number of bytes of scanned data

6. Cost

This is the last time we talked about it. Here is a simple introduction.

Cost has no unit and is a relative value. It is used by oracle to evaluate the cost of cbo and select the execution plan when SQL is parsed and executed in cbo mode.

Formula:Cost = (Single block I/O cost + Multiblock I/O cost + CPU cost)/sreadtim

There is no clear meaning, but the comparison is very useful.

7. Time

Execution time of each segment

1 2 3 Next Page