Investigating query performance using Explain information obtained from a section

You can use the explain facility to examine the access plan for a specific statement as it will actually run (or as it was run) by generating the access plan from the section for the statement itself. By contrast, using the EXPLAIN statement creates the access plan by recompiling the statement. The resulting access plans from each of these two methods of creating access plans can be different. For example, if the statement in a section was compiled, say, 2 hours ago, then the access plan it uses might be different than the one produced by running the EXPLAIN statement against the statement.

If you have activity event monitor information available, you can generate the access plan for the section after it has run using the EXPLAIN_FROM_ACTIVITY procedure. (If section actuals are being collected, you can also view this information along with the estimates generated by the explain facility in the access plan. See Capturing and accessing section actuals for more information.)

If there is no activity event monitor information available for the statement, you can use the EXPLAIN_FROM_SECTION procedure to generate the access plan for the statement as it will run based on the section stored in the package cache. This topic shows how to use EXPLAIN_FROM_SECTION to view access plan information for a statement based on section information in the package cache.

Before you begin

This task assumes that you have already created the explain tables required by the explain facility.

About this task

In this topic, assume that you want to use the explain facility to examine the most CPU-intensive statement in the package cache.

Procedure

The first part of this procedure shows how to identify the most CPU-intensive statement. Then, it shows how to use the EXPLAIN_FROM_SECTION procedure to view the access plan information for that statement as it will actually run.

  1. Identify the statement that using the most processor time:
    SELECT SECTION_TYPE,
        CASE 
          WHEN SUM(NUM_COORD_EXEC_WITH_METRICS) > 0 THEN
               SUM(TOTAL_CPU_TIME)/SUM(NUM_COORD_EXEC_WITH_METRICS)
          ELSE
               0
        END as  AVG_CPU_TIME,
        EXECUTABLE_ID,
        VARCHAR(STMT_TEXT, 200) AS TEXT      
        FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T
        WHERE T.NUM_EXEC_WITH_METRICS <> 0 AND STMT_TYPE_ID LIKE 'DML%'
        GROUP BY SECTION_TYPE, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 200)
        ORDER BY AVG_CPU_TIME DESC
    
    The preceding SQL is written to avoid division by 0 when calculating the average processor time across members. It also examines DML statements only, since the explain facility does not operate on DDL statements.
    The results of this query are as follows:
    SECTION_TYPE AVG_CPU_TIME         EXECUTABLE_ID                                                       TEXT                                              
    ------------ -------------------- ------------------------------------------------------------------- --------------------------------------------------
    D                          250000 x'01000000000000005F0000000000000000000000020020101108135629359000' select cust_last_name, cust_cc_number, cust_intere
    SQL0445W  Value "SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2TableMainte" 
    has been truncated.  SQLSTATE=01004
    
    D                           15625 x'01000000000000001B0000000000000000000000020020101108135017625000' UPDATE SYSTOOLS.HMON_ATM_INFO SET STATS_LOCK = 'N'
    D                           15625 x'0100000000000000200000000000000000000000020020101108135018296001' UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET ATM.STATS
    D                           15625 x'0100000000000000210000000000000000000000020020101108135018312001' UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET STATS_FLA
    D                            7812 x'0100000000000000150000000000000000000000020020101108135016984000' SELECT COLNAME, TYPENAME FROM  SYSCAT.COLUMNS WHER
    D                               0 x'0100000000000000160000000000000000000000020020101108135017156000' SELECT TRIGNAME FROM  SYSCAT.TRIGGERS WHERE TABNAM
    D                               0 x'0100000000000000190000000000000000000000020020101108135017484000' SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='
    D                               0 x'01000000000000001A0000000000000000000000020020101108135017500000' SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='
    D                               0 x'01000000000000001C0000000000000000000000020020101108135017750002' SELECT CREATOR, NAME, CTIME FROM SYSIBM.SYSTABLES 
    D                               0 x'01000000000000001D0000000000000000000000020020101108135017828001' SELECT CREATE_TIME FROM SYSTOOLS.HMON_ATM_INFO WHE
    D                               0 x'01000000000000001E0000000000000000000000020020101108135018000001' DELETE FROM SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NO
    D                               0 x'01000000000000001F0000000000000000000000020020101108135018093000' SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2T
    D                               0 x'0100000000000000220000000000000000000000020020101108135018328001' SELECT IBM.TID, IBM.FID FROM SYSIBM.SYSTABLES AS I
    D                               0 x'0100000000000000230000000000000000000000020020101108135018343001' SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2C
    
      14 record(s) selected with 1 warning messages printed.
  2. Based on the output of the preceding query, use the EXPLAIN_FROM_SECTION procedure to generate explain information from the section for the most CPU-intensive statement:
    CALL EXPLAIN_FROM_SECTION (x'01000000000000005F0000000000000000000000020020101108135629359000' ,'M', NULL, 0, NULL, ?, ?, ?, ?, ? )
    
    The output of the EXPLAIN_FROM_SECTION procedure is as follows:
      Value of output parameters
      --------------------------
      Parameter Name  : EXPLAIN_SCHEMA
      Parameter Value : DB2DOCS  
    
      Parameter Name  : EXPLAIN_REQUESTER
      Parameter Value : DB2DOCS  
    
      Parameter Name  : EXPLAIN_TIME
      Parameter Value : 2010-11-08-13.57.52.984001
    
      Parameter Name  : SOURCE_NAME
      Parameter Value : SQLC2H21
    
      Parameter Name  : SOURCE_SCHEMA
      Parameter Value : NULLID  
    
      Parameter Name  : SOURCE_VERSION
      Parameter Value : 
  3. You can now examine the explain information, either by examining the explain tables using SQL, or using the db2exfmt command to format the information for easier reading.
    For example, running db2exfmt -d gsdb -e db2docs -w 2010-11-08-13.57.52.984001 -n SQLC2H21 -s NULLID -t -#0 against the explain information collected from the previous step generates the following output:
    Connecting to the Database.
    Db2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
    Licensed Material - Program Property of IBM
    IBM DATABASE 2 Explain Table Format Tool
    
    
    
    ******************** EXPLAIN INSTANCE ********************
    
    DB2_VERSION:       09.07.2
    SOURCE_NAME:       SQLC2H21
    SOURCE_SCHEMA:     NULLID  
    SOURCE_VERSION:      
    EXPLAIN_TIME:      2010-11-08-13.57.52.984001
    EXPLAIN_REQUESTER: DB2DOCS  
    
    Database Context:
    ----------------
       Parallelism:          None
       CPU Speed:            8.029852e-007
       Comm Speed:           100
       Buffer Pool size:     21418
       Sort Heap size:       6590
       Database Heap size:   1196
       Lock List size:       21386
       Maximum Lock List:    97
       Average Applications: 1
       Locks Available:      663821
    
    Package Context:
    ---------------
       SQL Type:           Dynamic
       Optimization Level: 5
       Blocking:           Block All Cursors
       Isolation Level:    Cursor Stability
    
    
    
    ---------------- STATEMENT 1  SECTION 201 ----------------
       QUERYNO:       0
       QUERYTAG:      CLP                 
       Statement Type:   Select
       Updatable:        No
       Deletable:        No
       Query Degree:     1
    
    Original Statement:
    ------------------
    select cust_last_name, cust_cc_number, cust_interest_code 
    from gosalesct.cust_crdt_card C, gosalesct.cust_customer D, 
            gosalesct.cust_interest E 
    where C.cust_code=d.cust_code AND c.cust_code=e.cust_code 
    group by d.cust_last_name,    c.cust_cc_number, e.cust_interest_code 
    order by d.cust_last_name ASC, c.cust_cc_number DESC, e.cust_interest_code 
            ASC
    
    
    Optimized Statement:
    -------------------
    SELECT Q5.CUST_LAST_NAME AS "CUST_LAST_NAME", Q5.CUST_CC_NUMBER AS 
            "CUST_CC_NUMBER", Q5.CUST_INTEREST_CODE AS "CUST_INTEREST_CODE" 
    FROM 
       (SELECT Q4.CUST_LAST_NAME, Q4.CUST_CC_NUMBER, Q4.CUST_INTEREST_CODE 
       FROM 
          (SELECT Q2.CUST_LAST_NAME, Q3.CUST_CC_NUMBER, Q1.CUST_INTEREST_CODE 
          FROM GOSALESCT.CUST_INTEREST AS Q1, GOSALESCT.CUST_CUSTOMER AS Q2, 
                  GOSALESCT.CUST_CRDT_CARD AS Q3 
          WHERE (Q3.CUST_CODE = Q1.CUST_CODE) AND (Q1.CUST_CODE = Q2.CUST_CODE)) 
               AS Q4 
       GROUP BY Q4.CUST_INTEREST_CODE, Q4.CUST_CC_NUMBER, Q4.CUST_LAST_NAME) AS 
            Q5 
    ORDER BY Q5.CUST_LAST_NAME, Q5.CUST_CC_NUMBER DESC, Q5.CUST_INTEREST_CODE
    
    Explain level:    Explain from section
    
    Access Plan:
    -----------
       Total Cost:       1255.29
       Query Degree:      1
    
                                Rows 
                               RETURN
                               (   1)
                                Cost 
                                 I/O 
                                 |
                                31255 
                               GRPBY 
                               (   2)
                               1255.29 
                                 NA 
                                 |
                                31255 
                               TBSCAN
                               (   3)
                               1249.02 
                                 NA 
                                 |
                                31255 
                               SORT  
                               (   4)
                               1242.74 
                                 NA 
                                 |
                                31255 
                               ^HSJOIN
                               (   5)
                               1134.96 
                                 NA 
                       /---------+---------\
                    31255                   31255 
                   HSJOIN                  TBSCAN
                   (   6)                  (   9)
                   406.871                 716.136 
                     NA                      NA 
              /------+-------\               |
           31255              31255         31255 
          TBSCAN             IXSCAN   TABLE: GOSALESCT
          (   7)             (   8)     CUST_CUSTOMER
          235.505            159.488         Q2
            NA                 NA 
            |                  |
           31255               -1 
     TABLE: GOSALESCT    INDEX: SYSIBM  
      CUST_CRDT_CARD   SQL101108113609000
            Q3                 Q1
    
    
    ⋮
    Objects Used in Access Plan:
    ---------------------------
    
       Schema: SYSIBM  
       Name:    SQL101108113609000
       Type:    Index
             Last statistics update:    2010-11-08-13.29.58.531000
             Number of rows:       -1
             Number of buffer pool pages:    -1
             Distinct row values:       Yes
             Tablespace name:       GOSALES_TS        
             Tablespace overhead:       7.500000
             Tablespace transfer rate:    0.060000
             Prefetch page count:       32
             Container extent page count:    32
             Index clustering statistic:    1.000000
             Index leaf pages:       37
             Index tree levels:       2
             Index full key cardinality:    31255
             Base Table Schema:       GOSALESCT
             Base Table Name:       CUST_INTEREST
             Columns in index:
                CUST_CODE(A)
                CUST_INTEREST_CODE(A)
    
       Schema: GOSALESCT
       Name:    CUST_CRDT_CARD
       Type:    Table
             Last statistics update:    2010-11-08-11.59.58.531000
             Number of rows:       31255
             Number of buffer pool pages:    192
             Distinct row values:       No
             Tablespace name:       GOSALES_TS        
             Tablespace overhead:       7.500000
             Tablespace transfer rate:    0.060000
             Prefetch page count:       32
             Container extent page count:    32
             Table overflow record count:    0
             Table Active Blocks:       -1
             Average Row Compression Ratio:    0
             Percentage Rows Compressed:    0
             Average Compressed Row Size:    0
    
       Schema: GOSALESCT
       Name:    CUST_CUSTOMER
       Type:    Table
             Last statistics update:    2010-11-08-11.59.59.437000
             Number of rows:       31255
             Number of buffer pool pages:    672
             Distinct row values:       No
             Tablespace name:       GOSALES_TS        
             Tablespace overhead:       7.500000
             Tablespace transfer rate:    0.060000
             Prefetch page count:       32
             Container extent page count:    32
             Table overflow record count:    0
             Table Active Blocks:       -1
             Average Row Compression Ratio:    0
             Percentage Rows Compressed:    0
             Average Compressed Row Size:    0
    
    Base Table For Index Not Already Shown:
    ---------------------------------------
    
       Schema: GOSALESCT
       Name:    CUST_INTEREST
             Time of creation:       2010-11-08-11.30.28.203002
             Last statistics update:    2010-11-08-13.29.58.531000
             Number of rows:       31255
             Number of pages:       128
             Number of pages with rows:    124
             Table overflow record count:    0
             Indexspace name:       GOSALES_TS
             Tablespace name:       GOSALES_TS
             Tablespace overhead:       7.500000
             Tablespace transfer rate:    0.060000
             Prefetch page count:       -1
             Container extent page count:    32
    
             Long tablespace name:       GOSALES_TS
    
    (The preceding output has had several lines removed for presentation purposes.)

What to do next

Analyze the explain output to see where there are opportunities to tune the query.