Detailed Explanation of DBMS_XPLAN Processing Execution Plan in Oracle
- 2021-12-13 10:06:05
- OfStack
DBMS_XPLAN is a package provided by Oracle for viewing SQL plans, including execution plans and interpretation plans; I used the set autotrace command when I looked at the SQL execution plan before, but now it seems that the DBMS_XPLAN package gives a much simpler way to get and display the plan.
These five functions correspond to different ways of displaying plans. The DBMS_XPLAN package can not only obtain interpretation plans, but also output statement plans stored in AWR, SQL debug sets, cached SQL cursors, and SQL baselines. To achieve the above functions, one or five methods are usually used:
1.DISPLAY
2.DISPLAY_AWR
3.DISPLAY_CURSOR
4.DISPLAY_PLAN
5.DISPLAY_SQL_PLAN_BASELINE
6.DISPLAY_SQLSET
The following will focus on the application of the DBMS_XPLAN package in interpreting and executing plans.
Look at a frequently used example of an interpretation plan for viewing a statement:
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.
-- Correspondence autotrace Realization
SQL> set autotrace traceonly explain
In the above example, the dbms_xplan.display method is used to display the interpretation plan saved in PLAN_TABLE. If you want to display the execution plan, you need to use the DMBS_XPLAN.DISPLAY_CURSOR method. DMBS_XPLAN.DISPLAY_CURSOR call signature:
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
sql_id denotes id of SQL statement stored in cursor cache, child_number is used to indicate id of caching sql statement plan, and format parameter is used to control the type of information contained in the output. The parameters of official file are as follows:
1. BASIC: Displays minimal information, including only the operation type, ID name, and options.
2. TYPICAL: Default, display information, and some additional display options, such as partitioning and concurrent usage.
3. SERIAL: Similar to the TYPICAL type, except that it does not include concurrent information, even plans that execute in parallel.
4. ALL: Displays the most information, including all of TYPICAL and more additional information, such as aliases and remote calls.
In addition to the above four basic output formats, format has one additional option for customizing output behavior. In use, multiple keywords can be declared by comma and space separation, and "+" and "-" symbols can be used to include or exclude corresponding display elements. These additional options are also recorded in the official file:
1. ROWS-Displays the line number of the record estimated by the optimizer
2. BYTES-Number of bytes estimated by the display optimizer
3. COST-Displays cost information calculated by the optimizer
4. PARTITION-Displays partition information for partitions
5. PARALLEL-Display parallel execution information
6. PREDICATE-Display predicate
7. PROJECTION-Displays the column projection section (those columns per 1 row are passed to their parent columns and have the size of those columns)
8. ALIAS-Shows the query block name has been aliased by the object
9. REMOTE-Display distributed query information
10. NOTE-Display comments
11. IOSTATS-Displays IO statistics for cursor execution
12. MEMSTATS-Displays memory management statistics for memory-intensive operations such as hash join, sort, or some type of bitmap operation
13. ALLSTATS-Equivalent to 'IOSTATS MEMSTATS'
14. LAST-Displays the last execution plan statistics, default to ALL type, and can be accumulated.
The above parameters are also applicable to the display method of interpreting the plan.
Example section:
1. Use the display_cursor method to view the execution plan for the last statement
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 |
-------------------------------------------------------------------------------------
14 rows selected.
When using dbms_xplan.display_cursor (null, null, 'ALLSTATS LAST'), set sql_id and child_number to null, indicating that the execution plan of the previous execution statement is obtained; Note that in the above example, 1 must specify the gather_plan_statistics hint or manually set the database STATISTICS_LEVEL parameter to ALL to make it capture the execution statistics of the row data source, including the number of rows, the number of direct readings, the number of physical readings, the number of physical writes and the running time of the operation on 1 row of data. If this hint is not specified, there will be no A-Rows, A-Time and Buffers columns.
2. Gets the execution plan for a specified statement
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);
COUNT(*)
----------
9
-- Pass v$sql View queries to sql Statement SQL_ID And CHILD_NUMBER
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 /*+ gather_plan_statisti
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))
21 rows selected.
3. Customize the execution plan output information through format parameters
-- Use ALL To display all the information that explains the 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]
43 rows selected.
-- Remove bytes and cost statistics from 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))
19 rows selected.
-- Another 1 A kind of option, peep the value of bound variables, 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)
24 rows selected.
-- 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
RESEARCH 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)
38 rows selected.