Well, some information about how to go about generating and reading explain plan of a SQL query. Explain plan gives the information about the access path that Oracle is going to follow to execute your query [explain plan doesn't actually execute the query, it just tells about the access path that Oracle will follow to execute the query; that too in current session, with all the current settings, it may be totally different in another session on the very same database].

So after reading the explain plan of a particular query, we can see that is it executing efficiently ? Are the indexes being used ? and so on. Let us go through the basic setup required to use explain plan.

First of all we need to create a table called PLAN_TABLE (It is the default name used by Oracle, you can use any other name also. But then, you would need to mention the name every time you run the explain plan stuff) where explain plan will store the details of the query plan.

To create this table, run the script provided by Oracle $ORACLE_HOME/rdbms/admin/utlxplan.sql

I would like to mention the Tom Kyte approach here. In his book Effective Oracle by design, Tom suggests to create plan_table as a GLOBAL TEMPORARY TABLE with the ON COMMIT PRESERVE ROWS option and then granting all privileges on PLAN_TABLE to public. Also create a public synonym, so that everyone can use the same table.

Now when the plan table has been created, we are ready to go. Lets do EXPLAIN PLAN of a query:

For the first time, we are going to use the simplest “Hello World” query: select * from emp; So lets see…

SQL> explain plan for2  select * from emp;Explained.

This is the way to run EXPLAIN PLAN for some query. I simply write explain plan for and then the query itself. Now this Explained means Oracle has stored all the details n PLAN_TABLE and we have to read that data to see the details.

Again to view those details, Oracle provides a script called $ORACLE_HOME/rdbms/admin/utlxpls.sql. For your convenience you can ed in your session and create this script over there itself as I did with the name explain.

So its the time to see that explain plan which we have just run. As I said I run the script explain:

select * from emp;

SQL> @explainPLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.SQL>
Now we are ready to use it. Well, lets use a query based on emp and deptno tables.
select * from emp,deptwhere emp.deptno=dept.deptno;
To see explain plan of this query we write
SQL> explain plan set statement_id='q1' for  2  select * from emp,dept  3  where emp.deptno=dept.deptno;Explained.SQL>
This is the syntax for using explain plan. set statement_id we use to store multiple plans in the plan table. Then we write for and the SQL query we want to see explain plan for. SQL Plus will prompt “Explained” and we are back to SQL prompt. Now the plan has been stored in PLAN_TABLE table and we need to read it. For that Oracle provides us with a script called utlxpls.sql (in $ORACLE_HOME/rdbms/admin/). [I generally create the same script in bin folder, so that to run it I can simply write @utlxpls]
SQL> @utlxplsPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |----------------------------------------------------------------------------|   0 | SELECT STATEMENT             |             |       |       |       ||   1 |  NESTED LOOPS                |             |       |       |       ||   2 |   TABLE ACCESS FULL          | EMP         |       |       |       ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |       |       |       ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |       |       |       |----------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note: rule based optimization17 rows selected.SQL>
These are the details that explain plan has provided us. Now there comes interpretation of this information. First of all to figure out what happens first, second and so on ? Well this is shown by the indentation of various steps in plan_table_output. The rightmost step (the most indented one) is executed first and then the 2nd most indented and so on. So in our case step with id 4 is executed first, then 2 and 3, and then 1. In predicate information we can see that…….
to be continued…