How To Display Execution Plan in Oracle

We can use table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.

Run a Query with a distinctive comment.in this example /* TOTO */

SELECT /* TOTO */ ename, dname 
FROM dept d join emp e USING (deptno);

Get sql_id and child_number for the preceding statement:

SELECT sql_id, child_number
FROM v$sql 
WHERE sql_text LIKE '%TOTO%';

SQL_ID         CHILD_NUMBER
----------     -----------------------------
gwp663cqh5qbf   0

Display the execution plan for the cursor:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ 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")

Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:

Display the execution plan of all cursors matching the string 'TOTO':

SELECT t.* FROM v$sql s,table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) tWHERE sql_text LIKE '%TOTO%';

The arguments accepted by DBMS_XPLAN.DISPLAY_CURSOR are:
• SQL ID (default null, means the last SQL statement executed in this session),
• child number (default 0),
• format, controls the amount of information displayed (default 'TYPICAL')
Besides the privileges to actually run the SQL statement, the executing user needs SELECT privilege

on V$SQL_PLAN, V$SQL_PLAN_DETAIL and SELECT_CATALOG_ROLE.