Get the Execution Plan from/using V$SQL_PLAN

V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. V$SQL_PLAN provides a way to examine the execution plan for cursors that were recently executed. Information in V$SQL_PLAN is very similar to the output of an EXPLAIN PLAN statement. It contains the execution plan of every cursor in the library cache (including child).  The ADDRESS, HASH_VALUE, and CHILD_NUMBER columns can be used to join with V$SQL to add the child cursor–specific information.

The information in this view is very similar to the information in PLAN_TABLE. However, V$SQL_PLAN contains the actual plan used. The execution plan obtained by the EXPLAIN PLAN statement can be different from the execution plan used to execute the cursor. This is because the cursor might have been compiled with different values of session parameters or bind variables.

V$SQL_PLAN shows the plan for a cursor rather than for all cursors associated with a SQL statement. The difference is that a SQL statement can have more than one cursor associated with it, with each cursor further identified by a CHILD_NUMBER. For example, the same statement executed by different users has different cursors associated with it if the object that is referenced is in a different schema. Similarly, different hints can cause different cursors. The V$SQL_PLAN table can be used to see the different plans for different child cursors of the same statement. 

You can query V$SQL_PLAN using the DBMS_XPLAN.DISPLAY_CURSOR() function to display the current or last executed statement (as shown in the example). You can pass the value of SQL_ID for the statement as a parameter to obtain the execution plan for a given statement. SQL_ID is the SQL_ID of the SQL statement in the cursor cache. 

You can retrieve the appropriate value by querying the SQL_ID column in V$SQL or V$SQLAREA. Alternatively, you could select the PREV_SQL_ID column for a specific session out of V$SESSION. This parameter defaults to null in which case the plan of the last cursor executed by the session is displayed. 

To obtain 47ju6102uvq5q SQL_ID execution plan, execute the following query:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('47ju6102uvq5q'));