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:
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.
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.
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.
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.
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.
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.
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.
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:
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.
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|