EXPLAN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

· An ordering of the tables referenced by the statement

· An access method for each table mentioned in the statement

· A join method for tables affected by join operations in the statement

· Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

· Optimization, such as the cost and cardinality of each operation

· Partitioning, such as the set of accessed partitions

· Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

V$SQL_PLAN

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

Creating PLAN_TABLE

SQL> CONNECT ORTHO1648/ORTHO1648

Connected.

SQL> @%ORACLE_HOME%\RDBMS\ADMIN\UTLXPLAN.SQL

Table created.

Running EXPLAN_PLAN

SQL> EXPLAIN PLAN FOR SELECT * FROM G_USER_LANE_INT;

Explained.

Identifying Statement for EXPLAN PLAN

SQL> EXPLAIN PLAN SET STATEMENT_ID='BABU_E1' FOR SELECT * FROM G_USER_LANE_INT;

Explained.

PLAN_TABLE Output.

After you have explained the plan, use the following SQL scripts or PL/SQL package provided by Oracle to display the most recent plan table output:

· UTLXPLS.SQL

This script displays the plan table output for serial processing is an example of the plan table output when using the UTLXPLS.SQL script.

· UTLXPLP.SQL

This script displays the plan table output including parallel execution columns.

· DBMS_XPLAN.DISPLAY procedure

This procedure accepts options for displaying the plan table output. You can specify:

o A plan table name if you are using a table different than PLAN_TABLE

o A statement Id if you have set a statement Id with the EXPLAIN PLAN

o A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL.

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:
SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY ());

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));