EXPLAIN Facility and Predictive Analysis › Table Designs for Special Situations › What EXPLAIN Does Not Tell You

What EXPLAIN Does Not Tell You

EXPLAIN does not tell you everything about your queries. You have to be aware of this limitation to effectively performance tune and predictively analyze. EXPLAIN does not tell you about the following:

• INSERT indexes

  EXPLAIN does not tell you the index that DB2 will use for an INSERT statement. It is important that you understand your clustering indexes, and whether DB2 will be using APPEND processing for your inserts. This understanding is important for INSERT performance, and the proper organization of your data. For more information, see Designing Tables and Indexes for Performance.

• Access path information for enforced referential constraints

  If you have INSERTS, UPDATES, and DELETES in the program to which you have applied EXPLAIN, the database enforced RI relationships and associated access paths are not exposed in the EXPLAIN tables. Ensure, therefore, that proper indexes in support of the RI constraints are established and in use.

• Predicate evaluation sequence

  The EXPLAIN tables do not show you the order in which predicates of the query are evaluated. For more information on predicate evaluation sequence, see Predicates and SQL Tuning.

• Statistics used

  The optimizer used catalog statistics to help determine the access path at the time EXPLAIN was applied to the statement. Unless you have historical statistics that correspond to the time that EXPLAIN was executed, you do not know that how the statistics appeared when the EXPLAIN was executed. Additionally, you do not know if they are different now and therefore you provide a different access path.

• Input values

  If you are using host variables in your programs, EXPLAIN does not know about the potential input values to those variables. Therefore, it is important for you to understand these values, what the most common values are, and if data is skewed relative to the input values.

• SQL statement

  The SQL statement is not captured in the EXPLAIN tables, although some of the predicates are. If you dynamically applied EXPLAIN to a statement, or you applied it through one of the tools, you know what the statement looks like. However, if you applied EXPLAIN to a package or plan, you have to see the program source code.

• Order of input to your transactions

  The SQL statement may appear to be correct when you view the access path, but you cannot tell the order of the input data in relation to the statement, what ranges are supplied, or how many transactions are issued. EXPLAIN output does not let you see whether it is possible to order the input or the data in the tables more efficiently.

• Program source code

  To understand the impact of the access path that a statement used, you have to see how that statement is used in the application program. We recommend that you regularly review the program source code of the program in which the statement is embedded in. By reviewing the program source code, you can answer the following questions:

  • How many times will the statement be executed?
  • Is the statement in a loop?
  • Can you avoid executing the statement?
  • Is the program issuing 100 statements on individual key values in a range predicate when one statement would suffice?
  • Is the programming performing programmatic joins?

  Although the EXPLAIN output may show an efficient access path, the statement might be unnecessary. You can also use a monitor or performance trace, which lets you see exactly which statements are executed.