-- 実行
SQL> SELECT /* the sql */ ename, dname 
FROM dept d join emp e USING (deptno);
 
-- sql_idとchild_numberの確認
SQL> select sql_id, child_number from v$sql
where sql_text like '%the sql%';
 
SQL_ID         CHILD_NUMBER
----------     -----------------------------
gwp663cqh5qbf   0
 
-- 実行計画の確認
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));
 
SELECT /* the sql */ ename, dname 
FROM dept d JOIN emp e USING (deptno);
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")
 
-- 一つのSQLで行う
SQL> SELECT t.*
  FROM v$sql s,
       table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
 WHERE sql_text LIKE '%the sql%';