dbms_xplan

SQL> col PLAN_TABLE_OUTPUT format a100
SQL> set linesize 1000

SQL> set pagesize 0

/* Formatted on 2013/4/8 14:27:04 (QP5 v5.114.809.3010) */
WITH emps AS (  SELECT   deptno, COUNT ( * ) AS cnt
                  FROM   emp
              GROUP BY   deptno)
SELECT /*+ gather_plan_statistics  */ dept.dname, emps.cnt
  FROM   dept, emps
 WHERE   dept.deptno = emps.deptno
 
 
/* Formatted on 2013/4/8 14:32:13 (QP5 v5.114.809.3010) */
SELECT   sql_id, child_number, sql_text
  FROM   v$sql
 WHERE   sql_text LIKE '%WITH emps%' AND sql_text NOT LIKE '%v$sql%'



/* Formatted on 2013/4/8 14:32:34 (QP5 v5.114.809.3010) */
SELECT   *
  FROM   table (DBMS_XPLAN.display_cursor ('20fd557wv2gg9', 0, 'iostats'))

SELECT   *
  FROM   table (DBMS_XPLAN.display_cursor ('30byj2016bnuw', 0, 'all'))