3.2 Reading DB2 Execution Plans

DB2 uses multiple approaches to generate and display execution plans. You use SQL to place plan data into a table, after which you can view the data by several means. These are the primary methods that IBM itself describes in its documentation:


Visual Explain

Visual Explain requires a client installation on your workstation and is not available on all supported platforms. For that reason, I've never used it; I prefer a tool that I can always count on being readily accessible.


The db2exfmt tool

This tool runs from the command line in any environment, including nongraphical environments, so you can count on it being available. However, I find that it tells me far more than I want to know, making it hard to find the forest for the trees, so to speak. For example, it produced a 1,216-line report for an execution plan of a simple four-way join. Even the portion of the report that shows the big picture is hard to use. It displays the execution plan tree in an ASCII text layout that mimics a graphical picture of the tree structure, but it requires far more line-width than you can easily view for all but the simplest execution plans.


Handwritten queries against the plan-data tables

This approach works best for me, so I describe it in this section in detail. If you already know how to answer the basic questions about an execution plan (e.g., the join order, the join methods, and the table-access methods) using the other tools, you probably don't need this section and can function well with the method you already know.

3.2.1 Prerequisites

DB2 places execution-plan data into the following seven tables:

  • EXPLAIN_INSTANCE

  • EXPLAIN_STREAM

  • EXPLAIN_OBJECT

  • EXPLAIN_ARGUMENT

  • EXPLAIN_OPERATOR

  • EXPLAIN_PREDICATE

  • EXPLAIN_STATEMENT

To create these tables, run the EXPLAIN.DDL script located in the misc subdirectory under the sqllib directory, while connected to the schema in which you need these tables. From the misc directory, connect and change to the schema that belongs to the user you will use when generating execution plans. From the Unix prompt, you then execute the command:

db2 -tf EXPLAIN.DDL

DB2's plan tables contain a hierarchy of data about each execution plan stored, with EXPLAIN_INSTANCE at the top of the hierarchy with one row per execution plan. When you delete an EXPLAIN_INSTANCE row, the delete cascades to remove details for that execution plan from the other tables as well. Normally, your execution plans end up in these tables in the schema that belongs to the end user you logged on as. For example, you might have connected with this command:

CONNECT TO Server_Name USER User_Name USING SomePassword;

In this case, you likely set your schema to the schema that contains the application data, so you could run and explain queries against that data:

SET SCHEMA Appl_Schema;

However, this latter step has no effect on where execution plans you generate will end up; they still go to EXPLAIN_ tables in the User_Name schema.

3.2.2 The Underlying Process of Displaying Execution Plans

You use a four-step process from the DB2 command-line interpreter to generate and display execution plans with the least interference to other end users who might also be using the plan table:

  1. Delete all rows from the top-level execution-plan table EXPLAIN_INSTANCE in the schema you are using to store the execution plans, usually the schema belonging to the user you logged in as. The DELETE from the EXPLAIN_INSTANCE table automatically cascades to clean up the execution plan data in the other six tables as well.

  2. Generate the execution-plan records with the SQL statement EXPLAIN PLAN FOR <Statement_To_Be_Tuned>;.

  3. Display the execution plan with a statement by any of several means that DB2 provides, as I described in the earlier, just under the heading Section 3.2.

  4. Clean up your work with ROLLBACK;.

I'll demonstrate this process to show the execution plan for a simple query:

SELECT Last_Name, First_Name, Salary FROM Employees
WHERE Manager_ID=137
ORDER BY Last_Name, First_Name;

Here is the actual content of a DB2 session to manually determine the execution plan of this query, with generic passwords and names:

$ db2 +c -t
(c) Copyright IBM Corporation 1993,1997
Command Line Processor for DB2 SDK 5.2.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => CONNECT TO Server_Name USER User_Name USING SomePassword;

   Database Connection Information

 Database server        = DB2/SUN 5.2.0
 SQL authorization ID   = USER_NAME
 Local database alias   = SERVER_NAME

db2 => SET SCHEMA Appl_Schema;
DB20000I  The SQL command completed successfully. 
db2 => DELETE FROM USER_NAME.EXPLAIN_INSTANCE;
DB20000I  The SQL command completed successfully.
db2 => EXPLAIN PLAN FOR SELECT Last_Name, First_Name, Salary FROM Employees
db2 (cont.) => WHERE Manager_ID=137
db2 (cont.) => ORDER BY Last_Name, First_Name;
DB20000I  The SQL command completed successfully.
db2 => SELECT O.Operator_ID, S2.Target_ID, O.Operator_Type,
db2 (cont.) =>        S.Object_Name, CAST(O.Total_Cost AS INTEGER) Cost
db2 (cont.) => FROM USER_NAME.EXPLAIN_OPERATOR O
db2 (cont.) =>      LEFT OUTER JOIN USER_NAME.EXPLAIN_STREAM S2
db2 (cont.) =>                      ON O.Operator_ID=S2.Source_ID
db2 (cont.) =>      LEFT OUTER JOIN USER_NAME.EXPLAIN_STREAM S
db2 (cont.) =>                      ON O.Operator_ID = S.Target_ID
db2 (cont.) =>                     AND O.Explain_Time = S.Explain_Time
db2 (cont.) =>                     AND S.Object_Name IS NOT NULL
db2 (cont.) => ORDER BY O.Explain_Time ASC, Operator_ID ASC;
   
OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
          1         - RETURN        -                          186
          2         1 TBSCAN        -                          186
          3         2 SORT          -                          186
          4         3 FETCH         EMPLOYEES                  186
          5         4 IXSCAN        EMP_MGR_ID                  25

  5 record(s) selected.

db2 => ROLLBACK;
DB20000I  The SQL command completed successfully.
db2 =>

This shows an execution plan that finds the index range (on the index Emp_Mgr_ID) that covers employees who report to the manager with ID 137. That index range scan delivers a list of rowids that point to specific rows in specific blocks of the Employees table. For each of those rowids, DB2 performs logical I/O and, if necessary, physical I/O to the necessary table block, where it finds the specific row indicated. Following the table reads, DB2 sorts the rows in ascending order into a temporary table, based on the indicated ORDER BY columns. Finally, it scans the temporary table that contains the sorted result.

This form of query shows steps labeled by OPERATOR_ID and allows tracing of a tree-like plan through the column TARGET_ID. TARGET_ID points to the step that is a parent of the step shown. In the example, each parent has a single child, but many potential steps, such as nested-loops steps, are parents to a pair of later steps. You can use TARGET_ID to lay the steps out in a tree structure that corresponds to the execution plan. DB2's other methods for showing execution plans show this same tree structure directly, though it is hard to see all at once on your screen.

The same sort of tree structure is reflected in the indentation of the execution plans from the earlier query I showed to illustrate Oracle execution plans, but that query uses CONNECT BY, a feature lacking in DB2. SQL Server also uses indentation to show the tree structure of the underlying execution plan, in plans shown with SHOWPLAN_TEXT, described later.

3.2.3 The Practical Process of Displaying Execution Plans

To a beginner, the process for displaying DB2 execution plans looks clumsy, I know, but you can automate the underlying steps with a little simple scripting. If you are working from Unix, create the following files:

-- File called head.sql
DELETE FROM User_Name.EXPLAIN_INSTANCE;
EXPLAIN PLAN FOR

-- File called tail.sql
SELECT O.Operator_ID, S2.Target_ID, O.Operator_Type,
       S.Object_Name, CAST(O.Total_Cost AS INTEGER) Cost
FROM User_Name.EXPLAIN_OPERATOR O
     LEFT OUTER JOIN User_Name.EXPLAIN_STREAM S2
                     ON O.Operator_ID=S2.Source_ID
     LEFT OUTER JOIN User_Name.EXPLAIN_STREAM S
                     ON O.Operator_ID = S.Target_ID
                    AND O.Explain_Time = S.Explain_Time
                    AND S.Object_Name IS NOT NULL
ORDER BY O.Explain_Time ASC, Operator_ID ASC;
ROLLBACK;

With the aid of head.sql and tail.sql, the practical process of displaying execution plans, after you have chosen the execution plan you want (see Chapter 5-Chapter 7), becomes:

  1. Place the bare SQL to be analyzed into tmp.sql, in the same directory as head.sql and tail.sql.

  2. From a DB2 session started in that same directory, after running quit; to reach the shell prompt, run cat head.sql tmp.sql tail.sql | db2 +c +p -t from the shell prompt.

  3. Tweak the database (for example, with index changes) and the SQL to be tuned in tmp.sql (following the methods of Chapter 4) and repeat the previous step from the shell prompt until you have the execution plan you want. Then, save the corrected result in a permanent location.

Begin by editing a copy of the SQL in question (complete with terminating semicolon) in tmp.sql, using the editor of your choice, in one window. In another window, start a DB2 session from the directory that holds head.sql, tail.sql, and tmp.sql. Next, exit the db2 command-line processor with quit, but stay at the shell prompt. Generate and view new execution plans for the current version of tmp.sql (after you save it!) with the following command:

cat head.sql tmp.sql tail.sql | db2 +c +p -t

Use your favorite shell shortcut to repeat this command as needed. With this process, it takes just seconds to make a change and see the results. If you need to print the execution plan or to view it with an editor, you can redirect the output:

cat head.sql tmp.sql tail.sql | db2 +c +p -t > tmp.out

In operating systems other than Unix, you can try similar tricks or you can always just add the contents of head.sql to the top of tmp.sql, add the contents of tail.sql to the bottom, and run the whole script at one time, an approach that works in any operating system. Here is an example of the process in action, with the same query I explained earlier, beginning with the quit command to reach the shell prompt:

db2 => quit; 
DB20000I  The QUIT command completed successfully.
$ cat head.sql tmp.sql tail.sql | db2 +c +p -t
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
          1         - RETURN        -                          186
          2         1 TBSCAN        -                          186
          3         2 SORT          -                          186
          4         3 FETCH         EMPLOYEES                  186
          5         4 IXSCAN        EMP_MGR_ID                  25

  5 record(s) selected.

DB20000I  The SQL command completed successfully.
$

In practice, about half the changes you will make to force the execution plan you want will be to tmp.sql, and the other half will be to the environment, through the db2 command-line interface, with operations such as creating and dropping indexes, generating table and index statistics, or modifying session optimization parameters.

3.2.4 Robust Execution Plans

When tuning SQL, you'll usually want to verify that you are getting simple execution plans that drive through nested loops in the correct join order. I refer to these execution plans as robust, because they tend to scale well to high data volumes. Here's an example that generates a robust plan, to better understand the process, with the following SQL statement to be tuned, placed in tmp.sql:

-- File called tmp.sql
SELECT E.First_Name, E.Last_Name, E.Salary, LE.Description,
       M.First_Name, M.Last_Name, LM.Description
FROM Employees E 
     INNER JOIN Locations LE ON E.Location_ID=LE.Location_ID
     INNER JOIN Employees M ON E.Manager_ID=M.Employee_ID
     INNER JOIN Locations LM ON M.Location_ID=LM.Location_ID
WHERE E.Last_Name = ? 
  AND UCASE(LE.Description) = ? ;

To demonstrate this SQL on a realistic case, I populated the Employees table with 100,000 rows, having 10,000 different values for Last_Name. I populated the Locations table with 1,000 rows. I quit to the shell prompt after connecting to DB2 in the directory with tmp.sql, head.sql, and tail.sql. I executed cat head.sql tmp.sql tail.sql | db2 +c +p -t from the shell prompt and produced the following output, with indexes only on the primary keys and on Employees(Last_Name):

$ cat head.sql tmp.sql tail.sql | db2 +c +p -t
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
          1         - RETURN        -                          305
          2         1 NLJOIN        -                          305
          3         2 NLJOIN        -                          285
          4         3 NLJOIN        -                          260
          5         4 FETCH         EMPLOYEES                   80
          6         5 IXSCAN        EMP_LAST_NAME               50
          7         4 FETCH         LOCATIONS                   50
          8         7 IXSCAN        LOCATION_PKEY               25
          9         3 FETCH         EMPLOYEES                   75
         10         9 IXSCAN        EMPLOYEE_PKEY               50
         11         2 FETCH         LOCATIONS                   50
         12        11 IXSCAN        LOCATION_PKEY               25

  12 record(s) selected.  

DB20000I  The SQL command completed successfully.
$
3.2.4.1 How to interpret the plan

Here is how you read the execution plan output:

  • All joins are nested loops, based on the series of rows that state NLJOIN. If you have a mix of join methods, the first join executed will be the last one listed. You read the order of join methods executed from the bottom up.

  • The order of table access is Employees, Locations, Employees, Locationsthe same order they appear in the execution plan output. When SQL references the same tables multiple times, aliases for those tables are mandatory. As you can see in the example FROM clause, the Employees table is aliased to both E and M. You can tell from the index choices that alias E, rather than alias M, represents the driving table, even though both aliases map to the same Employees table. It is less obvious which alias that maps to Locations the database reaches first, but it must be LE, since only that alias is reachable second in the join order.

  • All four table reads are through some index, as shown by the OPERATOR_TYPE FETCH in front of each table name. The indexes used come in the OPERATOR_TYPE IXSCAN entries just below each table access. Thus, you know that the driving table E is reached through an index scan (a read that potentially touches multiple rows at a time) on the index EMP_LAST_NAME. The rest of the table accesses are unique reads since they use equality conditions on the tables' primary keys. Since all reads after the driving table are for unique joins, you know that the query will read at most the same number of rows for each of these other tables as it reads for the driving table.

For this example, I contrived index names that make clear which indexed column provides the table access, but indexes are often much more cryptically named than this. If it is not completely clear which column or columns are included in the index used, do not guesscheck! One of the most common pitfalls in tuning is to assume that the index range scan you wanted is the index range scan you got!


If you do not already know the indexes for a table, you don't know how they are named for each combination of columns, and the index names do not resolve the question. Always check in case the index range scan is not the one you expected. The simplest script to provide this check is as follows:

-- File called inddb2.sql
SELECT IndName, ColNames
FROM SYSCAT.INDEXES
WHERE TabName = UCASE('EMPLOYEES');

From DB2, logged into the schema that holds the table you need to check, edit the script to reference the table you want to investigate and run db2 -tf inddb2.sql from the shell prompt. The script lists multicolumn indexes in order, first column first, on a single line, separated by + signs. Here is an example of the use of this script:

$ db2 -tf inddb2.sql

INDNAME            COLNAMES
------------------ ---------------------
EMP_MGR_ID         +MANAGER_ID
EMPLOYEE_PKEY      +EMPLOYEE_ID
EMP_LOCATION_ID    +LOCATION_ID
EMP_DEPARTMENT_ID  +DEPARTMENT_ID   
EMP_HIRE_DATE      +HIRE_DATE
EMP_LAST_NAME      +LAST_NAME
EMP_NICKNAME       +NICKNAME
EMP_FIRST_NAME     +FIRST_NAME

  8 record(s) selected.
3.2.4.2 Narrative interpretation of the execution plan

I just explained how to find the join order, the join methods, and the table-access methods for the robust execution plan I showed earlier. If you combine that with the basics covered in Chapter 2, you should understand how DB2 will reach the data, from end to end. To test your understanding, try constructing a narrative that explains the full execution plan in English, as a set of instructions to the database. Compare your result with what follows. If it does not match well, try again later, after you have read a few more execution plans, to see if your understanding has improved. Here is the execution plan expressed in narrative form, as instructions to the database:

  1. Using the condition E.Last_Name = ?, go to the index EMP_LAST_NAME and find the list of rowids that correspond to employees with the requested last name.

  2. For each of these rowids, go to the table Employees (E) with a single-block read (logical read, physical when necessary) according to each rowid from the previous step, using the block-address part of the rowid. Using the row-address part of the rowid, find the specific row that the rowid points to and read all necessary data (requested data for alias E) from that row.

  3. For each such row, using the join condition E.Location_ID=LE.Location_ID, go to the primary-key index LOCATION_PKEY to find a single matching rowid that corresponds to the location record that matches the employee whose record you already read. If no matching row is found, discard the result row being built.

  4. Otherwise, for the matching rowid, go to the table Locations (LE) with a single-block read (logical read, physical when necessary) according to the rowid from the previous step, using the block-address part of the rowid. Using the row-address part of the rowid, find the specific row that the rowid points to and read all necessary data (requested data for alias LE) from that row. Append the applicable data to the incoming row from the earlier table read to complete the result row. Discard the whole result row if it contains data that fails to meet the condition UCASE(LE.Description) = ?.

Note that you will find no explicit step in the execution plan for this last filter, which discards rows that fail to meet the condition on the location description. I call this filter a post-read filter, since it does not contribute to the method of reaching this table row but instead is used to discard some rows after they are read. DB2 does not make the discard actions on post-read filters explicit in the tables I queried, but you can always count on DB2 to apply them at the first opportunity, as soon as it has reached the data necessary to evaluate the truth of their conditions. Since the execution plan includes further joins after this join, DB2 performs those joins only on rows that passed this post-read filter, discarding the rest.


For each row returned that combines E and LE:

  1. Using the join condition E.Manager_ID=M.Employee_ID, go to the primary-key index EMPLOYEE_PKEY to find a single matching rowid that corresponds to the employee record of the manager for the employee whose record you already read. If no matching row is found, discard the result row being built.

  2. Otherwise, for the matching rowid, go to the table Employees (M) with a single-block read (logical read, physical when necessary) according to the rowid from the previous step, using the block-address part of the rowid. Using the row-address part of the rowid, find the specific row that the rowid points to and read all necessary data (requested data for alias M) from that row. Append the applicable data to the incoming row from the earlier table reads to build a partial result row.

  3. For each such row, using the join condition M.Location_ID=LM.Location_ID, go to the primary-key index LOCATION_PKEY to find a single matching rowid that corresponds to the location record that matches the manager for the employee whose record you already read. If no matching row is found, discard the result row being built.

  4. Otherwise, for the matching rowid, go to the table Locations (LM) with a single-block read (logical read, physical when necessary) according to the rowid from the previous step, using the block-address part of the rowid. Using the row-address part of the rowid, find the specific row that the rowid points to and read all necessary data (requested data for alias LM) from that row. Append the applicable data to the incoming row from the earlier table reads to complete each result row. Immediately return the fully built result row.

3.2.5 Nonrobust Execution Plans

Execution plans often use join methods other than nested loops, especially the starting plans you will need to tune, so I next show an example that performs one of the joins by the less robust sort-merge method. If I drop all the indexes, DB2 delivers a new execution plan:

$ cat head.sql tmp.sql tail.sql | db2 +c +p -t
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
          1         - RETURN        -                        21033
          2         1 NLJOIN        -                        21033
          3         2 NLJOIN        -                        20830
          4         3 MSJOIN        -                        10517
          5         4 TBSCAN        -                          204
          6         5 SORT          -                          204
          7         6 TBSCAN        LOCATIONS                  204
          8         4 FILTER        -                        10313
          9         8 TBSCAN        -                        10313
         10         9 SORT          -                        10313
         11        10 TBSCAN        EMPLOYEES                10313
         12         3 TBSCAN        EMPLOYEES                10313
         13         2 TBSCAN        LOCATIONS                  202

  13 record(s) selected.

DB20000I  The SQL command completed successfully.
$

In steps shown with OPERATOR_ID 5 through 11, DB2 sorts full table scans of Locations and Employees (aliases LE and E) on the join key Location_ID, discarding rows that fail to meet the filter conditions on these tables. In the step shown with OPERATOR_ID=4, DB2 performs a sort-merge join between E and LE. Interestingly, since it sees such good filters on both these tables, it estimates it will likely have at most a single row left at that step, and it chooses to do nested loops to full table scans to join to aliases M and LM, as the last two steps. Nested loops to full table scans such as this would scale badly if the data caused DB2 to loop many times. The cost of merge or hash joins would be slightly higher than nested loops to a single full table scan, but such joins would scale much better.

3.2.6 Complex Execution Plans

There are other execution-plan features, such as indicators of which joins are outer joins and steps for sorts and sort-unique operations that discard duplicates that you will see regularly, but these are fairly self-explanatory and are not usually important to performance. The only remaining important subtleties that you will often see deal with subqueries and multipart execution plans. I'll cover both of these at once with one final example:

SELECT E.First_Name, E.Nickname, E.Last_Name,
E.Phone_Number, L.Description
FROM Employees E 
     INNER JOIN Locations L ON E.Location_ID=L.Location_ID
WHERE (E.First_Name= ? OR E.Nickname= ?)
AND EXISTS (SELECT 1 FROM Wage_Payments P
            WHERE P.Employee_ID=E.Employee_ID
            AND P.Payment_Date > CURRENT DATE - 31 DAYS);

Populate Wage_Payments with 500,000 rows. Place indexes on:

  • Employees(First_Name)

  • Employees(Nickname)

  • Locations(Location_ID)

  • Wage_Payments(Employee_ID)

You then find the following execution plan:

$ cat head.sql tmp.sql tail.sql | db2 +c +p -t
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
          1         - RETURN        -                         2014
          2         1 MSJOIN        -                         2014
          3         2 TBSCAN        -                          203
          4         3 SORT          -                          203
          5         4 TBSCAN        LOCATIONS                  202
          6         2 FILTER        -                         1810
          7         6 TBSCAN        -                         1810
          8         7 SORT          -                         1810
          9         8 NLJOIN        -                         1810
         10         9 FETCH         EMPLOYEES                  422
         11        10 RIDSCN        -                          100
         12        11 SORT          -                           50
         13        12 IXSCAN        EMP_FIRST_NAME              50
         14        11 SORT          -                           50
         15        14 IXSCAN        EMP_NICKNAME                50
         16         9 FETCH         WAGE_PAYMENTS              134
         17        16 IXSCAN        WAGE_PYMNT_EMP_ID           50

  17 record(s) selected.                                            
                                      
$

Steps shown with OPERATOR_ID 11 through 15 show the collection of a union of the sets of rowids from the name conditions joined by OR on E. The resulting new set of rowids feeds into the step labeled OPERATOR_ID=10 to get just the set of employees that have the chosen name or nickname. From that list, DB2 chooses nested loops (NLJOIN) to Wage_Payments. The loops halt as soon as the first match is found, since this is an EXISTS correlated join. This nested-loops join is labeled OPERATOR_ID=9. It discards any Employees records that fail to find a matching Wage_Payment in the subquery. Since DB2 calculates that it still has a fairly long list of Employees by that point, it chooses to read the Locations table once and perform a merge join (MSJOIN) with the Employees records, sorting both rowsets on the join keys.