Oracle Database - Explain Plan

About

EXPLAIN PLAN is an Oracle 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

For this purpose, it will insert rows in a table. It's a DML statement

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).

Syntax

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.

PLAN_TABLE

The PLAN_TABLE is a table owned by PUBLIC. The creation script is in this location Oracle_Home/rdbms/admin/utlxplan.sql. utlxplan stands for UTiLity eXplain PLAN table.

create table PLAN_TABLE sharing=none (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(128),
        object_name        varchar2(128),
        object_alias       varchar2(261),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(128),
        other_xml          clob
);

Example

In the hr schema

explain plan 
set statement_id= 'myStatementId'
into HR.MY_PLAN_TABLE
for 
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM   hr.employees e, hr.departments d
WHERE  d.department_name IN ('Marketing', 'Sales')
AND    e.department_id = d.department_id;
Explained

The EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. It will insert rows in the PLAN_TABLE Therefore a COMMIT to preserve the data.

COMMIT;

In SQL Plus, you can add the explain plan statement before the last query in the SQL buffer with the help of zero.

0 explain for
run

will explain your last statement

How to view the result

From the default table

Once the sql is explained, you can view the execution plan from the default table (ie without specifying the into clause):

SELECT * FROM TABLE(dbms_xplan.display);
  • or with the utlxpls.sql in SQL Plus. utlxpls stands for UTiLity eXplain PLan Serial. It displays normal, serial (non-parallel) plans.
@?rdms\admin\utlxpls.sql
  • or with utlxplp.sql in SQL Plus. utlxplp stands for UTiLity eXplain PLan Parallel. It displays the contents of the plan table, including information specific to parallel-query plans.
@?rdms\admin\utlxplp.sql

They give the same answer.

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------

Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    19 |   722 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    19 |   722 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     2 |    32 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Note
-----
   - this is an adaptive plan

22 rows selected.

From the specified table or specified statement id

select * from TABLE(DBMS_XPLAN.DISPLAY('HR.MY_PLAN_TABLE','myStatementId','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    19 |   722 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    19 |   722 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     2 |    32 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / [email protected]$1
   4 - SEL$1 / [email protected]$1
   5 - SEL$1 / [email protected]$1

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
       "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25], "E"."SALARY"[NUMBER,22]
   2 - (#keys=0) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
       "E".ROWID[ROWID,10]
   3 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   4 - "E".ROWID[ROWID,10]
   5 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."SALARY"[NUMBER,22]

Note
-----
   - this is an adaptive plan

42 rows selected.
  • Or With a specific SQL
SELECT
  id,
  LPAD( ' ', 2 *( LEVEL - 1 ) )
  ||operation operation,
  OPTIONS,
  object_name,
  object_alias,
  qblock_name,
  position
FROM
  PLAN_TABLE
  START WITH id       = 0
AND statement_id      = 'myStatementId'
  CONNECT BY PRIOR id = parent_id
AND statement_id      = 'myStatementId'
ORDER BY
  id;
 ID OPERATION                      OPTIONS         OBJECT_NAME                    OBJECT_ALI QBLOCK_NAM   POSITION
--- ------------------------------ --------------- ------------------------------ ---------- ---------- ----------
  0 SELECT STATEMENT                                                                                     4
  1   HASH JOIN                                                                              SEL$1       1
  2     NESTED LOOPS                                                                         SEL$1       1
  3       NESTED LOOPS                                                                                   1
  4         STATISTICS COLLECTOR                                                                         1
  5           TABLE ACCESS         FULL            DEPARTMENTS                    [email protected]$1    SEL$1       1
  6         INDEX                  RANGE SCAN      EMP_DEPARTMENT_IX              [email protected]$1    SEL$1       2
  7       TABLE ACCESS             BY INDEX ROWID  EMPLOYEES                      [email protected]$1    SEL$1       2
  8     TABLE ACCESS               FULL            EMPLOYEES                      [email protected]$1    SEL$1       2

For info: SQL Plus formatting

set linesize 140
column id format 99
column operation format a30
column options format a15
column object_alias format a10
column QBLOCK_NAME format a10

Documentation / Reference


Powered by ComboStrap