EXPLAIN PLAN - what's it and how do we use it?

What does the EXPLAIN PLAN statement do in Oracle?

EXPLAIN PLAN statement when executed for a query displays the execution plan chosen by the Cost Based Oracle Otimizer. The execution plan of a SQL statement shows the sequence of instrcutions the Oracle Server will perform while executing that statement. This works for SELECT, INSERT, UPDATE, and DELETE all four statements. The beauty of EXPLAIN PLAN statement lies in the fact that it doesn't require the query statement to be run and this reduces the time taken for displaying the execution plan to a great extent, especially for long-running queries. This was a limitation with the AUTOTRACE statement and probably this is the reason why they have come up with the EXPLAIN PLAN statement as AUTOTRACE always requires the query to be run to completion (Remember TRACEONLY option of the AUTOTRACE command only suppresses the display of the result of the query, but the query is required to run to completion in that case as well).

DML or DDL?

EXPLAIN PLAN is a DML statement and hence you need to COMMIT the transaction explicitly as Oracle does not implicitly commit the changes made by DML statements.

What all you require to use it?

EXPLAIN PLAN statement requires you to have sufficient privileges like INSERT-privilege on the output table (in case you're specifying an output table to hold the execution plan... the default output table is PLAN_TABLE), EXECUTE-privilege to execute the SQL statement being used with the EXPLAIN PLAN, and if that SQL statement accesses any other Views/Tables (and if those Views/Tables subsequently access any other Vieews/Tables then for them as well... and so on) then you should have privileges to access all those Views/Tables involved directly or indirectly involved in the execution of the SQL statement for which EXPLAIN PLAN is being used.

Example: how do we use EXPLAIN PLAN?

EXPLAIN PLAN
FOR
SELECT * FROM TABLE_NAME1 T1, TABLE_NAME2 T2
WHERE T1.PK = T2.PK AND ... ;

The immediate output your SQL* Plus console will show is 'Explained.' and for viewing the execution plan generated by the EXPLAIN PLAN command, you can use utlxpls.sql (for parallel queries you should use utlxplp.sql).

What if the same plan table is used by many?

If the same plan table is being shared by many or if you are interested in maintaining the history of execution plans generated then you should use the STATEMENT_ID clause and this will attach the user specified ID with the particular execution plan. Now, you can easily retrieve all the plans by just mentioning the particular STATEMENT_IDs.

EXPLAIN PLAN
SET STATEMENT_ID = 'Statement Id #1'
INTO PLAN_TABLE
FOR
...SQL statement...;

EXPLAIN PLAN works fine for partitioned tables as well and in that case you can simply use the PARTITION_START, PARTITION_STOP, and PARTITION_ID columns of the PLAN_TABLE (if the default plan table is being used to capture the execution plan) to get the partitio-related information contained in the execution plan. Needless to mention that you always use pre-defined bind variables with the EXECUTION PLAN.

Note: This question was asked by one of our regular visitors, Amit. He has been very frequent in bringing up good questions. Keep it up Amit. Thanks for your contribution.