Detailed Oracle execution Analysis

We know that the execution plan generated by oracle for each statement is placed in the explain pool. For the first time, it is hard parsed to generate hash values. Compare the hash value when you execute this statement next time.

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 hash values. Compare the hash value when you execute this statement next time.

The following is an example.

Here is a supplement: There are several 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 like SET AUTOTRACE TRACEONLY. We will use this method in future examples.

SQL> select * from orders a where a. department_id in (select B. department_id from employees B where B. employee_id = 205 );

Execution Plan
----------------------------------------------------------
Plan hash value: 2782876085

Bytes ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 27 | 2 (0) | 00:00:01 |
| 1 | nested loops | 1 | 27 | 2 (0) | 00:00:01 |
| 2 | table access by index rowid | EMPLOYEES | 1 | 7 | 1 (0) | 00:00:01 |
| * 3 | index unique scan | EMP_EMP_ID_PK | 1 | 0 (0) | 00:00:01 |
| 4 | table access by index rowid | parts | 27 | 540 | 1 (0) | 00:00:01 |
| * 5 | index unique scan | DEPT_ID_PK | 1 | 0 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

3-access ("B". "EMPLOYEE_ID" = 205)
5-access ("A". "DEPARTMENT_ID" = "B". "DEPARTMENT_ID ")

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
749 bytes sent via SQL * Net to client
492 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Let's look at one row:

I. Table

1. Plan hash value:

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 ).

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.

SQL> select * from orders a where a. department_id = (select B. department_id from employees B where B. employee_id = 205 );

Execution Plan
----------------------------------------------------------
Plan hash value: 3449260133

Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 20 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | contents | 1 | 20 | 1 (0) | 00:00:01 |
| * 2 | index unique scan | DEPT_ID_PK | 1 | 0 (0) | 00:00:01 |
| 3 | table access by index rowid | EMPLOYEES | 1 | 7 | 1 (0) | 00:00:01 |
| * 4 | index unique scan | EMP_EMP_ID_PK | 1 | 0 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------