Use explain plan to obtain the SQL statement execution plan

The performance of SQL query statements affects the performance of the entire database to a certain extent. In many cases, poor database performance is almost caused by poor SQL statements. SQL statement execution
The Plan determines how the SQL statement extracts data from the database and returns the data to the client. This document describes how to obtain the SQL statement execution plan through the explain Plan.
The execution plan of the SQL statement.

I. How to obtain the SQL statement execution plan
1. Use the explain Plan to load the execution plan to the plan_table table, and then query the table to obtain the estimated execution plan.
2. query the dynamic performance view v $ SQL _plan, V $ SQL _plan_statistics, V $ SQL _workarea, and so on to obtain the real execution plan cached in the library cache.
3. query the automatic workload database (automatic workload repository) or statspack, that is, obtain the execution plan from the database.
4. enable the execution plan tracking function, that is, the autotrace function.
5. Use the methods provided by PL/SQL developer to obtain the execution plan
6. Use the toad tool to obtain the execution plan

The following describes how to use explain plan to obtain an execution plan.

Ii. essence, premise and operation method of the explain Plan
1. essence of work
Load the SQL statement prediction execution plan to the plan_table table. The DML operation is performed on the plan_table table, so no implicit commit is performed.
You can load execution plans for select, insert, update, merge, delete, create table, create index, and alter index to plan_table.

2. Prerequisites
You need to create a plan_table first. The creation method is :@? /Rdbms/admin/utlxplan
You have the execution permission for the current SQL statement and the corresponding operation permission for the dependent object.

3. Usage:
Explain plan for select * from Scott. EMP where ename = 'Scott '; -- no tag bit is set
Explain plan set statement_id = 'test' for select * from Scott. EMP where ename = 'Scott '-- set the flag bit to test

Iii. Practical drills
1. Environment

Scott @ orcl> select * from V $ version; </P> <p> banner <br/> ---------------------------------------------------------------- <br/> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod <br/>

2. Create a test table to demonstrate how to obtain the execution plan

Scott @ orcl> Create Table T as select * From all_objects where rownum <= 1000; </P> <p> table created. </P> <p> -- load the execution plan of the created table (DDL Execution Plan) <br/> Scott @ orcl> explain Plan set statement_id = 't1' for create table T1 as select * from t; </P> <p> explain. </P> <p> -- use the following statement to obtain the execution plan from plan_table <br/> Col operation format A25 <br/> Col options format A25 <br/> Col object_name format a25 <br/> select lpad ('', 2 * (level-1) | operation <br/>, options <br/>, object_name <br/>, position POS <br/>, bytes <br/>, cost <br/> from plan_table <br/> start with ID = 0 <br/> and statement_id = upper ('& input_statement_id ') <br/> connect by prior id = parent_id; </P> <p> enter value for input_statement_id: t1 <br/> old 9: and statement_id = upper ('& input_statement_id') <br/> New 9: And statement_id = upper ('t1 ') </P> <p> operation options object_name POS bytes cost <br/> ------------------------- create table statement 8 79000 8 <br/> load as select t1 1 <br/> table access full T 1 79000 5 </P> <p> -- create a test table T1 and collect statistics. <br/> Scott @ orcl> Create Table T1 nologging as select * from T; </P> <p> Scott @ orcl> exec dbms_stats.gather_table_stats ('Scott ', 't1 '); </P> <p> -- use the explain Plan to load the execution plan for index creation <br/> Scott @ orcl> explain Plan set statement_id = 'idx' for create index I _t1 on T1 (object_id ); </P> <p> explained. </P> <p> Scott @ orcl> @ get_plan <br/> enter value for input_statement_id: idx <br/> old 9: And statement_id = upper ('& input_statement_id ') <br/> New 9: And statement_id = upper ('idx ') </P> <p> operation options object_name POS bytes cost <br/> --------------------------- creating indexes ---------- ------------ <br/> Create index Statement 6 4000 6 <br/> index build non unique I _t1 1 <br/> sort create Index 1 4000 <br/> table access full T1 1 4000 5 </P> <p> Scott @ orcl> Create index I _t1 on T1 (object_id ); </P> <p> Scott @ orcl> Delete from plan_table;

3. Use the top-down reading method to obtain the execution plan

-- Use the explain Plan to load the execution plan for re-indexing <br/> Scott @ orcl> explain Plan set statement_id = 'A _ idx' For Alter index I _t1 rebuild; </P> <p> explained. </P> <p> -- execute the following statement to obtain the execution plan of a_idx. The result is read from top to bottom and read from the innermost side to the outermost side. <Br/> select lpad ('', 2 * (level-1) | level | '. '| nvl (Position, 0) | ''| <br/> operation |'' | options | ''| object_name |'' | <br/> object_type |'' | <br/> decode (ID, 0, statement_id | 'cost = '| position) | cost | ''| <br/> object_node" query plan "<br/> from plan_table <br/> start with ID = 0 <br/> and statement_id = upper ('& input_statement_id ') <br/> connect by prior id = parent_id <br/> and statement_id = upper ('& input_statement_id'); </P> <p> enter value for input_statement_id: a_idx <br/> old 8: And statement_id = upper ('& input_statement_id') <br/> New 8: And statement_id = upper ('A _ idx ') <br/> enter value for input_statement_id: a_idx <br/> old 10: And statement_id = upper ('& input_statement_id') <br/> New 10: and statement_id = upper ('A _ idx ') </P> <p> query plan <br/> statement <br/> 1.2 alter index statement a_idx cost = 22 <br/> 2.1 index build non unique I _t1 <br/> 3.1 sort create index <br/> 4.1 index fast full scan I _t1 index 2 </P> <p> -- use the explain Plan to load the execution plan of the query statement <br/> Scott @ orcl> explain Plan set statement_id = 'query' for <br/> 2 select ename, dname <br/> 3 from EMP join dept <br/> 4 on EMP. deptno = Dept. deptno <br/> 5 where Dept. deptno = 30; </P> <p> explained. </P> <p> Scott @ orcl> @ get_plan2 </P> <p> query plan <br/> explain <br/> 1.4 SELECT statement query cost = 44 <br /> 2.1 nested loops 4 <br/> 3.1 table access by index rowid dept Table 1 <br/> 4.1 index unique scan pk_dept index (unique) 0 <br/> 3.2 Table access full EMP Table 3 <br/>

The reading method in the above example:
Perform a unique index scan of 4.1
Returns the result set of 4.1 to 3.1.
Perform 3.2 full table scan.
Return the result set of steps 3.1 and 3.2 To Step 2.1.
Execute a nested loop of 2.1
Returns the final result set.
Note the nested loop query method.
Oracle reads the first row from the first row source and compares it with all record rows in the second row source. All matched records are placed in the result set. Then Oracle reads the first row
The next row in each row source. And so on until all rows in the source of the first row are processed.
4. view the execution plan using the build tree method

Scott @ orcl> Delete from plan_table; </P> <p> -- use the explain Plan to load the SQL query execution plan <br/> Scott @ orcl> explain Plan set statement_id = 'query2' for <br/> 2 select ename, dname <br/> 3 from EMP join dept <br/> 4 on EMP. deptno = Dept. deptno <br/> 5 where EMP. empno = 7788; </P> <p> explained. </P> <p> -- use the following SQL query to generate the build tree <br/> Col operation format A30 <br/> Col options format A20 <br/> col "Object name "format A25 <br/> Col order format A10 <br/> Col opt format A15 <br/> select lpad ('', 2 * (level-1) | operation "operation", <br/> options "options", <br/> decode (to_char (ID ), <br/> '0', <br/> 'cost = '| nvl (to_char (position), 'n'/A'), <br/> object_name) "Object Name", <br/> ID | '-' | nvl (parent_id, 0) | '-' | nvl (Position, 0) "order ", <br/> substr (optimizer, 1, 6) "Opt" <br/> from plan_table <br/> start with ID = 0 <br/> and statement_id = upper ('& input_statement_id ') <br/> connect by prior id = parent_id <br/> and statement_id = upper ('& input_statement_id '); </P> <p> operation options object name order opt <br/> export ----------------------------------------- ------------------- <br/> SELECT statement cost = 2 0-0-2 all_ro <br/> nested loops 1-0-1 <br/> table access by index rowid EMP 2-1-1 analyz <br/> index unique scan pk_emp 3-2-1 analyz <br /> table access by index rowid dept 4-1-2 analyz <br/> index unique scan pk_dept 5-4-1 analyz <br/>

Order and opt columns in the query results
Order
The Order column refers to the ID, parent ID, and the position of this step in the execution plan.
The ID column identifies this step, but does not explain the execution sequence
The parent ID indicates the parent step in this step.
Location Information describes the execution sequence of sub-operations with the same parent ID.
OPT
Describes the mode used by the optimizer.
Analysis
First, it starts from step 3, and step 3 returns the result set to the parent Step 2 by uniquely scanning pk_emp through the index.
Step 2 access the table EMP Based on the rowid obtained in step 3 and return the result set to the parent step 1.
For each row of data retrieved in step 2, step 1 passes deptno to Step 5
Step 5 perform a unique index Scan Based on the obtained deptno and return the result set to Step 4
Step 4 access the table dept Based on the rowid obtained in step 5 and return the result set to the parent step 1.
For the remaining rows in step 3, return all result sets to Step 1 in sequence as described above
Step 1 return the final result set to step 0, and SQL completes the query

Build the execution plan tree based on the results returned by the query
Starting from the column with ID 1 as the root node
Search for all child IDs whose parent ID is 1. For example, if they are 2 and 4, add them to the tree.
Search for all child IDs whose parent IDs are 2 and 4 respectively and include them in the tree.
Until all IDs have no parent ID

---------------
Nested loop (1)
---------------
--
--
--
-------------------
EMP (2) dept (4)
-------------------
--
--
-------------------
Pk_emp (3) pk_dept (5)
-------------------
5. Use the SQL statement execution plan provided by Oracle
You can use the SQL statements provided by Oracle to obtain the execution plan of the last SQL statement of the current session.

Utlxpls. SQL --> used to view the serial execution plan
Utlxplp. SQL --> used to view parallel execution plans

Scott @ orcl> @? /Rdbms/admin/utlxpls. SQL -- Obtain the execution plan of the last SQL statement in the current session plan_table </P> <p> plan_table_output <br/> explain <br/> plan hash value: 2385808155 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> ------- --------------------------------------------------------------------------------- <Br/> | 0 | SELECT statement | 1 | 28 | 2 (0) | 00:00:01 | <br/> | 1 | nested loops | 1 | 28 | 2 (0) | 00:00:01 | <br/> | * 2 | table access by index rowid | EMP | 1 | 14 | 1 (0) | 00:00:01 | <br/> | * 3 | index unique scan | pk_emp | 1 | 0 (0) | 00:00:01 | <br/> | 4 | table access by index rowid | dept | 4 | 56 | 1 (0) | 00: 00: 01 | <br/> | * 5 | index unique scan | pk_dept | 1 | 0 (0) | 00:00:01 | <br/> identified </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 2-filter ("EMP ". "deptno" is not null) <br/> 3-access ("EMP ". "empno" = 7788) <br/> 5-access ("EMP ". "deptno" = "Dept ". "deptno") </P> <p> 19 rows selected. </P> <p> -- load the execution plan of parallel SQL statements <br/> Scott @ orcl> explain Plan for select/* + parallel (T4, 2) */* From T4; </P> <p> explained. </P> <p> Scott @ orcl> @? /Rdbms/admin/utlxplp. SQL </P> <p> plan_table_output <br/> explain <br/> plan hash value: 128826497 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | TQ | in-out | PQ distrib | <br/> latency <br/> | 0 | SELECT statement | 400k | 7817k | 183 (4) | 00:00:03 | <br/> | 1 | Px Coordinator | <br/> | 2 | Px send QC (random) |: tq10000 | 400k | 7817k | 183 (4) | 00:00:03 | Q1, 00 | p-> S | QC (RAND) | <br/> | 3 | Px block iterator | 400k | 7817k | 183 (4) | 00:00:03 | Q1, 00 | pcwc | <br/> | 4 | table access full | T4 | 400k | 7817k | 183 (4) | 00:00:03 | Q1, 00 | PCWP | <br/> average <br/>

Iv. Summary:
1. The explain Plan does not execute the current SQL statement, but obtains the best execution plan based on the statistical information recorded in the data dictionary and loads it to the plan_table table.
2. The explain Plan may be different from the actual execution plan due to changes in the execution environment due to statistical information.
3. for SQL statements that run for a long period, you do not need to wait until the result is output to obtain the execution plan of the SQL statement in advance. debugging in the production environment reduces the database load.
4. Note that the Set statement_id identifier is case sensitive.