What is Explain Plan in Oracle, & how do we use it?

An Explain Plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster.
Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed. This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. The command is of the form:

EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement;

You need to use a statement_id and then give your SQL statement. For instance, suppose I have a query to tune. How does it get executed? I issue the following in SQL*Plus:

SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained.

I used 'q1' for my statement id (short for query 1). But you can use anything you want. My SQL statement is the second line. Now I query the PLAN_TABLE to see how this statement is executed. This is done with the following query:

SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = TABLE ACCESS FULL TEST

This tells me that my SQL statement will perform a FULL table scan on the TEST table (TABLE ACCESS FULL TEST). Now let's add an index on that table!

SQL> create index test_name_idx on test(object_name); Index created. SQL> truncate table plan_table; Table truncated. SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained. SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = INDEX RANGE SCAN TEST_NAME_IDX

I added an index to the table. Before I issue another EXPLAIN PLAN, I truncate the contents of my PLAN_TABLE to prepare for the new plan. Then I query the PLAN TABLE. Notice that this time I'm using an index (TEST_NAME_IDX) that I created!! Hopefully, this query will run faster now that it has an index to use. But this may not always be the case.