Saturday, September 4, 2010

How the Explain Plan Works?

Whenever any read or write data in Oracle, it issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan.

An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.

The general syntax of EXPLAIN PLAN is:
explain plan for your-precious-sql-statement;

If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:
explain plan into table_name for your-precious-sql-statement;
If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN).

UTLXPLAN.SQL located under $ORACLE_HOME\RDBMS\ADMIN.