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
2. Then search SQL ID for the query that is executed
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
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;
from emp e ,dept d where d.deptno = e.deptno;
select sql_id,sql_text from v$sqlarea where sql_text like '%sql_execution2%';
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'));
