Access Path Analysis

The Access Path Analysis screen displays when you select option 1 from the Plan Table Analysis screen. It provides a detailed analysis of how the SQL statement is analyzed and executed by Db2. For example, the sequence and manner in which various tables are accessed, the locking method that is used, and a comprehensive description of both consequences and recommendations.

Access Path Analysis Screen

File-AID for DB2 -------- PLAN_TABLE Analysis Report for: <SOURCE>
COMMAND ===>                                                  SCROLL ===> CSR

Use SQL to View/Save/Re-explain the SQL Source

DB2 VERSION: 10.1.5

QUERY NO    : 1
CURR SCHEMA : DB2SA

Conditions
  Query Block No:      1
  Plan No:             1

  Query Block Type:     SELECT

  01 - The following table will be the first table accessed in this
       query block.
         TB Creator: SYSIBM
              Table: SYSSTMT

  06 - The following table will be accessed through a tablespace scan
       of DSNDB06.SYSPLAN
         TB Creator: SYSIBM
              Table: SYSSTMT
  07 - The tablespace contains 1080 pages.
  08 - The tablespace contains multiple tables and is not segmented.
  33 - A sequential PREFETCH will be used to read a series of
       consecutive pages.
  39 - Intent-share locking at the page level is in effect for this query.
  64 - The Access Path was determined at Bind time using
       default filter factors for host variables and parameter
       markers.
  94 - The encoding scheme of the table is Unicode.

Consequences
  03 - A scan of a tablespace of this size will significantly add to:
       - the processing cost of the query
         number of rows scanned is a contributing factor.
       - the I/O cost of the query because the number
         of data pages read is also a factor.

  04 - In a tablespace that is not segmented, DB2 reads every row in
       tablespace DSNDB06.SYSPLAN. The total time required to
       execute the query depends on the number of pages to be read
       in the tablespace. This will include storage for other
       tables, space left free for expansion and unreclaimed
       storage after delete operations.

Recommendations
  07 - The following table has no available indexes. Consider creating
       an index for frequently used columns in the table and
       incorporating the index into the query.
          TB Creator: SYSIBM
               Table: SYSSTMT

  08 - Consider segmenting tablespace DSNDB06.SYSPLAN,
       so that DB2 will scan only segments containing rows of
       the following table:
          TB Creator: SYSIBM
               Table: SYSSTMT

The report contains header information and a Conditions, Consequences, and Recommendations section for each table and/or index used by the SQL statement. The text in these sections can be modified to meet your site’s needs. See Modifying-Access-Path-Analysis-Text for information on modifying the text.

Header Information

The top of the report contains header information, including the Db2 Version and the Query Number. The Db2 Version is the Db2 release for the subsystem upon which the query is executed. The Query Number identifies the EXPLAINed statement. File-AID for Db2 assigns the program statement number as the query number unless the statement has a QUERYNO clause specified. The explain command may generate one or more rows in the PLAN_TABLE. If such is the case, all rows with the same query number refer to the same SQL statement. The PLAN_TABLE only contains the QUERYNO, not the statement number.

Conditions

The Conditions section of the report identifies the Query Block Number and Plan Number for the statement. The Query Block Number (QBlock Number) identifies the position of the query within the SQL statement. For example:

SELECT * FROM ABC WHERE COL1 IN
SELECT COL1 FROM DEF WHERE COL2 = 0

The outer SELECT will have a Query Block Number of 1 and the second SELECT a Query Block Number of 2.

The Plan Number identifies the sequence in which the steps within the Query Block Number specified are processed.

Tip

The following data can also display in the Conditions section, depending on the type of statement being explained: Query Block Type, Parent Query Block Type, Method of Join, and Join Type.

The Conditions section also contains information on any locking that may be performed as well as any index utilization.

Consequences

The Consequences section of the report outlines the performance ramifications of the SQL and the effects of object volume. The analysis is performed using actual catalog statistics in effect at the time the analysis operation was performed. To maximize the potential of the analysis operation, use objects with characteristics that closely resemble the objects that the SQL will ultimately be used against.

Recommendations

The Recommendations section of the report offers suggestions for improving the performance of the SQL statement. The recommendations are based on actual, not potential, object statistics. You may need to scroll with the DOWN primary command to view this section.

Executing the SQL Command

When you execute the SQL primary command, a temporary data set containing the SQL source is created. An ISPF/PDF edit session is initiated enabling you to edit the SQL. If you want to save the data set, you must use the ISPF/PDF CREATE or COPY command before exiting the window.

SQL Source — After Executing the SQL Command

When you press END to exit the window, the SQL is automatically re-explained and the SQL Analysis Summary screen displays (SQL Analysis Summary Screen — After a Re-explain).

The modified SQL is inserted directly after the original statement. The same DBRM/Package name and Statement number are used. The new Db2 cost value and access type are displayed. The new row is identified by an asterisk (*) in front of the DBRM/Package name, enabling you to compare the results of the changes immediately.

Important

In the modified SQL, the host variables are always substituted with parameter markers prior to the re-explain. This may affect the resulting Db2 cost.

SQL Analysis Summary Screen — After a Re-explain