Interpreting the Explain report

This section highlights some of the information to look for in an Explain report.

The information reported varies depending on the type of report, the level of detail requested, and whether data is available to report. The example shown in the previous section shows an Explain at package level. The package explained contains SQL statements. The statement QUERYNO=1011071 has two statement steps (Select with index for PLANNO=1 and sort for ORDER BY for PLANNO=2).

Page header

The page header (Explain report – page header) is printed at the top of each report page and shows general information about the report, including the type of report, subsystem identifier, DB2 version, and the objects reported.

Package version

The Package Version block of the report (Explain report – package version details) shows the package versions available and the versions and generations reported.

Statement details

The Statement Details block of the report (Explain report – statement details) shows the complete plan or package identification details, including:

Location
Name
Statement number and text
Other statement-related information

It also shows the status and isolation level, from SYSIBM.SYSPLAN or SYSIBM.SYSPACKAGE or SYSIBM.SYSPACKSTMT, and cost information.

PLAN_TABLE information

This shows the contents of the PLAN_TABLE (Explain report – PLAN_TABLE details). The report block starts with EXPLAIN TABLE and has two block columns. The report field labels are the full PLAN_TABLE column names. If the report field value is a long name or a long value, it is marked with an asterisk (*) at the end of the report field value, and reported at the end of the block in its full length.

The plan table is searched by using the bind time of the package. There might be multiple occurrences of the package with different bind times. For example, the Db2 system catalog might contain the latest package that is created by the Db2 command BIND or REBIND. It might also contain a former package version that is activated by the Db2 command REBIND SWITCH.

Access path

The Access Path block of the report (Explain report – access path) shows, for each step in an SQL statement, the access path used by DB2 to satisfy the statement step. This shows:

Use of index

If an index is used, the type of index scan and details about matching columns are shown.

The appropriate use of indexes plays a key role in the efficiency of an application or query. For example, if a table contains 3000 rows of information in 100 data pages, the following query returns one row of data:

SELECT COL1 COL2 FROM T1 WHERE COL4=10 AND COL5=20

No index

If the table has no index associated, DB2 must scan each data page to satisfy the query. In this instance, DB2 must perform 100 Getpage operations for a single SELECT statement.

Index on COL4

In this instance, the number of Getpage operations depends on the number of rows matching the predicate and their distribution, and the number of index pages. For example, if rows matching the predicate are spread over six data pages and pointers are spread over two index pages, the number of Getpage operations for the SELECT is eight.

Unique index on COL4 and COL5

In this instance, DB2 only needs to read one page from the root index and one page from the leaf index to locate the data page. The number of Getpage operations for the SELECT is three.

Index only scan

When you create an index on COL1, COL2, COL4, and COL5, and alter the SELECT to predicate all four columns:

SELECT COL1 COL2 FROM T1 WHERE COL4=10 AND COL5=20 
AND COL1 NOT NULL AND COL2 NOT NULL

In this instance, DB2 can satisfy the query from the index alone, requiring just one Getpage operation for the root index and one Getpage operation for the leaf index.

Properly organized indexes can also help reduce or eliminate sort operations.

You can define multiple indexes on a base table. However, weigh the performance gain with the additional workload required for the database manager to update the indexes when data changes in the table. Generally, multiple indexes are useful for tables that are often queried and seldom updated.

You can use the Accounting reports to derive the Getpage/SQL ratios for plans and packages. This is the total number of Getpage operations divided by the total number of SQL SELECT, INSERT, UPDATE, and FETCH statements. The value of the ratio depends on the type of application and the DB2 environment, but you might look for applications with ratios greater than five.

Prefetch

Prefetch improves the performance of DB2 by reducing the time spent waiting for sequential I/O. It can also substantially reduce the Getpage/SQL ratio.

For Sequential Prefetch, data must be ordered in the tables in the same way as it is accessed by the application. This allows DB2 to fetch the pages before they are accessed by the application. This means that the design and organization of tables and applications needs to be tailored to exploit this behavior. DB2 can also decide to use Sequential Prefetch if it sees that the data is sequenced. This is known as Dynamic Prefetch. For SQL queries DB2 can read up to 32 pages per Read I/O.

List Prefetch works much like Sequential Prefetch, except that data pages do not need to be contiguous. List prefetch is always used for multiple index access and to obtain access to data from the inner table of a hybrid join.

Direct row access

Direct row access allows DB2 to obtain access to a row directly through the ROWID column. If an application selects a row from a table that contains a ROWID column, the row ID value implicitly contains the location of the row. If you use that row ID value in the search condition of subsequent SELECTs, DB2 might be able to navigate directly to the row.

To use direct row access, you first select the values of a row into host variables. The value that is selected from the ROWID column contains the location of that row. Later, when you perform queries which access that row, you include the row ID value in the search condition. If DB2 determines that it can use direct row access, DB2 uses the row ID value to navigate directly to the row.

Because direct row access provides highly efficient data access, consider modifying older applications to exploit this feature.

Index data

The Index Data block of the report (Explain report – index details) shows data derived from the SYSIBM.SYSINDEXES table. This is only shown if an index is used in the access plan. You can use the Information in SYSINDEXES to compare the available indexes on a table to determine which one is the most efficient for a query.

Key column data

The Key Column Data block of the report (Explain report – key column details) shows index key information that is derived from the SYSIBM.SYSKEYS and SYSIBM.SYSCOLUMNS tables. This is only shown when an index is used in the access plan.

Table

The Table Data block of the report (Explain report – table details) shows information that is derived from SYSIBM.SYSTABLES.

Table space

The Table Space Data block of the report (Explain report – table space details) shows information that is derived from SYSIBM.SYSTABLESPACE.

Host variables data

When host variables are used in a statement, OMEGAMON XE for DB2 PE can show details about each variable (Explain report – host variables details). A host variable can be either a variable in a host language (such as a PL/I variable, C variable, Fortran variable, a COBOL data item, or Assembler language storage area) or a host language construct that was generated by an SQL precompiler from a variable declared using SQL extensions. A host variable can be an output value that is returned to the application by DB2 or an input to DB2.

Summary

The summary report (Explain summary report) shows the package name, SQL statement number, type, and access path for each statement reported. When the summary is appended to a full report, the statement numbers are also cross-referenced to the report page where the full entry can be found.