What is execution plan?
An execution plan describes the list of operations which are performed by SQL Engine for SQL statement. The order of operations and their implementation is decided by the oracle query optimizer. An execution plan also shows the estimated number of rows, cost require to perform the operation, how many bytes oracle will read the data and estimated time required for the operations.
Sample Execution Plan:
Displaying Execution plan:
Below are the two mostly used methods to display an execution plan.
- EXPLAIN PLAN command: This displays an execution plan for SQL statement without actually executing it.
- V$SQL_PLAN: This dynamic performance view shows execution plan for statement that has been compiled into cursor and stored in the cursor cache.
DBMS_XPLAN Package: This package provides the different PL/SQL interfaces to display the plan from different sources.
- Obtaining Execution Plan from EXPLAIN PLAN Command and DBMS_XPLAN.DISPLAY function:
Below example shows how to use EXLAIN_PLAN command and DBMS_XPLAN.DISPLAY function to display the plan.
- Obtaining Plan from DBMS_XPLAN.DISPLAY_CURSOR function:
Execution plan for executed statement can be displayed by using DBMS_XPLAN.DISPLAY_CURSOR function. It takes input as SQL_ID of the statement.
- Obtaining History Plan from DBMS_XPLAN.DISPLAY_AWR function
Sometimes you will not get plan from DISPLAY_CURSOR function as it might have flushed from the cursor cache. In this case we can obtain the plan using DISPLAY_AWR function. This function gives us all the execution plans that the statement had used in the past. It takes input as SQL_ID of the statement.
Conclusion: In this article we have seen the methods to obtain an execution plan from the cursor cache as well as from AWR. I will write an article on interpreting these execution plans very soon.
For more information read