Oracle Database - Explain Plan

Card Puncher Data Processing

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 / D@SEL$1
   4 - SEL$1 / E@SEL$1
   5 - SEL$1 / E@SEL$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                    D@SEL$1    SEL$1       1
  6         INDEX                  RANGE SCAN      EMP_DEPARTMENT_IX              E@SEL$1    SEL$1       2
  7       TABLE ACCESS             BY INDEX ROWID  EMPLOYEES                      E@SEL$1    SEL$1       2
  8     TABLE ACCESS               FULL            EMPLOYEES                      E@SEL$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





Discover More
Card Puncher Data Processing
Glossary

(CREATE, ALTER, DROP) (GRANT, REVOKE) (SELECT, UPDATE, INSERT, DELETE) (COMMIT, ROLLBACK) NLS (National Language Support) UDML is the abbreviation for Universal Database Markup Language ...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - (Actual|Final) Execution Plan

The actual or final plan is the execution that was executed in order to retrieve the result of a SQL. optimizerquery planrow source generator V view contains the actual plan for a query that...
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - DBMS_XPLAN

Dbms_xplan is a method for: querying and displaying the explain plan output. display the plan of a statement stored: in the Automatic Workload Repository (AWR) or in a SQL tuning set. It...
Card Puncher Data Processing
Oracle Database - EXPLAIN PLAN and Autotrace Comparison

First, I create a partition table and I generate random data with the DBMS_RANDOM package (). We see with : that a full table scan will be performed in the table t the cost for each step (3) ...
Card Puncher Data Processing
Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Joins are data operation / method / algorithm in order to match data from more than one table. They all require sorting and then matching aspects of a dataset. There are only three join mechanisms used...
Card Puncher Data Processing
Oracle Database - Rowid Scans operation

The rowidscan is a access path used by the query optimizer to produce the best explain plan. The rowid of a row specifies the datafile and data block containing the row and the location of the row in...
Card Puncher Data Processing
Oracle Database - Sample SQL Clause for Table Scans/Access

A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views. This access path is used when a statement's FROM...
Card Puncher Data Processing
Oracle Partition - Hash partitioning

Sometimes it may not be possible to define the ranges of—or a set of known values for—each partition. To create the CUST example table with four hash partitions: A typical example is a table,...



Share this page:
Follow us:
Task Runner