Use Dbms_xplan processing execution plan in Oracle _oracle

Dbms_xplan is a package that Oracle provides to view SQL plans, including execution plans and interpretation plans, and I used the set autotrace command when I looked at the SQL execution plan, but now it seems that Dbms_ The Xplan package gives a simpler way to get and display a plan.

These 5 functions correspond to different display plans, and the Dbms_xplan package can not only get an interpretation plan, it can also be used to output the Awr,sql debug set, the cached SQL cursor, and the statement plan in the SQL baseline to implement functions such as the above, usually using 5 methods:

1.DISPLAY
2.display_awr
3.display_cursor
4.display_plan
5.display_sql_plan_baseline
6.display_sqlset

The following focuses on the application of the Dbms_xplan package to the interpretation plan and execution plan.

Look at a frequently used example of an explanation plan for viewing a statement:

Copy Code code as follows:

Sql> explain plan for SELECT * from Scott.emp;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output
--------------------------------------------------------------------------------
Plan Hash value:3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |    |   14 |     532 | 3 (0) | 00:00:01 |
|  1 | TABLE ACCESS full|    EMP |   14 |     532 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

--corresponding Autotrace implementation
Sql> Set Autotrace traceonly explain

The Dbms_xplan.display method is used in the previous example to display an explanation plan saved in plan_table, and if you want to display an execution plan, you need to use the Dmbs_xplan. Display_cursor method, Dmbs_xplan. Display_cursor Call Signature:

Copy Code code as follows:

Dbms_xplan. Display_cursor (
sql_id in VARCHAR2 DEFAULT NULL,
Child_number in number DEFAULT NULL,
Format in VARCHAR2 DEFAULT ' typical ');

SQL_ID represents the Id,child_number of the SQL statement stored in the cursor cache used to indicate that the child id,format parameters of the cached SQL statement plan are used to control the type of information contained in the output, and the official file parameters are as follows:

1.BASIC: Displays minimal information, including only the type of action, ID name, and options.
2.TYPICAL: Default, display related information, and some additional display options, such as partitioning and concurrent use.
3.SERIAL: Similar to the typical type, the difference is that it does not include concurrent information, even plans that are executed in parallel.
4.ALL: Displays the most information, including all the typical and additional information, such as aliases and remote calls.

In addition to the basic four types of output formats above, the format also has additional options for customizing the output behavior, where multiple keywords can be declared by commas and spaces, while the "+" and "-" symbols can be used to include or exclude corresponding display elements, which are also documented in official files:

1.rows– shows the line number of the record that was estimated by the optimizer
2.bytes– Displays the number of bytes estimated by the optimizer
3.cost– Displays the cost information that the optimizer calculates
4.partition– Display partition information
5.parallel– Display Parallel Execution information
6.predicate– Show predicates
7.projection– Display column Projection section (those columns in each row are passed to the size of their parent column already)
8.alias– Display query block name already object alias
9.remote– Display distributed Query information
10.note– Display Comments
11.iostats– Display IO statistics for cursor execution
12.memstats– displays memory management statistics for memory-intensive operations such as hash joins, sorting, or some types of bitmap operations
13.allstats– and ' iostats memstats ' equivalence
14.last– displays the last execution plan statistics, which are displayed as all types by default, and can be cumulative.

The above parameters also apply to the display method of interpreting the plan.

Example section:

First, use the Display_cursor method to view the execution plan of the most recent statement

Copy Code code as follows:

Sql> Select/*+ gather_plan_statistics */COUNT (*) from scott.emp;

COUNT (*)
----------
14

Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last '));

Plan_table_output
-------------------------------------------------------------------------------------------------------
sql_id F9QYZ8S3C2C02, child number 0
-------------------------------------
Select/*+ Gather_plan_statistics */COUNT (*) from scott.emp

Plan Hash value:2937609675

-------------------------------------------------------------------------------------


| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers |


-------------------------------------------------------------------------------------


| 0 |        SELECT STATEMENT |      |        1 |      |       1 |00:00:00.01 | 1 |


|  1 |        SORT AGGREGATE |      |      1 |      1 |       1 |00:00:00.01 | 1 |


|   2 | INDEX Full scan|      Pk_emp |     1 |     14 |       14 |00:00:00.01 | 1 |


-------------------------------------------------------------------------------------

Rows selected.

When using Dbms_xplan.display_cursor (Null,null, ' allstats last '), set the sql_id and child_number to null, representing the execution plan for getting the previous execution statement Note that the above example must specify the Gather_plan_statistics prompt or manually set the database Statistics_level parameter to all to enable it to crawl the execution statistics of the row data source, including the number of rows, the number of reads, the number of physical reads, The number of physical writes and the elapsed time that is spent on a single line of data, and if you do not specify the prompt, there will be no a-rows,a-time,buffers of these three columns of information.

Ii. getting the execution plan for a specified statement

Copy Code code as follows:

Sql> Select/*+ gather_plan_statistics */COUNT (*) from Hr.employees where department_id in (90, 100);

COUNT (*)
----------
9

--Query the sql_id and child_number of SQL statements through the V$sql view
Sql> Select Sql_id,child_number,sql_text from V$sql
2 where Sql_text like '%select/*+ gather_plan_statistics */count (*)%;

sql_id Child_number Sql_text
------------- ------------ -------------------------------------------------------------------------------------- --------------
5qxmkvh40yw0p 0 Select/*+ gather_plan_statistics */COUNT (*) from Hr.employees where department_id in (90, 100 )
bqjrnskvpv51n 0 Select Sql_id,child_number,sql_text from v$sql where Sql_text like '%select/*+ Tisti
CS/Count (*)% '

--Get the corresponding execution plan
Sql> select * FROM table (dbms_xplan.display_cursor (' 5qxmkvh40yw0p ', 0, ' allstats last '));

Plan_table_output
----------------------------------------------------------------------------------------------------
sql_id 5qxmkvh40yw0p, child number 0
-------------------------------------
Select/*+ Gather_plan_statistics */COUNT (*) from Hr.employees where
department_id in (90, 100)

Plan Hash value:4167091351

--------------------------------------------------------------------------------------------------


| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers |


--------------------------------------------------------------------------------------------------


| 0 |                   SELECT STATEMENT |      |        1 |      |       1 |00:00:00.01 | 2 |


|  1 |                   SORT AGGREGATE |      |      1 |      1 |       1 |00:00:00.01 | 2 |


|   2 |                   INLIST Iterator |      |        1 |      |       9 |00:00:00.01 | 2 |


|* 3 | INDEX RANGE scan|      Emp_department_ix |      2 |      9 |       9 |00:00:00.01 | 2 |


--------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

3-access (("department_id" =90 OR "department_id" =100))

Rows selected.

Custom execution plan output information through format parameters

Copy Code code as follows:

--use all to display all information about the explanation plan


Sql> explain plan for


2 SELECT * from EMP E, Dept D


3 Where E.deptno = D.deptno


4 and E.ename = ' JONES ';

explained.

Sql> select * FROM table (Dbms_xplan.display (format=> ' all '));

Plan_table_output
----------------------------------------------------------------------------------------------------
Plan Hash value:3625962092

----------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


----------------------------------------------------------------------------------------


| 0 |         SELECT STATEMENT |     |    1 |     58 | 4 (0) | 00:00:01 |


|  1 |         NESTED LOOPS |       |       |            |          | |


|   2 |         NESTED LOOPS |     |    1 |     58 | 4 (0) | 00:00:01 |


|* 3 | TABLE ACCESS Full |     EMP |    1 |     38 | 3 (0) | 00:00:01 |


|* 4 | INDEX UNIQUE SCAN |     pk_dept |       1 |     | 0 (0) | 00:00:01 |


|   5 | TABLE ACCESS by INDEX rowid|     DEPT |    1 |     20 | 1 (0) | 00:00:01 |

Query block Name/object Alias (identified by Operation ID):
-------------------------------------------------------------

1-sel$1
3-sel$1/e@sel$1
4-sel$1/d@sel$1
5-sel$1/d@sel$1

predicate information (identified by Operation ID):

3-filter ("E".) ename "= ' JONES '")
4-access ("E".) DEPTNO "=" D "." DEPTNO ")

Column projection information (identified by Operation ID):
-----------------------------------------------------------

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-(#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". ROWID[ROWID,10], "D". " DEPTNO "[number,22]


3-"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]


4-"D". ROWID[ROWID,10], "D". " DEPTNO "[number,22]


5-"D". " Dname "[varchar2,14]," D "." LOC "[varchar2,13]

Selected rows.

--Removes the number of bytes and cost statistics on the execution plan
Sql> select Empno, ename from EMP E, Dept D
2 Where E.deptno = D.deptno
3 and E.ename = ' JONES ';

EMPNO ename
---------- ----------
7566 JONES

Sql> select * FROM table (dbms_xplan.display_cursor (null,null,format=> ' allstats last-cost-bytes '));

Plan_table_output
----------------------------------------------------------------------------------------------------
sql_id 3mypf7d6npa97, child number 1
-------------------------------------
Select Empno, ename from EMP E, dept d where E.deptno = D.deptno and
E.ename = ' JONES '

Plan Hash value:3956160932

------------------------------------------------------------------------------------
| id  | operation         | Name | Starts | E-rows | A-rows |   a-time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT statement  |      |      1 |         |      1 |00:00:00.01 |       8 |
|*  1 |  TABLE ACCESS full| emp  |      1 |      1 |      1 | 00:00:00.01 |       8 |
------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter (("E".) ename "= ' JONES ' and" E "." DEPTNO ' is not NULL)

Rows selected.

--Another option to peek at the value of the binding variable, very convenient!!
Sql> variable v_empno number
sql> exec:v_empno: = 7566;

Pl/sql procedure successfully completed.

Sql> SELECT * from emp where empno =: v_empno;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-apr-81 2975 20

Sql> select * FROM table (dbms_xplan.display_cursor (null,null,format=> ' +peeked_binds '));

Plan_table_output
----------------------------------------------------------------------------------------------------
sql_id 9Q17W9UMT58M7, child number 0
-------------------------------------
SELECT * from emp where empno =: v_empno

Plan Hash value:2949544139

--------------------------------------------------------------------------------------


| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |


--------------------------------------------------------------------------------------


| 0 |        SELECT STATEMENT |       |       |     |          1 (100) | |


|  1 | TABLE ACCESS by INDEX rowid|     EMP |    1 |     38 | 1 (0) | 00:00:01 |


|* 2 | INDEX UNIQUE SCAN |     Pk_emp |       1 |     |          0 (0) | |


--------------------------------------------------------------------------------------

Peeked binds (identified by position):
--------------------------------------

1-: V_empno (number): 7566

predicate information (identified by Operation ID):
---------------------------------------------------

Plan_table_output
----------------------------------------------------------------------------------------------------
2-access ("EMPNO" =:v_empno)

Selected rows.

--Parallel query information filtering
Sql> Select/*+ Parallel (d, 4) parallel (E, 4) */
2 D.dname, AVG (e.sal), Max (e.sal)
3 from Dept D, EMP E
4 Where D.deptno = E.deptno
5 GROUP BY D.dname
6 ORDER by Max (E.sal), avg (e.sal) desc;

Dname AVG (e.sal) MAX (e.sal)
-------------- ---------- ----------
SALES 1566.66667 2850
2175 3000
ACCOUNTING 2916.66667 5000

Sql> select * FROM table (Dbms_xplan.display_cursor (null,null, ' typical-bytes-cost '));

Plan_table_output
----------------------------------------------------------------------------------------------------
sql_id gahr597f78j0d, child number 0
-------------------------------------
Select/*+ Parallel (d, 4) parallel (E, 4)/D.dname, AVG (e.sal),
Max (e.sal) from Dept D, emp e where d.deptno = E.deptno GROUP BY
D.dname ORDER by Max (E.sal), avg (e.sal) desc

Plan Hash value:3078011448

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |    Time | TQ | In-out| PQ Distrib |
--------------------------------------------------------------------------------------------------

Plan_table_output


----------------------------------------------------------------------------------------------------


| 0 |          SELECT STATEMENT |       |          |        |      |            | |


|  1 |          PX Coordinator |       |          |        |      |            | |


|   2 | PX SEND QC (order) |     : TQ10004 | 4 |  00:00:01 | q1,04 | P->s | QC (Order) |


|    3 |          SORT ORDER BY |     | 4 |  00:00:01 | q1,04 |            PCWP | |


|     4 |          PX RECEIVE |     | 4 |  00:00:01 | q1,04 |            PCWP | |


|      5 | PX SEND RANGE |     : TQ10003 | 4 |  00:00:01 | q1,03 | P->p | RANGE |


|       6 |          HASH GROUP by |     | 4 |  00:00:01 | q1,03 |            PCWP | |


|        7 |          PX RECEIVE |    | 14 |  00:00:01 | q1,03 |            PCWP | |


|         8 | PX SEND HASH |    : TQ10002 | 14 |  00:00:01 | q1,02 | P->p | HASH |


|* 9 |          HASH JOIN Buffered |    | 14 |  00:00:01 | q1,02 |            PCWP | |


|           10 |          PX RECEIVE |     | 4 |  00:00:01 | q1,02 |            PCWP | |


|            11 | PX SEND HASH |     : TQ10000 | 4 |  00:00:01 | q1,00 | P->p | HASH |


|             12 |          PX Block Iterator |     | 4 |  00:00:01 | q1,00 |            PCWC | |


|* 13 | TABLE ACCESS full|     DEPT | 4 |  00:00:01 | q1,00 |            PCWP | |


|           14 |          PX RECEIVE |    | 14 |  00:00:01 | q1,02 |            PCWP | |


|            15 | PX SEND HASH |    : TQ10001 | 14 |  00:00:01 | q1,01 | P->p | HASH |


|             16 |          PX Block Iterator |    | 14 |  00:00:01 | q1,01 |            PCWC | |


|* 17 | TABLE ACCESS full|    EMP | 14 |  00:00:01 | q1,01 |            PCWP | |


--------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

9-access ("D".) DEPTNO "=" E "." DEPTNO ")
13-access (: Z>=:z and:z<=:z) 17-access (: Z>=:z and:z<=:z)

Selected rows.