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 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.
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.
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.
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.
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.
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.
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. $
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.
|
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.
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) = ?.
|
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.
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.
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.