Explain information for instances
Explain instance information is stored in the EXPLAIN_INSTANCE table. Additional specific information about each query statement in an instance is stored in the EXPLAIN_STATEMENT table.
Explain instance identification
- The user who requested the explain information
- When the explain request began
- The name of the package that contains the explained statement
- The SQL schema of the package that contains the explained statement
- The version of the package that contains the statement
- Whether snapshot information was collected
- The version and release number of the Db2® product
- The degree of parallelism under which the query was compiled
The CURRENT DEGREE special register, the DEGREE bind option, the SET RUNTIME DEGREE command, and the dft_degree database configuration parameter determine the degree of parallelism under which a particular query is compiled.
- Whether the statement is dynamic or static
- The query optimization class used to compile the query
- The type of row blocking for cursors that occurs when compiling the query
- The isolation level under which the query runs
- The values of various configuration parameters when the query
was compiled. Values for the following parameters are recorded when
an explain snapshot is taken:
- Sort heap size (sortheap)
- Average number of active applications (avg_appls)
- Database heap (dbheap)
- Maximum storage for lock list (locklist)
- Maximum percent of lock list before escalation (maxlocks)
- CPU speed (cpuspeed)
- Communications bandwidth (comm_bandwidth)
- The type of statement: SELECT, DELETE, INSERT, UPDATE, positioned DELETE, positioned UPDATE, or SET INTEGRITY
- The statement and section number of the package issuing the statement, as recorded in the SYSCAT.STATEMENTS catalog view
The QUERYTAG and QUERYNO fields in the EXPLAIN_STATEMENT
table contain identifiers that are set as part of the explain process.
When EXPLAIN MODE or EXPLAIN SNAPSHOT is active, and dynamic explain
statements are submitted during a command line processor (CLP) or
call-level interface (CLI) session, the QUERYTAG value is set to
respectively. In this case, the QUERYNO value defaults to a number
that is incremented by one or more for each statement. For all other
dynamic explain statements that are not from the CLP or CLI, or that
do not use the EXPLAIN statement, the QUERYTAG value is set to blanks
and QUERYNO is always 1.
- The query optimizer does not estimate elapsed time but only resource consumption.
- The optimizer does not model all factors that can affect elapsed time. It ignores factors that do not affect the efficiency of the access plan. A number of runtime factors affect the elapsed time, including the system workload, the amount of resource contention, the amount of parallel processing and I/O, the cost of returning rows to the user, and the communication time between the client and server.
optimizedtext include the following:
- The name of a derived column, where n represents an integer value.
- This tag identifies a constraint that was added to the original statement during compilation, and is seen in conjunction with the $WITH_CONTEXT$ prefix.
- The name of a derived table, where n represents an integer value.
- This tag indicates the presence of a function that is used by the compiler for the explained query but that is not available for general use.
- This tag indicates the presence of a predicate that was added during compilation of the explained query but that is not available for general use. An internal predicate is used by the compiler to satisfy additional context that is added to the original statement because of triggers or constraints.
- This tag indicates the presence of an internal table function that takes a single annotated XPath pattern as an input parameter and returns a table with one or more columns that match that pattern.
- This tag identifies the row identifier (RID) column for a particular row.
- This tag identifies a trigger that was added to the original statement during compilation, and is seen in conjunction with the $WITH_CONTEXT$ prefix.
- This prefix appears at the beginning of the text when additional triggers or constraints have been added to the original query statement. A list of the names of any triggers or constraints that affect the compilation and resolution of the statement appears after this prefix.