To display the execution plan of all children associated with the SQL ID; you would have to call the table function in below format.
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID'));
- Following steps will load two children cursors in the cursor cache
SQL> SET LINESIZE 131
SQL> SET PAGESIZE 100
SQL> COLUMN sql_text FORMAT A100 WORD_WRAPPED
SQL> ALTER SESSION SET optimizer_mode = all_rows;
Session altered.
SQL> SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> ALTER SESSION SET optimizer_mode = first_rows;
Session altered.
SQL> SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
- By querying V$SQL, We can see that SQL_ID gfvh124qr5nzy has two childrens, 0 and 1;
SQL> SELECT sql_id, child_number, sql_text FROM v$sql 2 WHERE sql_text LIKE '%RAM_XPLAN_CURSOR_TEST%' 3 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ ---------------------------------------------------------------------- gfvh124qr5nzy 0 SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500 gfvh124qr5nzy 1 SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500
- Let us execute DBMS_XPLAN.DISPLAY_CURSOR with only SQL_ID as a parameter; it only prints the execution plan for the first child. This has been reported in unpublished bug 6501604
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('gfvh124qr5nzy'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID gfvh124qr5nzy, child number 0
-------------------------------------
SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 407 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 11 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7500)
19 rows selected.
- In order to display plans for all the childs, you will have to join V$SQL using SQL_ID and below is the suggested workaround.
SQL> SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
2 WHERE sql_text LIKE '%RAM_XPLAN_CURSOR_TEST%'
3 AND sql_text NOT LIKE '%v$sql%';
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID gfvh124qr5nzy, child number 0
-------------------------------------
SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 407 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 11 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7500)
SQL_ID gfvh124qr5nzy, child number 1
-------------------------------------
SELECT /* RAM_XPLAN_CURSOR_TEST */ * FROM scott.emp WHERE empno > 7500
Plan hash value: 169057108
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 407 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 11 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">7500)
38 rows selected.