SQL Engine - How to read a query plan ?

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > RDBMS - Sql Processing (Engine)

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

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.

gerardnico@orcl>CONNECT SCOTT/scott
Connected.
scott@orcl>DELETE FROM plan_table;
 
0 rows deleted.
 
scott@orcl>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.
 
scott@orcl>@?/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.