EXPLAIN Facility and Predictive Analysis

EXPLAIN Facility

The DB2 EXPLAIN facility is used to expose query access path information. This result enables application developers and DBAs to see what access path DB2 is going to take for a query and decide if the query tuning is needed. DB2 can gather basic access path information in a special table called the PLAN_TABLE (DB2 V7, DB2 V8, DB2 9), as well as detailed information about predicate stages, filter factor, predicate matching, and dynamic statements that are cached (DB2 V8, DB2 9).

You can invoke EXPLAIN by doing one of the following:

Executing the EXPLAIN SQL statement for a single statement
Specifying the BIND option EXPLAIN(YES) for a plan or package bind
Executing the EXPLAIN through the Optimization Service Center or through Visual EXPLAIN

EXPLAIN can populate many tables when it is executed. The target set of EXPLAIN tables depends on the authorization ID associated with the process. The creator (schema) of the EXPLAIN tables is determined by the CURRENT SQLID of the person running the EXPLAIN statement, or the owner of the plan or package at bind time. The EXPLAIN tables are optional, and DB2 only populates the tables that it finds under the SQLID or owner of the process invoking the EXPLAIN.

The following tables can be defined manually, and the DDL can be found in the DB2 sample library member DSNTESC:

PLAN_TABLE

Contains basic access path information for each query block of your statement. This information includes, details about index usage and the number of matching index columns, which join method is used, which access method is used, and whether a sort will be performed. The PLAN_TABLE forms the basis for access path determination.

DSN_STATEMNT_TABLE

Contains estimated cost information for the cost of a statement. If the statement table is present when you run EXPLAIN on a query, the table is populated with the cost information that corresponds to the access path information for the query stored in the PLAN_TABLE. For a given statement, this table contains the estimated processor cost in milliseconds and service units and it places the cost values into the following categories:

Category A - DB2 has enough information to make a cost estimation without using any defaults.
Category B - DB2 has to use default values to make some cost calculations.

The statement table can be used to compare estimated costs when you are attempting to modify statements for performance. This is a cost estimate, and is not truly reflective of how your statement will be used in an application (given input values, transaction patterns, and so on). You should always test your statements for performance in addition to using the statement table and EXPLAIN.

DSN_FUNCTION_TABLE

Contains information about user-defined functions that are a part of the SQL statement. Information from this table can be compared to the cost information (if populated) in the DB2 System Catalog table, SYSIBM.SYSROUTINES, for the user-defined functions.

DSN_STATEMENT_CACHE_TABLE

Is not populated by a normal invocation of EXPLAIN, but instead by the EXPLAIN STMTCACHE ALL statement. Issuing this statement results in DB2 reading the contents of the dynamic statement cache, and putting runtime execution information into the table. This includes information about the frequency of execution of these statements, the statement text, the number of rows that are processed by the statement, lock and latch requests, I/O operations, number of index scans, number of sorts, and so on. This is valuable information about the dynamic queries executing in a subsystem. This table is available only for DB2 V8 and DB2 9.

Note: Many EXPLAIN tables exist. Additional EXPLAIN tables are typically populated by the optimization tools that use them. You can make some tables without using the various optimization tools. For information about these tables, see the DB2 Performance Monitoring and Tuning Guide (DB2 9).