How to get actual explain plan of query in oracle

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 runtime 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.

    how to use

    -- first execute the query
select e.ename , e.sal, d.dname from emp e ,dept d where e.deptno = d.deptno;
    -- then search sql id for the query that is executed
select sql_id,sql_text from v$sqlarea where sql_text like '%select e.ename , e.sal, d.dname from emp e ,dept d where e.deptno = d.deptno;%'; 

SQL_ID        SQL_TEXT
------------- ---------------------------------------------------
1rk5afg9tcn4f select sql_id,sql_text from v$sqlarea where sql_tex
dmrp9jvfxd89f select e.ename , e.sal, d.dname from emp e ,dept d
4fdqm0tbnycyb select sql_id,sql_text from v$sqlarea where sql_tex

    -- chose the correct sql_id wrt to your query, then excute the below query to get actual plan
select * from table(dbms_xplan.display_cursor('dmrp9jvfxd89f'));

  

For any modification , updatation in post please comment