Oracle Database - Explain Plan

> Database > Oracle Database

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

Advertising

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

4 - 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
);

5 - 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

Advertising

6 - How to view the result

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

6.2 - 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
Advertising

7 - Documentation / Reference