How do you get the explain plan?

Question

Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.

Answer 1

You can use the package DBMS_XPLAN:

SQL> explain plan for select * from dual;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     2 |     2 |
|   1 |  TABLE ACCESS FULL   | DUAL        |     1 |     2 |     2 |
--------------------------------------------------------------------
Note: cpu costing is off

Answer 2

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

EXPLIAIN PLAN FOR ...

Replace the ... with your SQL Statement. After running this run the following to view the plan:

SELECT * FROM table(dbms_xplan.display);

For more information see the Overview or Usage sections in the Oracle 11g Performance Tuning Guide.