Explain tables and the organization of explain information
An explain instance represents one invocation of the explain facility for one or more SQL or XQuery statements. The explain information that is captured in one explain instance includes information about the compilation environment and the access plan that is chosen to satisfy the SQL or XQuery statement that is being compiled.
- All eligible SQL or XQuery statements in one package, for static
query statements. For SQL statements (including those that query XML
data), you can capture explain information for CALL, compound SQL
(dynamic), DELETE, INSERT, MERGE, REFRESH TABLE, SELECT, SELECT INTO,
SET INTEGRITY, UPDATE, VALUES, and VALUES INTO statements. In the
case of XQuery statements, you can obtain explain information for
XQUERY db2-fn:xmlcolumn and XQUERY db2-fn:sqlquery statements.Note: REFRESH TABLE and SET INTEGRITY statements are compiled only dynamically.
- One particular SQL statement, for incremental bind SQL statements.
- One particular SQL statement, for dynamic SQL statements.
- Each EXPLAIN statement (dynamic or static).
- Run the EXPLAIN.DDL script in the misc subdirectory of the sqllib subdirectory.
- Use the SYSPROC.SYSINSTALLOBJECTS procedure. You can also use this procedure to drop and validate explain tables.
Explain tables can be common to more than one user. You can define tables for one user and then create aliases pointing to the defined tables for each additional user. Alternatively, you can define the explain tables under the SYSTOOLS schema. The explain facility uses the SYSTOOLS schema as the default if no explain tables or aliases are found under your session ID (for dynamic SQL or XQuery statements) or under the statement authorization ID (for static SQL or XQuery statements). Each user sharing common explain tables must hold the INSERT privilege on those tables.
The following table summarizes the purpose of each explain table.
|ADVISE_INDEX||Stores information about recommended indexes. The table can be populated by the query compiler or the db2advis command, or you can populate it. This table is used to get recommended indexes and to evaluate proposed indexes.|
|ADVISE_INSTANCE||Contains information about db2advis command execution, including start time. This table contains one row for each execution of the db2advis command.|
|ADVISE_MQT||Contains the following information:
|ADVISE_PARTITION||Stores virtual database partitions that are generated and evaluated by the db2advis command.|
|ADVISE_TABLE||Stores the data definition language (DDL) statements for table creation, using the final Design Advisor recommendations for MQTs, multidimensional clustering tables (MDCs), and database partitioning.|
|ADVISE_WORKLOAD||Contains a row for each SQL or XQuery statement in a workload. The db2advis command uses this table to collect and store workload information.|
|EXPLAIN_ACTUALS||Contains the explain section actuals information.|
|EXPLAIN_ARGUMENT||Contains information about the unique characteristics of each operator, if any.|
|EXPLAIN_DIAGNOSTIC||Contains an entry for each diagnostic message that is produced for a particular instance of an explained statement in the EXPLAIN_STATEMENT table.|
|EXPLAIN_DIAGNOSTIC_DATA||Contains message tokens for diagnostic messages that are recorded in the EXPLAIN_DIAGNOSTIC table. The message tokens provide additional information that is specific to the execution of the SQL statement that generated the message.|
|EXPLAIN_INSTANCE||Is the main control table for all explain information. Each row in the explain tables is linked to a unique row in this table. Basic information about the source of the SQL or XQuery statements being explained and environmental information are kept in this table.|
|EXPLAIN_OBJECT||Identifies the data objects that are required by the access plan that is generated to satisfy an SQL or XQuery statement.|
|EXPLAIN_OPERATOR||Contains all of the operators that the query compiler needs to satisfy an SQL or XQuery statement.|
|EXPLAIN_PREDICATE||Identifies the predicates that are applied by a specific operator.|
|EXPLAIN_STATEMENT||Contains the text of the SQL or XQuery
statement for the different levels of explain information. The SQL
or XQuery statement that you issued and the version that the optimizer
uses to choose an access plan are stored in this table.
When an explain snapshot is requested, additional explain information is recorded to describe the access plan that was selected by the query optimizer. This information is stored in the SNAPSHOT column of the EXPLAIN_STATEMENT table.
|EXPLAIN_STREAM||Represents the input and output data streams between individual operators and data objects. The operators are represented in the EXPLAIN_OPERATOR table. The data objects are represented in the EXPLAIN_OBJECT table.|
|OBJECT_METRICS||Contains runtime statistics for
each object that is referenced in a specific execution of a section
at a specific time. If object statistics are collected on multiple
members, this table contains a row for each member on which the object
was referenced. If object statistics are collected for a partitioned
object, this table contains a row for each data partition.
This table contains information only if the activity event monitor captures section actuals.