How can we get the actual execution plan of a query that is in the SQL area?
We have several options to get the execution plan of a query, but it doesn’t give us the real execution plan from the shared pool, but recalculate the execution plan for us.
So, how can we see the real execution plan?

In 10g, Oracle introduced a new function in dbms_xplan that can read the in-memory execution plan (v$sql_plan and associated structures), the display_cursor function.

This is how this function is used:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

The function takes three optional parameters:

SQL_ID – Specifies the SQL_ID of the SQL statement in the shared pool.
child_number – Child number of the cursor to display. The child_number can be specified only if SQL_ID was specified.
formatting string – Controls the level of details for the plan (for example: basic, typical, serial and all).

Examples:

Displaying the execution plan of the last executed statement for that session:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

Displaying the execution plan for a query in the shared pool, get the SQL_ID of the query:

Extract its execution plan:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>'));