SQL Engine - How to read a query plan ?

1 - About

How to read a query plan (ie a execution tree) ?

3 - Steps / Operation

Each row in the output table corresponds to a single step in the execution plan.

4 - Order Steps

The first step is the furthest indented to the right.

As it's a leaf operation (without children), this step accesses data.

Each step of the execution plan returns a set of rows that either is used by the next step. (A set of rows returned by a step is called a row set)

The next step is the parent of that line. It accepts then the rows from the first step (and for other steps on the same level).

If two lines are indented equally (they are then on the same level), then the top line is normally executed first.

The last step returned the data to the user or to the application issuing the SQL statement.

Each step of the execution plan either:

  • retrieves rows from the database
  • or accepts rows from one or more row sources as input.

5 - Id

The numbering of the step Ids reflects the order in which they are displayed in response to the EXPLAIN PLAN statement.

6 - Example Oracle

The step Ids with asterisks are listed in the Predicate Information section.


[email protected]>connect SCOTT/scott
Connected.
[email protected]>delete from plan_table;

0 rows deleted.

[email protected]>explain plan for
  2  select ename, dname, grade
  3    from emp, dept, salgrade
  4    where emp.deptno = dept.deptno
  5     and emp.sal between salgrade.losal and salgrade.hisal
  6  /

Explained.

[email protected]>@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
=============================================================================================
Plan hash value: 721498669

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    36 |     9  (23)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |     1 |    36 |     9  (23)| 00:00:01 |
|   2 |   MERGE JOIN                 |          |     1 |    23 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN                 |          |     5 |    50 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | SALGRADE |     5 |    50 |     3   (0)| 00:00:01 |
|*  5 |    FILTER                    |          |       |       |            |          |
|*  6 |     SORT JOIN                |          |    14 |   182 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    14 |   182 |     3   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   5 - filter("EMP"."SAL"<="SALGRADE"."HISAL")
   6 - access("EMP"."SAL">="SALGRADE"."LOSAL")
       filter("EMP"."SAL">="SALGRADE"."LOSAL")
   9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

24 rows selected.


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap