Create Plan Table[utlxplan.sql script]

EXPLAIN PLAN

All SQL tuning experts must be

proficient in reading Oracle execution plans and
understand the steps within explain plans and
the sequence in which the steps are executed.

To successfully understand an explain plan you must know the order that the plan steps are executed.
Reading an explain plan is important Oracle SQL tuning DBAs reveal the explain plans to check many things:

Ensure that the tables will be joined in optimal order.
Determine the most restrictive indexes to fetch the rows.
Determine the best internal join method to use (e.g. nested loops, hash join).
Determine that the SQL is executing the steps in the optimal order.

Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed.

Create Plan Table

SQL*Plus Release 3.3.4.0.0 - Production on Mon Oct 18

We create the PLAN TABLE by running the utlxplan.sql script, which always exists in the $ORACLE_HOME/rdbms/admin directory.

SQL > delete from plan_table;

Once the PLAN TABLE has been created, we must populate it with access path information. This is done by running EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR

set pages 9999;

At this point, the SQL optimizer has only been directed to compute the access place and place it in the PLAN TABLE. To view the contents of the PLAN TABLE, we need to execute a snippet of SQL, plan.sql.

SQL>
SQL> @plan

The EXPLAIN PLAN output, if read correctly, reveals vital information. In the case above, two index range scans using the named indexes are run. Next and equi-join (the AND-EQUAL) merges the results of the index range scans. This creates a list of index ROWID's that are passed to a full-table scan of the MON_RTAB_STATS table. Finally, the SORT statement directs Oracle to sort the result set, either in-memory or in the TEMP tablespace.