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 requires the user to have the SELECT privilege on DBA_SQL_PLAN_BASELINES as well as the privileges to execute the SQL statement for which the user is trying to get the plan.Ī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.

Walgreens xplan