SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno This query produces the following output: Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR) To display the execution plan of the last executed statement for that session: SET PAGESIZE 0 For example: SELECT ename FROM emp e, dept d Predicate Information (identified by operation id):ĭisplaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSORīy default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
This query produces the following output: Plan hash value: 3693697075 Using DISPLAY_SQL_PLAN_BASELINE Function the user requires the user to have the SELECT privilege on DBA_SQL_PLAN_BASELINES.Īll these privileges are automatically granted as part of the SELECT_CATALOG role.ĭisplaying a Plan Table Using DBMS_XPLAN.DISPLAYĮxecute an explain plan command on a SELECT statement: EXPLAIN PLAN FORĭisplay the plan using the DBMS_XPLAN.DISPLAY table function SET LINESIZE 130 Using the DISPLAY_SQLSET Functionrequires the user to have the SELECT privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS. Using the DISPLAY_AWR Function requires the user to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE. 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. This package runs with the privileges of the calling user, not the package owner ( SYS). The DBMS_XPLAN package supplies five table functions:ĭISPLAY - to format and display the contents of a plan table.ĭISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.ĭISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.ĭISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handleĭISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.