Description of each field module in the execution plan

In the Execution Plan of SQL statements, there are many field items and many modules. Different fields represent different meanings and some fields and modules are displayed or not in different situations.
The description of each field in the execution plan and the description of each module.

For descriptions of each field module in the execution plan, see descriptions of each field module in the execution plan.
For more information about how to obtain an execution plan by using SQL statements, see use explain plan to obtain an SQL statement execution plan.
For how to use autotrace to obtain the execution plan, see: Enable autotrace.
For more information about how to use the display_cursor function, see use the display_cursor function of dbms_xplan.

1. Description of fields in the execution plan
1. Basic fields (always available)
Id indicates the identifier of each operation (ROW) in the execution plan. If the number is preceded by an asterisk, it means that the predicate information contained in this line will be provided later.
The operation corresponding to the operation. Also called row-Source Operations
Name of the object for the name Operation

2. query the optimizer evaluation information
Rows (e-rows) estimates the number of records returned by an operation
Bytes (e-bytes) Estimated number of record bytes returned by the Operation
Estimate the size of the temporary tablespace used by the tempspc operation
Cost (% CPU) estimates the overhead required for the operation. The percentage of CPU overhead is listed in brackets. Note that these values are calculated based on the execution plan. In other words, the overhead of a parent operation includes the overhead of a sub-operation.
Time: estimate the time required to perform the operation (HH: mm: SS)

3. partitioning (the following fields are only visible when accessing a partitioned table)
The first partition accessed by pstart. If you do not know which partition you want to resolve, set it to key, key (I), key (MC), key (OR), key (SQ)
The last partition accessed by pstop. If you do not know which partition you want to resolve, set it to key, key (I), key (MC), key (OR), key (SQ)

4. parallel and distributed processing (the following fields are visible only when parallel or distributed operations are used)
In distributed operations, inst refers to the name of the Database Link Used for the operation.
In parallel operations, TQ is used for the table queue for inter-dependent thread communication.
Relationship between in-out parallel or distributed operations
In parallel operations, PQ distrib distributes data sent to consumers.

5. runtime Statistics (the following fields are visible when statistics_level is set to all or the gather_plan_statistics prompt is used)
Number of times the specified operation is executed by starts
Number of real records returned by a-rows operation
The actual execution time of the-time operation (HH: mm: Ss. ff)

6. I/O Statistics (the following fields are visible when statistics_level is set to all or the gather_plan_statistics prompt is used)
Number of logical read operations performed during buffers execution
Number of physical read operations performed during reads execution
Number of physical write operations performed during writes execution

7. memory usage statistics
Estimated memory size required for optimal omem execution
1 MEM (one-pass) Execution of the estimated memory size required
0/1/M optimal/number of times the operation is executed in multipass Mode
Used-MEM memory used for the last operation
The size of the temporary space used for the last execution of used-TMP. This field must be 1024 times larger to be consistent with other memory measurement fields (for example, 32 K means 32 MB)
The maximum temporary space used by the Max-TMP operation. This field must be 1024 times larger to be consistent with other memory measurement fields (for example, 32 K means 32 MB)

Ii. Description and examples of each module in the execution plan
1. Fields and modules in the estimated execution plan

[SQL]

SQL> explain Plan for 2 select * from EMP E, DEPT D 3 where E. deptno = D. deptno 4 and E. ename = 'Smith '; explained. /*************************************** * ********** // * Author: robinson Cheng * // * blog: http://blog.csdn.net/robinson_0612 * // * MSN: [email protected] * // * QQ: 645746311 *//************************************ * ************/SQL> set linesize 180 SQL> set pagesize 0 SQL> select * from table (dbms_xplan.display (null, null, 'advanced '); -- use the dbms_xplan.display function to obtain the statement execution plan hash value: 351108634 -- SQL statement hash embedding/* execution plan part */| ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 0 | SELECT statement | 1 | 117 | 4 (0) | 00:00:01 | 1 | nested loops | 1 | 117 | 4 (0) | 00:00:01 | * 2 | table access full | EMP | 1 | 87 | 3 (0) | 00:00:01 | 3 | table access by index rowid | dept | 1 | 30 | 1 (0) | 00:00:01 | * 4 | index unique scan | pk_dept | 1 | 0 (0) | 00:00:01 | invalid query block name/object alias (identified by Operation ID ): -- this part shows the query block name and object alias ------------------------------------------------------------- 1-Sel $1 -- sel $ is the abbreviation of select, located in Block 1, corresponding to Del $, INS $, UPD $, etc. 2-Sel $1/[email protected] $1 [email protected] $1, corresponding to the operation ID of 2 in the execution plan, that is, query on Table E, and E is an alias, the following is similar to 3-Sel $1/[email protected] $1 4-Sel $1/[email protected] $1 outline data-Outline, this section graphically presents the execution plan in text format, convert to the prompt method -------------/* + begin_outline_data use_nl (@ "sel $1" "D" @ "sel $1") -- use use_nl prompt, that is, nested loop leading (@ "sel $1" "E" @ "sel $1" "D" @ "sel $1 ") -- specify the index_rs_asc (@ "sel $1" "D" @ "sel $1" ("Dept ". "deptno") -- specifies that the access method on D is full (@ "sel $1" "E" @ "sel $1 ") -- indicates that the access method for e is outline_leaf (@ "sel $1") all_rows optimizer_features_enable ('10. 2.0.3 ') ignore_optim_embedded_hints end_outline_data */predicate information (identified by Operation ID): -- predicate Information Section, in the execution plan, each row with an asterisk ID corresponds to the following row ----------------------------------------------------- 2-filter ("e ". "ename" = 'Smith ') 4-access ("e ". "deptno" = "D ". "deptno") column projection information (identified by Operation ID): -- The column returned by each step during execution, the following steps return different columns --------------------------------------------------------------- 1-(# Keys = 0) "E ". "empno" [number, 22], "E ". "ename" [varchar2, 10], "E ". "job" [varchar2, 9], "E ". "Mgr" [number, 22], "E ". "hiredate" [date, 7], "E ". "Sal" [number, 22], "E ". "Comm" [number, 22], "E ". "deptno" [number, 22], "D ". "deptno" [number, 22], "D ". "dname" [varchar2, 14], "D ". "Loc" [varchar2, 13] 2-"E ". "empno" [number, 22], "E ". "ename" [varchar2, 10], "E ". "job" [varchar2, 9], "E ". "Mgr" [number, 22], "E ". "hiredate" [date, 7], "E ". "Sal" [number, 22], "E ". "Comm" [number, 22], "E ". "deptno" [number, 22] 3-"D ". "deptno" [number, 22], "D ". "dname" [varchar2, 14], "D ". "Loc" [varchar2, 13] 4-"D ". rowid [rowid, 10], "D ". "deptno" [number, 22] note -- Comments and descriptions, the following description shows that this SQL statement uses the dynamic sampling function ------dynamic sampling used for this statement 58 rows selected.

2. Fields and modules in the actual execution plan

SQL> select/* + gather_plan_statistics */* -- note that the gather_plan_statistics prompt is added here and the statement is executed 2 from EMP e, DEPT D 3 where E. deptno = D. deptno 4 and E. ename = 'Smith '; 7369 Smith clerk 7902 17-dec-80 800 20 20 research Dallas SQL> select * from table (dbms_xplan.display_cursor (null, null, 'iostats last ')); -- use display_cursor to obtain the actual execution plan SQL _id fpx7zw59f405d, Child number 0 -- This section provides the SQL _id of the SQL statement, the sub-game number and the original SQL statement --------------------------------- select/* + gather_plan_statistics */* from EMP E, DEPT d Where E. deptno = D. deptno and E. ename = 'Smith 'plan hash value: 351108634 -- hash value of an SQL statement -- execution plan of an SQL statement, we can see that some of the fields shown below are different from those in the predicted execution plan. The fields are region | ID | operation | Name | starts | E-rows | A-time | buffers | reads | bytes | 1 | nested loops | 1 | 1 | 1 | 00:00:00. 01 | 10 | 1 | * 2 | table access full | EMP | 1 | 1 | 1 | 00:00:00. 01 | 8 | 0 | 3 | table access by index rowid | dept | 1 | 1 | 1 | 00:00:00. 01 | 2 | 1 | * 4 | index unique scan | pk_dept | 1 | 1 | 1 | 00:00:00. 01 | 1 | 1 | your predicate information (identified by Operation ID): --------------------------------------------------- 2-filter ("e ". "ename" = 'Smith ') 4-access ("e ". "deptno" = "D ". "deptno") Note ------dynamic sampling used for this statement 26 rows selected.

Iii. Summary
It can be seen from the above that different information of the execution plan can be obtained under different circumstances, while different information shows the different situations corresponding to the SQL statement. Therefore, it should be analyzed according to the specific situation.

Description of each field module in the execution plan