How to generate and display an execution plan in Oracle

The method for generating and displaying execution plans in Oracle generates an execution plan. After a legal statement is executed, at least one execution plan is generated in the memory, which can be queried from the view v $ SQL _plan. Each execution plan has a cursor. The CHILD_NUMBER (that is, the CHILD_NUMBER in v $ SQL _plan) of the First cursor produced by a statement is 0. Different execution plans may be generated for the same SQL statement due to other factors such as the Environment or version, that is to say, an SQL statement may have multiple child_numbers. In addition to the executed SQL statements, www.2cto.com generates execution plans in the memory. You can also use the explain plan to enable the optimizer to parse the SQL statements and generate a query plan. After the explain plan command is executed, oracle inserts the explain execution plan into sys. plan_table $ (before 10 Gb, the table name is plan_table; after 10 Gb, point to sys through the public synonym plan_table. plan_table $. The execution plans generated by the explain plan are not reused during statement execution, but are cached in a format similar to the explain plan for <SQL>. Display execution plan display the Execution plan (recommended) using the DBMS_XPLAN package DBMS_XPLAN contains five functions used to output the formatted execution plan, as shown below: display the execution plan interpreted by the explain plan command display_cursor: display the execution plan display_awr: Execution Plan display_sqlset in the historical awr data: Execution Plan of the SQL optimization concentrated statement display_ SQL _plan_baseline: usage syntax of the SQL Execution Plan baseline www.2cto.com display: DBMS_XPLAN.DISPLAY (table_name IN VARCHAR2 DEFAULT 'Plan _ table', statement_id IN VARCHAR2 default null, format IN VARCHAR2 DEFAULT 'typical', filter_preds IN VARCHAR2 default null); parameter: parameter Descriptiontable_name: name of the table storing the query plan. The default value is PLAN_TABLEstatement_id. If it is null, the last interpreted statement format output is obtained, you can select or customize the following predefined formats: BASIC: the BASIC format with the least content. TYPICAL: the TYPICAL format is usually enough. The default format is SERIAL: SERIAL execution format. This format is basically the same as the output content in the TYPICAL format, but for parallel queries, it does not output the relevant parallel content ALL: full format: the output content is relatively complete. filter_preds: this parameter uses the predicate condition to filter the tables that store the execution plan, for example, 'Plan _ id = 10 or cost> 100 (show the operation with an estimated cost greater than 100). This value is empty by default. Example: [html] Use explain plan to execute an SQL statement: explain plan for select * FROM emp e, dept d WHERE e. deptno = d. deptno AND e. ename = 'benoit'; Display the plan using the DBMS_XPLAN.DISPLAY table function set linesize 130 set pagesize 0 SELECT * FROM table (DBMS_XPLAN.DISPLAY); the output result is as follows: Plan hash value: 3693697075 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 57 | 6 (34) | 00:00:01 | * 1 | hash join | 1 | 57 | 6 (34) | 00:00:01 | * 2 | table access full | EMP | 1 | 37 | 3 (34) | 00:00:01 | 3 | table access full | DEPT | 4 | 80 | 3 (34) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-access ("E ". "DEPTNO" = "D ". "DEPTNO") 2-filter ("E ". "ENAME" = 'benoit') 15 rows selected. display_cursor: display the execution plan of one or more cursors in the memory. syntax: DBMS_XPLAN.DISPLAY_CURSOR (SQL _id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT 'typical'); parameter: parameter Descriptionsql_id the IDchild_number subcursor serial number of the SQL statement to display the execution plan. The output format is the same as the display output format. Example: [html] <span style = "color: #009900; "> display_cursor is the execution plan for the next statement executed by this session by default: </span> SELECT ename FROM emp e, dept d WHERE e. deptno = d. deptno AND e. empno = 7369; ENAME ---------- smith set pagesize 0 SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR); <span style = "color: #009900;"> the output result is as follows: </span> Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0 -------------------------------------------------------------- SELECT ename FROM emp e, dept d WHERE e. deptno = d. deptno AND e. empno = 7369 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ------------------------------------------------------------------------- | 0 | select statement | * 1 | hash join | 1 | 16 | 6 (34) | 00:00:01 | * 2 | table access full | EMP | 1 | 13 | 3 (34) | 00:00:01 | 3 | table access full | DEPT | 4 | 12 | 3 (34) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-access ("E ". "DEPTNO" = "D ". "DEPTNO") 2-filter ("E ". & quot; EMPNO & quot; = 7369) 21 rows selected. www.2cto.com <span style = "color: #009900;"> You can also use the following method to obtain SQL _ID and CHILD_NUMBER </span> SELECT/* TOTO */ename, dname FROM dept d join emp e USING (deptno); <span style = "color: #009900;"> use the preceding statement to obtain SQL _id and child_number: </span> SELECT SQL _id, child_number FROM v $ SQL WHERE SQL _text LIKE '% TOTO %'; SQL _ID CHILD_NUMBER ---------- explain gwp663cqh5qbf 0 SELECT * FROM table (partition ('gwp663cqh5qbf ', 0); Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0 explain SELECT/* TOTO */ename, dname FROM dept d JOIN emp e USING (deptno ); role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement ||| 7 (100) | 1 | sort group by | 4 | 64 | 7 (43) | 00:00:01 | * 2 | hash join | 14 | 224 | 6 (34) | 00:00:01 | 3 | table access full | DEPT | 4 | 44 | 3 (34) | 00:00:01 | 4 | table access full | EMP | 14 | 70 | 3 (34) | 00:00:01 | identified by operation id: ----------------------------------------------------------------- 2-access ("E ". "DEPTNO" = "D ". "DEPTNO") <span style = "color: #009900;"> the preceding two steps can also be obtained in one click using the following method </span> SELECT t. * FROM v $ SQL s, table (DBMS_XPLAN.DISPLAY_CURSOR (s. SQL _id, s. child_number) t WHERE SQL _text LIKE '% TOTO % ';