What is Oracle Explain Plan?

Oracle is one of the most popular relational database management systems famous for its flexibility and convenience of information management. When working with Oracle, it is important to keep a close eye on each process and modification in order to avoid future errors and unnecessary delays. Moreover, it is always better to review a plan before actually doing anything since your code might require some Query Optimization and Performance Tuning. Slow-running queries can cause poor database performance that directly impacts a user's experience.

When the EXPLAIN PLAN statement is executed, the output will contain the plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. An execution plan of a statement is the sequence of operations Oracle performs to run it.

In this tutorial, we will be talking about how to use the Oracle EXPLAIN PLAN statement. You will find not only a dry theoretical component of the issue but also illustrative examples of how to apply it in everyday work. This way, you are going to achieve two goals: get a better understanding of Oracle EXPLAIN PLAN and learn how to generate an explain plan in practice.

Difference between Explain Plan and Execution Plan

To avoid any possible confusion, let us clearly define EXPLAIN PLAN vs EXECUTION PLAN in Oracle. Both are just sets of instructions for SQL statement that tell the database how to access and combine data to produce the required output.

As mentioned earlier, EXPLAIN PLAN is basically what the Oracle optimizer is planning to do. However, the plan does not always work as expected. Sometimes you have to change and modify it on the fly so that it fits a particular situation. Here is when the EXECUTION PLAN comes in: it is an actual set of steps you end up taking. To sum up, EXPLAIN PLAN predicts how Oracle will process your query, and EXECUTION PLAN describes the steps it actually took.

Prerequisites

The EXPLAIN PLAN statement requires certain privileges:

• To insert rows into an existing output table
• To execute the SQL statement that you are determining the plan for
• To access any tables and views
• To access both the view and the underlying table if the view is based on another view that is based on a table

Moreover, you will need the privileges to query the output table in order to examine the results of the EXPLAIN PLAN statement.

EXPLAIN PLAN belongs to DML (data manipulation language) statements rather than to DDL (data definition language) statements. This means that the changes made by EXPLAIN PLAN are not committed implicitly. In case you would like to save the rows generated by EXPLAIN PLAN, you will need to commit the transaction containing the statement.

Plan Table

The output of the EXPLAIN PLAN statement is saved in a table that is usually called PLAN TABLE. It can be queried to determine the execution plan for statements later on. The table is automatically created as a global temporary table that is supposed to hold the output of EXPLAIN PLAN for all users. While a PLAN TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local table in your schema.

Syntax

For a better understanding of the Oracle EXPLAIN PLAN query, let us take a closer look at its syntax and what purposes it serves.

SET STATEMENT_ID

In case the output table contains rows from more than one execution plan, make sure to use the STATEMENT_ID value. If you skip this statement in the query, the default NULL value will be used automatically.

INTO table

By adding INTO table to your query, you can choose where to save the output. Before running the EXPLAIN PLAN query, make sure the table to hold its output already exists. There is no need to specify the schema if the said table is located within the schema you are currently working with.

Lastly, if you skip INTO altogether, Oracle will save the query results into the table named PLAN TABLE on your local database.

FOR statement

FOR allows you to specify a statement for which the plan is being generated. It can be SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, etc.

Examples of EXPLAIN PLAN for Oracle

Now, we suggest diving into the practical side of the matter by looking into some examples of EXPLAIN PLAN in Oracle. The statement below determines the execution plan and cost for an UPDATE statement. The execution results are added as new rows to the specified plan table with the STATEMENT_ID value of ' Price Change '.

EXPLAIN PLAN 
    SET STATEMENT_ID = 'Price Change' 
    INTO plan_table 
    FOR UPDATE FILM 
        SET REPLACEMENT_COST = REPLACEMENT_COST * 5
        WHERE FILM_ID =  
           (SELECT FILM_ID FROM FILM_CATEGORY 
               WHERE CATEGORY_ID = 3);
                                    

When you execute the statement above, the output is added to the plan_table.

The query results will not be displayed right away. In order to display the results of the query in the corresponding window of the interface, use the following query:

SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, 
object_name, position 
    FROM plan_table 
    START WITH id = 0 AND statement_id = 'Price Change'
    CONNECT BY PRIOR id = parent_id AND statement_id = 'Price Change';
                                    

On executing the script above, you will see the plan appear below the SQL window:

The value in the first row of the POSITION column says that the statement has a cost of 1.

Conclusion

When working with Oracle databases, it is crucial to optimize and speed up their performance to achieve a better user experience. This is when the EXPLAIN PLAN statement hits the stage. It allows you to see the estimated plan of a query and adjust it according to your goals. dbForge Studio for Oracle is the best choice when it comes to query optimization. Oracle GUI features provide powerful functionality wrapped into smooth management that can help automate and simplify your everyday routine.