Reference
PL/SQL Packages and Types Reference
10g Release 1 (10.1)
DBMS_XPLAN
The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR).It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.
For more information on the EXPLAIN PLAN command and the AWR, see Oracle Database Performance Tuning Guide. For more information on the V$SQL_PLAN and V$SQL_PLAN_STATISTICS fixed views, see Oracle Database Reference.
Overview
The DBMS_XPLAN package supplies three table functions:
DISPLAY, to format and display the contents of a plan table
DISPLAY_CURSOR, to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_AWR to format and display the contents of the execution plan of a stored SQL statement in the AWR.
--------------------------------------------------------------------------------
Security Model
This package runs with the privileges of the calling user, not the package owner SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.
Using the DISPLAY_AWR function requires to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.
All these privileges are automatically granted as part of the SELECT_CATALOG role.
--------------------------------------------------------------------------------
Examples
Displaying a Plan Table Using DBMS_XPLAN.DISPLAY
Execute an explain plan command on a SELECT 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);
This query produces the following output:
Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."ENAME"='benoit')
15 rows selected.
Displaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSOR
By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. For example:
SELECT ename FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno=7369;
ENAME
----------
SMITH
To display the execution plan of the last executed statement for that session:
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
This query produces the following output:
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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."EMPNO"=7369)
21 rows selected.
You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.
Run a query with a distinctive comment:
SELECT /* TOTO */ ename, dname
FROM dept d join emp e USING (deptno);
Get sql_id and child_number for the preceding statement:
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%TOTO%';
SQL_ID CHILD_NUMBER
---------- -----------------------------
gwp663cqh5qbf 0
Display the execution plan for the cursor:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));
Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname
FROM dept d JOIN emp e USING (deptno);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:
Display the execution plan of all cursors matching the string 'TOTO':
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE
sql_text LIKE '%TOTO%';
Displaying a Plan Table with Parallel Information
By default, only relevant information is reported by the display and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Hence, information related to the parallelization of the plan is not reported. As shown in the following example, parallel information is reported only if the query executes in parallel.
ALTER TABLE emp PARALLEL;
EXPLAIN PLAN for
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename ='hermann'
ORDER BY e.empno;
Display the plan using the DBMS_XPLAN.DISPLAY table function
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3693697345
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |INOUT |PQ Distrib |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 6 (50) | 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) |:TQ10003 | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | P->S | QC (ORDER)|
| 3 | SORT ORDER BY | | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1 | 117 | 5 (40) | 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND RANGE |:TQ10002 | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | P->P | RANGE |
|* 6 | HASH JOIN | | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1 | 87 | 2 (50) | 00:00:01 | Q1,02 | PCWP | |
| 8 | PX SEND HASH |:TQ10001 | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWC | |
|* 10| TABLE ACCESS FULL | EMP | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWP | |
| 11 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 12 | PX RECEIVE | | 4 | 120 | 3 (34) | 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH |:TQ10000 | 4 | 120 | 3 (34) | 00:00:01 | | S->P | HASH |
| 14 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (34) | 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - filter("E"."ENAME"='hermann')
---------------------------------------------------
When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (INOUT) and table queue distribution method (PQ Distrib).
By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN. However, you should purge the plan table regularly (for example, by using the TRUNCATE TABLE command) to ensure good performance in the execution of the DISPLAY table function.
For ease of use, you can define a view on top of the display table function and then use that view to display the output of the EXPLAIN PLAN command:
Using a View to Display Last Explain Plan
# define plan view
CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
# display the output of the last explain plan command
SELECT * FROM PLAN;
--------------------------------------------------------------------------------
Summary of DBMS_XPLAN Subprograms
Table 120-1 DBMS_XPLAN Package Subprograms
Subprogram Description
DISPLAY_AWR Function
Displays the contents of an execution plan stored in the AWR
DISPLAY Function
Displays the contents of the plan table
DISPLAY_CURSOR Function
Displays the execution plan of any cursor in the cursor cache
--------------------------------------------------------------------------------
DISPLAY_AWR Function
This table function displays the contents of an execution plan stored in the AWR.
Syntax
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
Parameter Description
sql_id
Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.
plan_hash_value
Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID.
db_id
Specifies the database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, the database_id of the local database will be used, as shown in V$DATABASE.
format
Controls the level of details for the plan. It has the same set of values than the table function DISPLAY, that is, BASIC, TYPICAL, SERIAL and ALL.
Usage Notes
To use the DISPLAY_AWR functionality, the calling user must have SELECT privilege on DBA_HIST_SQL_PLAN. DBA_HIST_SQLTEXT, and V$DATABASE, otherwise it will show an appropriate error message.
Examples
To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
To display the execution plan of all stored SQL statements containing the string 'TOTO':
SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table
(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf
WHERE ht.sql_text like '%TOTO%';
--------------------------------------------------------------------------------
DISPLAY Function
This table function displays the contents of the plan table.
Syntax
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
Parameters
table_name
Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE, which is the default plan table for the EXPLAIN PLAN command. If NULL is specified it also defaults to PLAN_TABLE.
statement_id
Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.If no statement_id is specified, the function will show you the plan of the most recent explained statement.
format
Controls the level of details for the plan. It accepts four values:
BASIC: Displays the minimum information in the plan--the operation ID, the object name, and the operation option.
TYPICAL: This is the default. Displays the most relevant information in the plan. Partition pruning, parallelism, and predicates are displayed only when available.
ALL: Maximum level. Includes information displayed with the TYPICAL level and adds projection information as well as SQL statements generated for parallel execution servers (only if parallel).
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
Examples
To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table(DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
To display the plan for a statement identified by 'foo', such as statement_id='foo':
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'foo'));
--------------------------------------------------------------------------------
DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache.
Syntax
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
Parameters
sql_id
Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.
child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.
format
Controls the level of details for the plan. It has the same set of values than the table function 'DISPLAY', that is, 'BASIC', 'TYPICAL', 'SERIAL' and 'ALL'. Two additional values are also supported to display run-time statistics for the cursor:
RUNSTATS_LAST: Displays the runtime statistics for the last execution of the cursor.
RUNSTATS_TOT: Displays the total aggregated runtime statistics for all executions of a specific SQL statement since the statement was first parsed and executed.
Format options 'RUNSTATS_LAST' and 'RUNSTATS_TOT' can only be used if the target cursor was compiled and executed with the initialization parameter 'statistics_level' set to 'ALL'.
Usage Notes:
To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise it will show an appropriate error message.
Examples
To display the execution plan of the last SQL statement executed by the current session:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
To display runtime statistics for the cursor included in the preceding statement:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', null, 'RUNSTATS_
LAST');
Understanding % CPU Cost in Explain Plan
What is the Meaning of the %CPU Column in an Explain Plan?
Let’s try creating an explain plan on Oracle 11.2.0.1 for a query:
EXPLAIN PLAN FOR
SELECT
T1.C1,
T1.C2,
T1.C3
FROM
T1,
(SELECT
C1,
C2
FROM
T1
WHERE
MOD(C1,3)=0) V
WHERE
T1.C1=V.C1(+)
AND V.C1 IS NULL
ORDER BY
T1.C1 DESC;
The above command wrote a couple of rows into the PLAN_TABLE table. At this point, we should probably consult the documentation to understand the columns in the
PLAN_TABLE table.
COST: Cost of the operation as estimated by the optimizer’s query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.
IO_COST: I/O cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.
CPU_COST: CPU cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.
We found a couple of interesting columns in the PLAN_TABLE table, so let’s query the table
SELECT
ID,
COST,
IO_COST,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST CPU_COST
--- ----- -------- ----------
0 1482 1467 364928495
1 1482 1467 364928495
2 898 887 257272866
3 889 887 42272866
4 0 0 2150
Now let’s display the execution plan:
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1923834833
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99000 | 1836K| | 1482 (2)| 00:00:18 |
| 1 | SORT ORDER BY | | 99000 | 1836K| 2736K| 1482 (2)| 00:00:18 |
| 2 | NESTED LOOPS ANTI | | 99000 | 1836K| | 898 (2)| 00:00:11 |
| 3 | TABLE ACCESS FULL| T1 | 100K| 1367K| | 889 (1)| 00:00:11 |
|* 4 | INDEX UNIQUE SCAN| SYS_C0018049 | 10 | 50 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C1"="C1")
filter(MOD("C1",3)=0)
The %CPU is 2 for ID 0, 1, and 2, and the %CPU is 1 for ID 3. Let’s return to the query of the PLAN_TABLE table and perform a couple of calculations:
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
--- ----- -------- ----- -------- ----------
0 1482 1467 15 2 364928495
1 1482 1467 15 2 364928495
2 898 887 11 2 257272866
3 889 887 2 1 42272866
4 0 0 0 0 2150
In the above, I subtracted the IO_COST column from the COST column to derive the DIFF column. I then divided the value in the DIFF column by the COST column, multiplied the result by 100 to convert the number to a percent, and then rounded up the result to derive the PER_CPU column. The PER_CPU column seems to match the %CPU column in the DBMS_XPLAN output. Let’s try another SQL statement:
DELETE FROM PLAN_TABLE;
EXPLAIN PLAN FOR
SELECT
C1
FROM
T1
WHERE
'A'||C1 LIKE 'A%';
Now let’s run the query against the PLAN_TABLE table to see if we are able to predict the values that will appear in the %CPU column of the DBMS_XPLAN output:
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
--- ----- -------- ----- -------- ----------
0 54 52 2 4 43331709
1 54 52 2 4 43331709
The above indicates that the %CPU column should show the number 4 on both rows of the execution plan.
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2950179127
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 25000 | 54 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| SYS_C0018049 | 5000 | 25000 | 54 (4)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter('A'||TO_CHAR("C1") LIKE 'A%')
One of my previous blog articles showed the following execution plan – this was the actual plan displayed by DBMS_XPLAN.DISPLAY_CURSOR after the SQL statement executed:
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 247 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T1 | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
filter(("C1"<=10000 AND "C1">=1))
Is there anything strange about the %CPU column in the above plan?
Incidentally, a query of SYS.AUX_STATS$ shows the following output (values are used to determine the impact of the CPU_COST column that is displayed in the PLAN_TABLE table):
SELECT
PNAME,
PVAL1
FROM
SYS.AUX_STATS$
WHERE
PNAME IN ('CPUSPEED','CPUSPEEDNW');
PNAME PVAL1
---------- ----------
CPUSPEEDNW 2031.271
CPUSPEED
PL/SQL Packages and Types Reference
10g Release 1 (10.1)
DBMS_XPLAN
The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR).It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.
For more information on the EXPLAIN PLAN command and the AWR, see Oracle Database Performance Tuning Guide. For more information on the V$SQL_PLAN and V$SQL_PLAN_STATISTICS fixed views, see Oracle Database Reference.
Overview
The DBMS_XPLAN package supplies three table functions:
DISPLAY, to format and display the contents of a plan table
DISPLAY_CURSOR, to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_AWR to format and display the contents of the execution plan of a stored SQL statement in the AWR.
--------------------------------------------------------------------------------
Security Model
This package runs with the privileges of the calling user, not the package owner SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.
Using the DISPLAY_AWR function requires to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.
All these privileges are automatically granted as part of the SELECT_CATALOG role.
--------------------------------------------------------------------------------
Examples
Displaying a Plan Table Using DBMS_XPLAN.DISPLAY
Execute an explain plan command on a SELECT 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);
This query produces the following output:
Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."ENAME"='benoit')
15 rows selected.
Displaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSOR
By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. For example:
SELECT ename FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno=7369;
ENAME
----------
SMITH
To display the execution plan of the last executed statement for that session:
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
This query produces the following output:
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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."EMPNO"=7369)
21 rows selected.
You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.
Run a query with a distinctive comment:
SELECT /* TOTO */ ename, dname
FROM dept d join emp e USING (deptno);
Get sql_id and child_number for the preceding statement:
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%TOTO%';
SQL_ID CHILD_NUMBER
---------- -----------------------------
gwp663cqh5qbf 0
Display the execution plan for the cursor:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));
Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname
FROM dept d JOIN emp e USING (deptno);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:
Display the execution plan of all cursors matching the string 'TOTO':
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE
sql_text LIKE '%TOTO%';
Displaying a Plan Table with Parallel Information
By default, only relevant information is reported by the display and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Hence, information related to the parallelization of the plan is not reported. As shown in the following example, parallel information is reported only if the query executes in parallel.
ALTER TABLE emp PARALLEL;
EXPLAIN PLAN for
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename ='hermann'
ORDER BY e.empno;
Display the plan using the DBMS_XPLAN.DISPLAY table function
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3693697345
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |INOUT |PQ Distrib |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 6 (50) | 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) |:TQ10003 | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | P->S | QC (ORDER)|
| 3 | SORT ORDER BY | | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1 | 117 | 5 (40) | 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND RANGE |:TQ10002 | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | P->P | RANGE |
|* 6 | HASH JOIN | | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1 | 87 | 2 (50) | 00:00:01 | Q1,02 | PCWP | |
| 8 | PX SEND HASH |:TQ10001 | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWC | |
|* 10| TABLE ACCESS FULL | EMP | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWP | |
| 11 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 12 | PX RECEIVE | | 4 | 120 | 3 (34) | 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH |:TQ10000 | 4 | 120 | 3 (34) | 00:00:01 | | S->P | HASH |
| 14 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (34) | 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - filter("E"."ENAME"='hermann')
---------------------------------------------------
When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (INOUT) and table queue distribution method (PQ Distrib).
By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN. However, you should purge the plan table regularly (for example, by using the TRUNCATE TABLE command) to ensure good performance in the execution of the DISPLAY table function.
For ease of use, you can define a view on top of the display table function and then use that view to display the output of the EXPLAIN PLAN command:
Using a View to Display Last Explain Plan
# define plan view
CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
# display the output of the last explain plan command
SELECT * FROM PLAN;
--------------------------------------------------------------------------------
Summary of DBMS_XPLAN Subprograms
Table 120-1 DBMS_XPLAN Package Subprograms
Subprogram Description
DISPLAY_AWR Function
Displays the contents of an execution plan stored in the AWR
DISPLAY Function
Displays the contents of the plan table
DISPLAY_CURSOR Function
Displays the execution plan of any cursor in the cursor cache
--------------------------------------------------------------------------------
DISPLAY_AWR Function
This table function displays the contents of an execution plan stored in the AWR.
Syntax
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
Parameter Description
sql_id
Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.
plan_hash_value
Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID.
db_id
Specifies the database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, the database_id of the local database will be used, as shown in V$DATABASE.
format
Controls the level of details for the plan. It has the same set of values than the table function DISPLAY, that is, BASIC, TYPICAL, SERIAL and ALL.
Usage Notes
To use the DISPLAY_AWR functionality, the calling user must have SELECT privilege on DBA_HIST_SQL_PLAN. DBA_HIST_SQLTEXT, and V$DATABASE, otherwise it will show an appropriate error message.
Examples
To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
To display the execution plan of all stored SQL statements containing the string 'TOTO':
SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table
(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf
WHERE ht.sql_text like '%TOTO%';
--------------------------------------------------------------------------------
DISPLAY Function
This table function displays the contents of the plan table.
Syntax
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
Parameters
table_name
Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE, which is the default plan table for the EXPLAIN PLAN command. If NULL is specified it also defaults to PLAN_TABLE.
statement_id
Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.If no statement_id is specified, the function will show you the plan of the most recent explained statement.
format
Controls the level of details for the plan. It accepts four values:
BASIC: Displays the minimum information in the plan--the operation ID, the object name, and the operation option.
TYPICAL: This is the default. Displays the most relevant information in the plan. Partition pruning, parallelism, and predicates are displayed only when available.
ALL: Maximum level. Includes information displayed with the TYPICAL level and adds projection information as well as SQL statements generated for parallel execution servers (only if parallel).
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
Examples
To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table(DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
To display the plan for a statement identified by 'foo', such as statement_id='foo':
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'foo'));
--------------------------------------------------------------------------------
DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache.
Syntax
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
Parameters
sql_id
Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.
child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.
format
Controls the level of details for the plan. It has the same set of values than the table function 'DISPLAY', that is, 'BASIC', 'TYPICAL', 'SERIAL' and 'ALL'. Two additional values are also supported to display run-time statistics for the cursor:
RUNSTATS_LAST: Displays the runtime statistics for the last execution of the cursor.
RUNSTATS_TOT: Displays the total aggregated runtime statistics for all executions of a specific SQL statement since the statement was first parsed and executed.
Format options 'RUNSTATS_LAST' and 'RUNSTATS_TOT' can only be used if the target cursor was compiled and executed with the initialization parameter 'statistics_level' set to 'ALL'.
Usage Notes:
To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise it will show an appropriate error message.
Examples
To display the execution plan of the last SQL statement executed by the current session:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
To display runtime statistics for the cursor included in the preceding statement:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', null, 'RUNSTATS_
LAST');
Understanding % CPU Cost in Explain Plan
What is the Meaning of the %CPU Column in an Explain Plan?
Let’s try creating an explain plan on Oracle 11.2.0.1 for a query:
EXPLAIN PLAN FOR
SELECT
T1.C1,
T1.C2,
T1.C3
FROM
T1,
(SELECT
C1,
C2
FROM
T1
WHERE
MOD(C1,3)=0) V
WHERE
T1.C1=V.C1(+)
AND V.C1 IS NULL
ORDER BY
T1.C1 DESC;
The above command wrote a couple of rows into the PLAN_TABLE table. At this point, we should probably consult the documentation to understand the columns in the
PLAN_TABLE table.
COST: Cost of the operation as estimated by the optimizer’s query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.
IO_COST: I/O cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.
CPU_COST: CPU cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.
We found a couple of interesting columns in the PLAN_TABLE table, so let’s query the table
SELECT
ID,
COST,
IO_COST,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST CPU_COST
--- ----- -------- ----------
0 1482 1467 364928495
1 1482 1467 364928495
2 898 887 257272866
3 889 887 42272866
4 0 0 2150
Now let’s display the execution plan:
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1923834833
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99000 | 1836K| | 1482 (2)| 00:00:18 |
| 1 | SORT ORDER BY | | 99000 | 1836K| 2736K| 1482 (2)| 00:00:18 |
| 2 | NESTED LOOPS ANTI | | 99000 | 1836K| | 898 (2)| 00:00:11 |
| 3 | TABLE ACCESS FULL| T1 | 100K| 1367K| | 889 (1)| 00:00:11 |
|* 4 | INDEX UNIQUE SCAN| SYS_C0018049 | 10 | 50 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C1"="C1")
filter(MOD("C1",3)=0)
The %CPU is 2 for ID 0, 1, and 2, and the %CPU is 1 for ID 3. Let’s return to the query of the PLAN_TABLE table and perform a couple of calculations:
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
--- ----- -------- ----- -------- ----------
0 1482 1467 15 2 364928495
1 1482 1467 15 2 364928495
2 898 887 11 2 257272866
3 889 887 2 1 42272866
4 0 0 0 0 2150
In the above, I subtracted the IO_COST column from the COST column to derive the DIFF column. I then divided the value in the DIFF column by the COST column, multiplied the result by 100 to convert the number to a percent, and then rounded up the result to derive the PER_CPU column. The PER_CPU column seems to match the %CPU column in the DBMS_XPLAN output. Let’s try another SQL statement:
DELETE FROM PLAN_TABLE;
EXPLAIN PLAN FOR
SELECT
C1
FROM
T1
WHERE
'A'||C1 LIKE 'A%';
Now let’s run the query against the PLAN_TABLE table to see if we are able to predict the values that will appear in the %CPU column of the DBMS_XPLAN output:
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
--- ----- -------- ----- -------- ----------
0 54 52 2 4 43331709
1 54 52 2 4 43331709
The above indicates that the %CPU column should show the number 4 on both rows of the execution plan.
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2950179127
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 25000 | 54 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| SYS_C0018049 | 5000 | 25000 | 54 (4)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter('A'||TO_CHAR("C1") LIKE 'A%')
One of my previous blog articles showed the following execution plan – this was the actual plan displayed by DBMS_XPLAN.DISPLAY_CURSOR after the SQL statement executed:
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 247 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T1 | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
filter(("C1"<=10000 AND "C1">=1))
Is there anything strange about the %CPU column in the above plan?
Incidentally, a query of SYS.AUX_STATS$ shows the following output (values are used to determine the impact of the CPU_COST column that is displayed in the PLAN_TABLE table):
SELECT
PNAME,
PVAL1
FROM
SYS.AUX_STATS$
WHERE
PNAME IN ('CPUSPEED','CPUSPEEDNW');
PNAME PVAL1
---------- ----------
CPUSPEEDNW 2031.271
CPUSPEED
No comments:
Post a Comment