The DBMS_XPLAN
package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for
the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.
Related articles.
If it is not already present create the SCOTT
schema.
conn sys/password as sysdba @$ORACLE_HOME/rdbms/admin/utlsampl.sql
Create a PLAN_TABLE
if it does not already exist.
conn sys/password as sysdba @$ORACLE_HOME/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table; GRANT ALL ON sys.plan_table TO public;
The DISPLAY
function allows us to display the execution plan stored in the plan table. First we explain a SQL statement.
CONN scott/tiger EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
Next we use the DBMS_XPLAN.DISPLAY
function to display the execution plan.
SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ---------------------------------------------------------------------------------------- | 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 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."ENAME"='SMITH') 4 - access("E"."DEPTNO"="D"."DEPTNO") 18 rows selected. SQL>
The DBMS_XPLAN.DISPLAY
function can accept 3 optional parameters:
PLAN_TABLE
, default value 'PLAN_TABLE'.PLAN_TABLE
.From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with '-'). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.
EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH'; SET LINESIZE 130 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC')); Plan hash value: 3625962092 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | EMP | | 4 | INDEX UNIQUE SCAN | PK_DEPT | | 5 | TABLE ACCESS BY INDEX ROWID| DEPT | ------------------------------------------------ 12 rows selected. SQL>
In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR
function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL
, V$SQL
and V$SQL_PLAN
views, so the user must have access to these. It accepts three optional parameters:
SQL_ID
of the statement in the cursor cache. The SQL_ID
as available from the V$SQL
and V$SQLAREA
views, or from the V$SESSION
view using the PREV_SQL_ID
column. If omitted, the last cursor executed by the session is displayed.SQL_ID
parameter. If not specified, all cursors for the specified SQL_ID
are diaplyed.DISPLAY
function, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively. These additional format options require "STATISTICS_LEVEL=ALL".The following example show the advanced output from a query on the SCOTT schema.
CONN / AS SYSDBA GRANT SELECT ON v_$session TO scott; GRANT SELECT ON v_$sql TO scott; GRANT SELECT ON v_$sql_plan TO scott; GRANT SELECT ON v_$sql_plan_statistics_all TO scott; CONN scott/tiger SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH'; SET LINESIZE 130 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID gu62pbk51ubc3, child number 0 ------------------------------------- SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH' Plan hash value: 3625962092 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | 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)| | | 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 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.2') DB_VERSION('11.2.0.2') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "E"@"SEL$1") INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO")) LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1") USE_NL(@"SEL$1" "D"@"SEL$1") NLJ_BATCHING(@"SEL$1" "D"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."ENAME"='SMITH') 4 - access("E"."DEPTNO"="D"."DEPTNO") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "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], "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] 67 rows selected. SQL>
There are some other useful functions in the package, but I don't find myself using them very often, so they are summarized below. If you need more information, follow the links at the bottom of the article for the appropriate database version.
DISPLAY_AWR
- Introduced in Oracle 10g Release 1, this function displays an execution plan stored in the Advanced Workload Repository (AWR).DISPLAY_SQLSET
- Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.DISPLAY_SQL_PLAN_BASELINE
- Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.DISPLAY_PLAN
- Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.Execution plans can look very confusing, but reading them is reasonably simple provided you follow three simple rules:
Looking at the following execution plan, the order of the operations is 4, 5, 3, 6, 2, 9, 10, 8, 7, 1, 0.
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 570 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 10 | 570 | 7 (15)| 00:00:01 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 10 | 380 | 4 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 | | 7 | VIEW | index$_join$_004 | 107 | 2033 | 3 (34)| 00:00:01 | |* 8 | HASH JOIN | | | | | | | 9 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 | | 10 | INDEX FAST FULL SCAN | EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Using Adrian Billington's utility, described below, we can show the plan including the order of the operations in the "Ord" column.
--------------------------------------------------------------------------------------------------------------- | Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | | 11 | SELECT STATEMENT | | 10 | 570 | 7 (15)| 00:00:01 | |* 1 | 0 | 10 | HASH JOIN | | 10 | 570 | 7 (15)| 00:00:01 | | 2 | 1 | 5 | NESTED LOOPS | | | | | | | 3 | 2 | 3 | NESTED LOOPS | | 10 | 380 | 4 (0)| 00:00:01 | |* 4 | 3 | 1 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 | |* 5 | 3 | 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 | | 6 | 2 | 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 | | 7 | 1 | 9 | VIEW | index$_join$_004 | 107 | 2033 | 3 (34)| 00:00:01 | |* 8 | 7 | 8 | HASH JOIN | | | | | | | 9 | 8 | 6 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 | | 10 | 8 | 7 | INDEX FAST FULL SCAN | EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------
Adrian Billington has created an "XPlan Utility", available here, to extend the output of DBMS_XPLAN
to include the execution order of the steps. The following output shows the difference between the default output and that produced by Adrian's XPlan Utility.
CONN scott/tiger EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH'; SET LINESIZE 130 -- Default Output SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- Plan hash value: 3625962092 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 58 | 3 (0)| 00:00:53 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:53 | |* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 2 (0)| 00:00:35 | |* 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:18 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."ENAME"='SMITH') 4 - access("E"."DEPTNO"="D"."DEPTNO") 18 rows selected. SQL> -- XPlan Utility output @xplan.display.sql PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 3625962092 ---------------------------------------------------------------------------------------------------- | Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | | 6 | SELECT STATEMENT | | 1 | 58 | 3 (0)| 00:00:53 | | 1 | 0 | 5 | NESTED LOOPS | | | | | | | 2 | 1 | 3 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:53 | |* 3 | 2 | 1 | TABLE ACCESS FULL | EMP | 1 | 38 | 2 (0)| 00:00:35 | |* 4 | 2 | 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | | 5 | 1 | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:18 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."ENAME"='SMITH') 4 - access("E"."DEPTNO"="D"."DEPTNO") About ------ - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net) 18 rows selected. SQL>
In the extended output, the "Ord" column displays the execution order of the plan steps.
Sometimes the same table is included in a query multiple times, so it is hard to know which operation in the execution plan refers to which reference to the table. The QB_NAME
solves this problem by allowing you to name, or alias, individual query blocks. The alias information is displayed when the FORMAT
parameter of the DISPLAY%
functions is set to "ALL", or the " +ALIAS" value is added to the FORMAT
parameter in 10gR2 onwards.
The following query references the same table twice, so we cannot easily tell from the execution plan which reference is which.
SELECT (SELECT COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count, (SELECT COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count FROM dual; SET LINESIZE 100 SET PAGESIZE 50 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 8 | | | |* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------
Next, we add the QB_NAME
hint to the subqueries, giving each a recognizable alias. When the correct format is selected, the output from the DISPLAY% functions now includes a table giving us the relevant alias for each operation.
SELECT (SELECT /*+ QB_NAME(salesman) */ COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count, (SELECT /*+ QB_NAME(manager) */ COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count FROM dual; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALL)); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 8 | | | |* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SALESMAN 2 - SALESMAN / EMP@SALESMAN 3 - MANAGER 4 - MANAGER / EMP@MANAGER 5 - SEL$1 / DUAL@SEL$1
Using the GATHER_PLAN_STATISTICS
hint makes the optimizer gather the actual cardinalities in addition to the expected cardinalities in the execution plan. This can then be reported by the DISPLAY_CURSOR
function if the format is set to 'ALLSTATS'.
CONN scott/tiger SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH'; SET LINESIZE 130 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST')); -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 | | 1 | NESTED LOOPS | | 1 | | 1 |00:00:00.01 | 9 | | 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 | |* 3 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 7 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | | 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | --------------------------------------------------------------------------------------------------
Now the "Rows" column has been replaced by the original cardinality estimate (E-Rows) and the actual cardinality (A-Rows).
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/dbms_xplan