Administration Guide

How Explain Information is Organized

All explain information is organized around the concept of an explain instance. An explain instance represents one invocation of the explain facility for one or more SQL statements. An explain instance represents the explain information for:

• All the eligible SQL statements in one package for static SQL statements
• One particular SQL statement for dynamic SQL statements
• Each EXPLAIN SQL statement (whether dynamic or static).

The explain information captured within one explain instance includes the SQL Compilation environment as well as the access plan chosen to satisfy the SQL statement being compiled. Explain information is organized into 3 subsets:

Explain Instance Information

Compilation environment information captured for each explain instance.

Explain Snapshot Information

Information used by Visual Explain.

Explain Table Information

Information collected when explain table information is requested.

Explain Instance Information

Explain instance information is stored in the EXPLAIN_INSTANCE table. Additional specific information about each SQL statement explained within an explain instance is stored in the EXPLAIN_STATEMENT table.

Explain Instance Identification: You can uniquely identify each explain instance and correlate the information for the SQL statements to a given invocation of the facility with this information:

• The user who requested the explain information
• When the explain request began
• The name of the package from which the explained SQL statement came
• The schema of the package from which the explained SQL statement came.
• An indication whether a snapshot was part of the explain request.

Environmental Settings: Environmental information concerning how the SQL compiler optimized your queries is captured. The environmental information includes the following:

• The version and release number for the level of DB2 being used.

• The degree of parallelism used to compile the query. The CURRENT DEGREE special register, the DEGREE bind option, the SET RUNTIME DEGREE API, and the dft_degree configuration parameter may be used to determine the degree of parallelism to be used when compiling a particular query.

• Whether the SQL statement was dynamic or static.

• The query optimization class used to compile the query. See Adjusting the Optimization Class for more information.

• The type of cursor blocking specified when compiling the query. For more information about cursors, refer to the SQL Reference manual. For more information about cursor blocking, see Row Blocking.

• The isolation level used when compiling the query. See Concurrency for more information.

• The values of various configuration parameters when the query was compiled. See Configuration Parameters Affecting Query Optimization for more information about the configuration parameters that can affect query optimization, including the following parameters that are recorded when an explain snapshot is taken:
  • Buffer Pool Size (buffpage)
  • 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).

SQL Statement Identification: For each explain instance, multiple SQL statements may have been explained. Along with information that uniquely identifies the explain instance, the following information helps identify each individual SQL statement.

• The type of statement: SELECT, DELETE, INSERT, UPDATE, positioned DELETE, positioned UPDATE.
• The statement and section number of the package issuing the SQL statement, as recorded in SYSCAT.STATEMENTS catalog view.

Within the EXPLAIN_STATEMENT table, the QUERYTAG and QUERYNO fields contain identifiers and are set for you as part of the explain process.

For dynamic explain SQL statements submitted during a CLP or CLI session, when EXPLAIN MODE or EXPLAIN SNAPSHOT is active, the QUERYTAG is set to "CLP" or "CLI". When this happens, the QUERYNO is defaulted to a number that is incremented by one or more for each statement.

For all other dynamic explain SQL statements (not from CLP, CLI, or using the EXPLAIN SQL statement) the QUERYTAG is set to blanks, and the QUERYNO will always be "1".

Cost Estimation: For each statement explained, an estimate of the relative cost of executing the chosen access plan is recorded. This cost is given using a made-up, relative unit of measure called timerons. Estimates of elapsed times are not provided, for the following reasons:

• The SQL optimizer does not estimate elapsed time but rather resource consumption.
• The optimizer does not model all factors that can affect elapsed time; it ignores those that do not affect the efficiency of the access plan. The elapsed time is affected by a number of run-time factors 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.

Statement Text: For each statement explained, two versions of the text of the SQL statement are recorded. One version is the text as received by the SQL Compiler. The other is a version of the statement text that has been reverse-translated from the internal compiler representation of the query. This translation, while looking similar to other SQL statements, does not necessarily follow correct SQL syntax nor does it necessarily reflect the actual content of the internal representation as a whole. This translation is provided simply to allow an understanding of the SQL context from which the SQL optimizer chose the access plan. Comparing the user-written statement text to the internal representation of the SQL statement can help you to understand how the SQL compiler has rewritten your query for better optimization. (See Rewrite Query by the SQL Compiler.) It also shows you other elements in the environment affecting your statement such as triggers and constraints. Some keywords used by this "optimized" text are:

$Cn

The name of a derived column, where n represents an integer value.

$CONSTRAINT$

The tag used to indicate the name of a constraint added to the original SQL statement during compilation. Seen in conjunction with the $WITH_CONTEXT$ prefix.

$DERIVED.Tn

The name of a derived table, where n represents an integer value.

$INTERNAL_FUNC$

The tag used to indicate the presence of a function used by the SQL Compiler for the explained query but not available for general use.

$INTERNAL_PRED$

The tag used to indicate the presence of a predicate added by the SQL Compiler during compilation of the explained query. Again, such a predicate is not available for general use. An internal predicate is used by the compiler to satisfy additional context added to the original SQL statement as the result of triggers and constraints.

$RID$

The tag used to identify the Row Identifier (RID) column for a particular row.

$TRIGGER$

The tag used to indicate the name of a trigger added to the original SQL statement during compilation. Seen in conjunction with the $WITH_CONTEXT$ prefix.

$WITH_CONTEXT$(...)

This prefix will appear at the start of the text when additional triggers or constraints have been added into the original SQL statement. Following this prefix will appear a list of the names of any triggers or constraints affecting the compilation and resolution of the SQL statement.

Explain Snapshot Information

When an explain snapshot is requested, additional explain information is recorded describing the access plan selected by the SQL optimizer. This information is stored in the SNAPSHOT column of the EXPLAIN_STATEMENT table in the format required by Visual Explain. This format is not usable by other applications.

Additional information on the contents of the explain snapshot information is available from Visual Explain itself and in:

• Explain Information for Data Objects
• Explain Information for Data Operators.

Explain Table Information

When explain table information is requested, additional information is recorded describing the access plan selected by the SQL optimizer. This information is stored in the following explain tables:

• EXPLAIN_ARGUMENT. This table represents the unique characteristics for each individual operator, if any.
• EXPLAIN_INSTANCE. This table is the main control table for all Explain information. Each row of data in the Explain tables is explicitly linked to one unique row in this table. Basic information about the source of the SQL statements being explained and environment information is kept in this table.
• EXPLAIN_OBJECT. This table identifies those data objects required by the access plan generated to satisfy the SQL statement.
• EXPLAIN_OPERATOR. This table contains all the operators needed to satisfy the SQL statement by the SQL compiler.
• EXPLAIN_PREDICATE. This table identifies which predicates are applied by a specific operator.
• EXPLAIN_STATEMENT. This table contains the text of the SQL statement as it exists for the different levels of Explain information. The original SQL statement as entered by the user is stored in this table along with the version used (by the optimizer) to choose an access plan to satisfy the SQL statement.
• EXPLAIN_STREAM. This table represents the input and output data streams between individual operators and data objects. The data objects themselves are represented in the EXPLAIN_OBJECT table. The operators involved in a data stream are represented in the EXPLAIN_OPERATOR table.
• ADVISE_WORKLOAD. This table allows users to describe their workload to the database. Each row in the workload represents a SQL statement, and is described by an associated frequency. This table is used by the db2advis tool and the Index SmartGuide, to pick up and store work and information.
• ADVISE_INDEX. This table stores information about recommended indexes. The table is populated by the SQL compiler, the db2advis utility, the Index SmartGuide, or a user. This table is used in two ways:

  • To get recommended indexes.

  • To evaluate indexes based on input about proposed indexes.

All of the tables above are not created by default. They can be created by running the EXPLAIN.DDL script found in the misc subdirectory of the SQLLIB subdirectory. Connect to the database where the Explain and Advise tables are required. Then issue the command: db2 -tf EXPLAIN.DDL and the tables will be created. The tables could also be automatically created by the Index SmartGuide, if necessary.

Each rectangular object node of Visual Explain corresponds to a row in the EXPLAIN_OBJECT table. Each octagonal "operator" node of Visual Explain corresponds to a row in the EXPLAIN_OPERATOR table. Each link between operators or operator's objects corresponds to a row of the EXPLAIN_STREAM table.

The explain table information is similar in content to that recorded for an explain snapshot, however, this information is stored in ordinary relational tables which can be accessed using standard SQL statements.

Explain tables, like the Visual Explain access plan graph, are designed to reflect the relationships between operators and data objects within the access plan. The following diagram shows the relationships between these tables.

Figure 82. Overview of Explain Table Relationships (not all tables are shown).

It is possible to have explain tables that are common to more than one user. The explain tables can be defined for one user. Aliases can then be defined using the same name for each additional user pointing to the defined tables. Each user sharing the common explain tables must have insert permission on those tables.

See Appendix L, SQL Explain Tools for more information on the Explain tables and how to create the tables. Additional information on the contents of the explain table information is available in:

• Explain Information for Data Objects
• Explain Information for Data Operators.

The db2exfmt tool provided in the misc subdirectory under the sqllib directory can be used to format the contents of the explain tables into a legible, organized output.

[ DB2 List of Books | Search the DB2 Books ]