DBMS_XPLAN.DISPLAY_CURSOR

DBMS_XPLAN.DISPLAY_CURSOR show actual execution plan from dynamic performance views.

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) or
stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution run time statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.

Parameter for DBMS_XPLAN.DISPLAY_CURSOR:
SQL_ID: sql_id we will get from V$SQL OR V$SQLAREA. Default this value as NULL.

STEPS:
1. Execute the query

select e.ename , e.sal, d.dname/*sql_execution2*/ 
 from emp e ,dept d where d.deptno = e.deptno; 

2. Then search SQL ID for the query that is executed

select sql_id,sql_text from v$sqlarea where sql_text like '%sql_execution2%';

OUTPUT:
SQL_ID                 SQL_TEXT
-----------------       ------------------------------------------------------------------------------------------

bt7bfjp0525vu      select e.ename , e.sal, d.dname/*sql_execution2*/ from emp e ,dept d where                       
3. Chose the correct SQL_ID write to your query, then execute the below query to get actual plan

select * from table(dbms_xplan.display_cursor('bt7bfjp0525vu'));

DBMS_XPLAN

One more way we can see the Explain Plan by using DBMS_XPLAN package.

• DBMS_XPLAN automatically queries the last plan in PLAN_TABLE.
• It uses a TABLE() function with another pipelined function.

Example of DBMS_XPLAN:

select * from table(dbms_xplan.display);    

PLAN_TABLE Detils

In PLAN TABLE some of the important columns descriptions are as follows :