Oracle - How to Retrieve Execution Plan?

SQL Optimizer in Oracle generates multiple plans for a SQL statement and chooses the one with the lowest cost.

1. A Simple Way

If you have the SQL statement at hand, you can simply execute the following commands to retrieve the execution plan.
Here I use "SELECT * FROM EMP" script as an example.


EXPLAIN PLAN FOR ( SELECT * FROM EMP );

SELECT PLAN_TABLE_OUTPUT FROM TABLE( DBMS_XPLAN.DISPLAY );

2. Retrieve Execution Plan Using SQL_ID

2.1 Get SQL_ID

Basically using the SQL script shown as below can fetch the SQL_ID of the intended SQL statement. MODULE column will help you identify who issued the statement.

SELECT MODULE, SQL_ID, CHILD_NUMBER, LAST_LOAD_TIME, SQL_TEXT
FROM V$SQL
WHERE SQL_FULLTEXT LIKE '%FROM EMP%'
ORDER BY MODULE, LAST_LOAD_TIME DESC ;

There are some points that you may need to pay attention to.

• If your have a lengthy SQL statement, it is recommended to use SQL_FULLTEXT instead of SQL_TEXT in WHERE clause.
• If you are using an Oracle Real Application Cluster, you may want to use GV$SQL rather than V$SQL, which retrieves information from all eligible instances. 
• LAST_LOAD_TIME help you identify the latest time when the query plan was loaded into the library cache.

2.2 Retrieve Execution Plan

After you get the proper SQL_ID, you can retrieve the Execution Plan via the following script. The 2nd parameter is CHILD_NUMBER, please make sure you have the right number as well.

SELECT PLAN_TABLE_OUTPUT 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4ttqgu8uu8fus', 0, 'TYPICAL'));

 
As you might have noticed, TYPICAL is specified as the 3rd parameter in this example. It indicates what level information to be retrieved, as listed below.

• BASIC
• TYPICAL
• SERIAL
• ALL

To use it properly, you may need to set a corresponding statistical level, which can be done by the following commands.
Check statistics level: SHOW PARAMETER STATISTICS_LEVEL
Change statistics level: ALTER SESSION SET STATISTICS_LEVEL