Displaying Execution Plans

Display Most Recent Plan from PLAN_TABLE

WITH CurrPlan AS (SELECT * FROM PLAN_TABLE WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM PLAN_TABLE)) 
select 
  ID, 
  lpad(' ', level-1) || 'ParID='||PARENT_ID || ':'||operation || ' (' || options || ')' "Operation", 
  object_name "Object",
  CurrPlan.*
from 
  CurrPlan
 start with id = 0 
connect by prior id=parent_id;

Display from Cursor Cache
Add a unique comment hint (like QUERYCOMMENT001 below) so we can locate the SQL easily in v$sql.

WITH targetsql AS
     (SELECT sql_id, child_number, sql_text
      FROM gv$sql
      WHERE sql_text LIKE '%QUERYCOMMENT001%' AND 
            sql_text NOT LIKE '%PLAN_QUERY%' 
            AND UPPER (sql_text) NOT LIKE '%XPLAN%')
SELECT /* PLAN_QUERY */ ID, 
       LPAD (' ', LEVEL - 1) || 'ParID=' || parent_id || ':' || operation || ' (' || options || ')' "Operation", 
       object_name "Object", 
       sp.*,
       TargetSQL.*
  FROM gv$sql_plan sp, targetsql
 WHERE sp.sql_id = targetsql.sql_id AND sp.child_number = targetsql.child_number
 START WITH ID = 0
CONNECT BY PRIOR ID = parent_id;

Display with Plan Statistics
Add a unique comment hint (like QUERYCOMMENT001 below) so we can locate the SQL easily in v$sql.

This is similar information as DBMX_XPLAN with ALLSTATS LAST option. More information is given here about query block names and such - this helps if you are trying to issue hints for specific places in your query.

WITH TargetSQL AS
     (SELECT sql_id, child_number, sql_text
      FROM gv$sql
      WHERE sql_text LIKE '%QUERYCOMMENT001%' AND 
            sql_text NOT LIKE '%PLAN_QUERY%' 
            AND UPPER (sql_text) NOT LIKE '%XPLAN%')
SELECT /* PLAN_QUERY */ ID, 
       LPAD (' ', LEVEL - 1) || 'ParID=' || parent_id || ':' || operation || ' (' || options || ')' "Operation", 
       OBJECT_OWNER "Owner",
       OBJECT_NAME,
       object_alias "Alias",
       QBLOCK_NAME, 
       LAST_STARTS as Starts,
       CARDINALITY as EST_ROWS,
       LAST_OUTPUT_ROWS as ACT_ROWS,
       TO_CHAR(LAST_ELAPSED_TIME/1000000,'9999.99') as "ElapsedSecs",
       '|||' as "|||",
       sp.*,
       ts.*,
       sps.*
  FROM targetsql ts, 
       gv$sql_plan sp, 
       gv$sql_plan_statistics sps
 WHERE ts.sql_id = sp.sql_id  
   AND ts.child_number = sp.child_number
   AND sp.sql_id=sps.sql_id (+)
   AND sp.child_number=sps.child_number (+)
   AND sp.id = sps.operation_id (+)
 START WITH SP.ID = 0
CONNECT BY PRIOR SP.ID = SP.parent_id;