Reading explain plan of a SQL query...

Well, some information about how to go about generating and reading explain plan of a SQL query. Explain plan gives 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 also 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 for 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 where explain plan will store the query plan. To create this table run $ORACLE_HOME/rdbms/admin/utlxplan.sql [Run it from sys, then grant all on PLAN_TABLE to public, so that everybody can use the same table]

Now we are ready to use it. Well, lets use a query based on emp and deptno tables.

select * from emp,dept
where 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> @utlxpls

PLAN_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 optimization

17 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... ;)