SQL Tuning [Electronic resources] نسخه متنی

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:

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.

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

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.

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:

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

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.

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:

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.

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.

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.

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:

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.

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.

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.

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.