Oracle Database - Explain Plan

EXPLAIN PLAN is the SQL Command that tell you what the query plan for a given SQL would be :

• if executed it right now
• in the current session
• with the current settings

EXPLAIN PLAN cannot tell you what plan was actually used to run a given query in the past because the query could have taken place in a session with very different settings (Example : Sort area size).

• Oracle Database - EXPLAIN PLAN and Autotrace Comparison

It involves several script in $ORACLE_HOME/rdbms/admin :

• utlxplan.sql (for UTiLity eXplain PLAN table), which contains a CREATE TABLE statement for a table named PLAN_TABLE. This is the table in which EXPLAIN PLAN places the query plan.
• utlxplp.sql (for UTiLity eXplain PLan Parallel), which displays the contents of the plan table, including information specific to parallel-query plans.
• utlxpls.sql (for UTiLity eXplain PLan Serial), which displays the contents of the plan table for normal, serial (non-parallel) plans.

To set up EXPLAIN PLAN, first create the plan table itself.

gerardnico@orcl>@?/rdbms/admin/utlxplan

Table created.

gerardnico@orcl>desc plan_table

If you want to make EXPLAIN PLAN available to the world out of the box, without set up perform this statements.

The format of the EXPLAIN PLAN command is :

EXPLAIN plan
  [SET statement_id= 'text']
  [INTO [owner.]table_name]
FOR statement;

• The text in bracket is optionnal.
• The statement_id allows you to store mutliple plans in the plan table
• The owner.table_name allow you to use another table than PLAN_TABLE.

• Interpreting Explain plan