Displaying Explain Plan

 I would like to warn you before you go into further, this article is not about “Explain the Explain Plan, it is about read further to know what this is about if the title does not make sense. The EXPLAIN PLAN statement displays execution plans chose by the Oracle optimized for SELECT, UPDATE, INSERT, and DELETE statement. A statement execution plan is the sequence of operations Oracle performs to return the statement. The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users.

Execution Plan is the best tool for DBA and developer to understand how their query is being executed.  This tool also helps you understand how the Oracle is returning the query result. Since you know, how oracle is processing your query, you can re-write to make it more efficient, faster without increasing the overheard. Remember Explain PLAN is a tool or command to display the execution plan. Don’t confuse explain plan with execution plan; they are not the same as we heard people using these terms interchangeably.

How do you display explain plan of any SQL query? With this article, I am going to demo two ways to display the explain plan.  

SQL Developer: There is an easy way to display the SQL execution plan in SQL Developer.

Highlight the SQL query, right click and click on Explain and then to Explain Plan. Also, you can use the hot key (F10) to display the execution plan.

SQL Code:  SQL Developer is the most popular IDE but this may not the case for all. What if you don’t use SQL developer? The below method is IDE independent, this even works at SQL plus also.

EXPLAIN PLAN FOR 

SELECT to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') FROM dual; 
SELECT plan_table_output 
FROM   TABLE(dbms_xplan.Display());