Using package cache information and db2advis to look for performance improvement opportunities

The Design Advisor can analyze SQL statements to make recommendations for how to improve database performance. You can use statements from the package cache (including statements captured by the package cache event monitor) as input to the Design Advisor to identify changes you can make to improve the performance for a given workload, or even for all statements run between two points in time.

Before you begin

The CREATE EVENT MONITOR statement requires a table space with a page size of at least 8 K to store the unformatted event (UE) table produced by the event monitor. Unless a table space is explicitly named in the CREATE EVENT MONITOR statement, the default table space for the database is used.
You must have created the explain tables required by the Design Advisor.

About this task

This task shows how you can use the package cache event monitor to track all work done on the system between two points in time, and then use the db2advis command to analyze high-cost statements that were run during that period. The output of the db2advis command suggests adjustments or changes you can make to your database to improve its performance, based on the statements run while the package cache event monitor was active. Using the package cache event monitor to capture these statements is useful if the statements in question are no longer in the package cache.

Restrictions

In this particular example, the length of the analyzed statements is limited to 3000 characters. This limitation is due to the use of the GROUP BY clause used in the statement, which cannot be used with LOB values, such as the stmt_text monitor element.

Procedure

Create a package cache event monitor to capture statements as they are removed (evicted) from the package cache.
For example, to create an event monitor called TRACKSTMTS, you could use the following SQL:
```
CREATE EVENT MONITOR TRACKSTMTS FOR PACKAGE CACHE WRITE TO UNFORMATTED EVENT TABLE
```
This statement creates a package cache event monitor that writes to a UE table with the same name as the event monitor, TRACKSTMTS.

By default, all statements evicted from the package cache are captured by the package cache event monitor. To limit the amount of information collected, you can specify options as part of the CREATE EVENT MONITOR statement that restrict the information collected. See the documentation for the CREATE EVENT MONITOR (package cache) statement for more information.
Next, activate the event monitor:
```
SET EVENT MONITOR TRACKSTMTS STATE 1
```
Note: By default, this event monitor starts automatically upon database activation, because the AUTOSTART option is applied by default. However, because this event monitor is being created in an already-active database, you must use the SET EVENT MONITOR command to start it manually.
Connect to the database and run whichever statements, workload or applications for which you are interested in doing performance analysis. You can collect as much information as you like. However, this type of performance tuning works best when you have applications or workloads that run on a regular basis; otherwise adjustments you make for previously executed statements might not have any impact on statements that run in the future.
When you are finished collecting data, deactivate the event monitor:
```
SET EVENT MONITOR TRACKSTMTS STATE 0
```
Extract the data from the UE table that was populated by the event monitor using the EVMON_FORMAT_UE_TO_TABLES procedure.
```
CALL EVMON_FORMAT_UE_TO_TABLES 
    ('PKGCACHE', NULL, NULL, NULL, NULL, NULL, NULL, -1, 
     'SELECT * FROM TRACKSTMTS')
```
This procedure creates two relational tables from the data collected by the package cache event monitor:
- PKGCACHE_EVENT
- PCKCACHE_METRICS
The first table contains the most frequently used monitor elements and metrics associated with each event captured. The second contains detailed metrics for each event.
Note: The values in the columns of PKGCACHE_METRICS can also be found in the XML document contained in the METRICS column of the PKGCACHE_EVENT table. They are provided in the PKGCACHE_METRICS table for more convenient, column-oriented access.

Query the output from the event monitor to determine which statements took the longest time to run. In this example, statement execution time (stmt_exec_time - Statement execution time monitor element) is the time-spent monitor element used to determine overall cost. This monitor element is summed across all database partitions.

Tip: Save the output from the query into a text file. You will us this file in the next step.

WITH STMTS AS
(
  SELECT SUM(TOTAL_STMT_EXEC_TIME)/SUM(TOTAL_NUM_COORD_EXEC_WITH_METRICS) AS AVG_TIME_PER_EXEC,
         STMT_TEXT, SUM(NUM_EXECUTIONS) AS NUM_EXECUTIONS, STMT_TYPE_ID
  FROM (
        (
         SELECT    SUM(STMT_EXEC_TIME) AS TOTAL_STMT_EXEC_TIME,
                   SUM(NUM_COORD_EXEC_WITH_METRICS) AS TOTAL_NUM_COORD_EXEC_WITH_METRICS,
                   SUM(NUM_COORD_EXEC) AS NUM_EXECUTIONS,
                   VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT,
                   STMT_TYPE_ID
         FROM      PKGCACHE_EVENT AS E, PKGCACHE_METRICS AS M 
         WHERE     E.XMLID = M.XMLID 
          AND      NUM_COORD_EXEC_WITH_METRICS > 0
         GROUP BY  VARCHAR(STMT_TEXT, 3000),STMT_TYPE_ID
         ORDER BY TOTAL_NUM_COORD_EXEC_WITH_METRICS DESC
         FETCH FIRST 50 ROWS ONLY
        )
        UNION ALL
        (
         SELECT    SUM(STMT_EXEC_TIME) AS TOTAL_STMT_EXEC_TIME,
                   SUM(NUM_COORD_EXEC_WITH_METRICS) AS TOTAL_NUM_COORD_EXEC_WITH_METRICS,
                   SUM(NUM_COORD_EXEC) AS NUM_EXECUTIONS,
                   VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT,
                   STMT_TYPE_ID
         FROM      TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T
         WHERE     NUM_COORD_EXEC_WITH_METRICS > 0
         GROUP BY  VARCHAR(STMT_TEXT, 3000),STMT_TYPE_ID
         ORDER BY TOTAL_NUM_COORD_EXEC_WITH_METRICS DESC
         FETCH FIRST 50 ROWS ONLY
        )
       ) AS Q_UA 
  GROUP BY STMT_TEXT, STMT_TYPE_ID
)
SELECT      '--# SET FREQUENCY ' || NUM_EXECUTIONS || X'0A' || STMT_TEXT || ';'
FROM        STMTS  WHERE STMT_TYPE_ID LIKE 'DML, Select%' OR STMT_TYPE_ID LIKE 'DML, Insert%' 1 
ORDER BY    AVG_TIME_PER_EXEC DESC
FETCH FIRST 50 ROWS ONLY;

In the preceding sample statement, both the data from the package cache event monitor and the in-memory information from the MON_GET_PKG_CACHE_STMT table function are retrieved. Looking at both data sets lets you see data for statements evicted from the package cache, as well as statements that still exist in the package cache. Doing so assures that when you evaluate which statements are costly to run that you also include statements not yet evicted from the cache. In each case, the query retrieves the top 50 statements from both the active package cache, and the package cache event monitor, based on the number of times the statements ran. Then, from these statements, the top 50 SELECT or INSERT statements are chosen 1 based on the average length of time the statements ran for.

Note: You can choose from a number of monitor elements to use when determining which statements are costly to run. In this example, statement execution time is used. This measurement includes shows the amount of time spent in execution by all members and agents executing this section, and includes things like wait time. You might instead choose to use CPU time (total_cpu_time - Total CPU time monitor element), which reports only the time spent by the CPU processing the statement. You could also choose from many of the other time-spent elements returned by the package cache event monitor. See Information written to relational tables by EVMON_FORMAT_UE_TO_TABLES for a package cache event monitor or Information written to XML for a package cache event monitor for more information about which monitor elements you can choose from.

In addition, the query presents the output in the --# SET FREQUENCY format the Design Advisor uses for its analysis.

The preceding query returns results like the ones that follow:

----------------------------------------------------------------------------------------------------------------------------
--# SET FREQUENCY 1                                                                                               
WITH STMTS AS ( SELECT SUM(TOTAL_STMT_EXEC_TIME)/SUM(TOTAL_NUM_COORD_EXEC_WITH_METRICS) AS AVG_TIME_PER_EXEC, STMT
--# SET FREQUENCY 2                                                                                               
WITH STMTS AS ( SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT
--# SET FREQUENCY 1055                                                                                            
SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2CommonMED' AND DECISION='NOP' AND NAME='CommonPolicy';           
--# SET FREQUENCY 99                                                                                              
SELECT CREATOR, NAME, CTIME FROM SYSIBM.SYSTABLES WHERE TYPE='T' OR TYPE='S' OR TYPE='N' WITH UR;                 
--# SET FREQUENCY 1                                                                                               
UPDATE SYSTOOLS.HMON_ATM_INFO SET STATS_LOCK = 'N', REORG_LOCK = 'N';                                             
--# SET FREQUENCY 1                                                                                               
UPDATE SYSTOOLS.HMON_ATM_INFO AS ATM SET STATS_FLAG = 'N', REORG_FLAG = 'N' WHERE (ATM.SCHEMA, ATM.NAME) IN   (SEL
--# SET FREQUENCY 1                                                                                               
SELECT POLICY FROM SYSTOOLS.POLICY WHERE MED='DB2TableMaintenanceMED' AND DECISION='TableRunstatsDecision' AND NAM
--# SET FREQUENCY 83                                                                                              
WITH JTAB(JSCHEMA,JNAME) AS (VALUES(TABLE_SCHEMA(CAST(? AS varchar(128)), CAST(? AS varchar(128))), TABLE_NAME (CA
--# SET FREQUENCY 122                                                                                             
WITH VTYPED (NAME, SCHEMA) AS (VALUES(TABLE_NAME (CAST(? AS varchar(128)), CAST(? AS varchar(128))), TABLE_SCHEMA(
--# SET FREQUENCY 1210                                                                                            
SELECT COLNAME, TYPENAME FROM  SYSCAT.COLUMNS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS';                    
--# SET FREQUENCY 105                                                                                             
SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='HMON_ATM_INFO' AND  TABSCHEMA='SYSTOOLS';                       
--# SET FREQUENCY 104                                                                                             
DELETE FROM SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NOT EXISTS ( SELECT * FROM SYSIBM.SYSTABLES AS IBM WHERE ATM.NAME 
--# SET FREQUENCY 1118                                                                                            
VALUES(SUBSTR(:H00003      ,:H00014,:H00015 ))         INTO :H00009:H00017    ;                                   
--# SET FREQUENCY 274                                                                                             
INSERT INTO "ASRISK"."PKGCACHE_EVENT"("EVENT_ID","XMLID","EVENT_TYPE","EVENT_TIMESTAMP","MEMBER","SECTION_TYPE","I
--# SET FREQUENCY 1                                                                                               
SELECT IBM.TID, IBM.FID FROM SYSIBM.SYSTABLES AS IBM, SYSTOOLS.HMON_ATM_INFO AS ATM WHERE ATM.STATS_FLAG <> 'Y' AN
--# SET FREQUENCY 115                                                                                             
VALUES(SUBSTR(CAST(? AS CLOB(162)),CAST(? AS INTEGER),CAST(? AS INTEGER)));                                       
--# SET FREQUENCY 8227   

⋮
                         
-# SET FREQUENCY 532                                                                                             
SELECT TBNAME, TBCREATOR FROM "ASRISK  ".SYSINDEXES WHERE NAME = 'INDCOLUMNS01' AND CREATOR = 'SYSIBM  ';         
--# SET FREQUENCY 105                                                                                             
SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='HMON_COLLECTION' AND  TABSCHEMA='SYSTOOLS';                     
--# SET FREQUENCY 4091                                                                                            
SELECT STATS_LOCK, REORG_LOCK FROM SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? AND CREATE_TIME = ? FOR UP
--# SET FREQUENCY 17100                                                                                           
SELECT CREATE_TIME FROM SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? FOR UPDATE;                          
--# SET FREQUENCY 524                                                                                             
SELECT COUNT(*) FROM "SYSIBM".SYSTABLES WHERE NAME = 'SYSDATAPARTITIONEXPRESSION' AND CREATOR = 'SYSIBM  ' AND TYP
--# SET FREQUENCY 532                                                                                             
SELECT COUNT(*) FROM "SYSIBM".SYSTABLES WHERE NAME = 'SYSCOLUMNS' AND CREATOR = 'SYSIBM  ' AND TYPE = 'S';        
                                                                                                                  
  47 record(s) selected

Note: The lines in the preceding sample output have been truncated for presentation purposes.

Create an input file for the db2advis command using the statements returned by the query in step 6. (For more information about creating input files for the db2advis command, refer to the reference documentation for that command.)
Run the db2advis command using the input file created in step 7.
For example, if the input file you create is called pkgcache_stmts.txt, run a command similar to this:
```
db2advis -d customer -i pkgcache_stmts.txt -m MICP
```
where
- -d CUSTOMER identifies the name of the database for which you are getting recommendations
- -i pkgcache_stmts.txt identifies the name of the input file for db2advis
- -m MICP is a directive to the db2advis command to produce the following recommendations to improve performance:
  
  M
  
  Recommends to create new materialized query tables (MQTs) and indexes on the MQTs. In partitioned database environments, partitioning on MQTs is also recommended.
  
  I
  
  Recommends new indexes. This is the default.
  
  C
  
  Recommends either to convert standard tables to multidimensional clustering (MDC) tables or to create a clustering index on the tables.
  
  P
  
  Recommends to repartition existing tables.

Results

The Design Advisor returns recommendations like ones that follow:

execution started at timestamp 2010-03-16-14.25.57.562000
Using the default table space name USERSPACE1
found [47] SQL statements from the input file
excluding statement [0] from the workload.
excluding statement [1] from the workload.
excluding statement [19] from the workload.
excluding statement [39] from the workload.
Recommending indexes...
Recommending MQTs...
Recommending Multi-Dimensional Clusterings...
Found 19 user defined views in the catalog table
Found [17] candidate MQTs
Getting cost of workload with MQTs
total disk space needed for initial set [   0.159] MB
total disk space constrained to         [  69.215] MB
  2  indexes in current solution
  0  MQTs in current solution
total disk space needed for initial set [   0.024] MB
total disk space constrained to         [ 103.822] MB
No useful Multi-dimensional Clustering dimensions for this workload
 [5651.8281] timerons  (without recommendations)
 [5519.8281] timerons  (with current solution)
 [2.34%] improvement


--
--
-- LIST OF MODIFIED CREATE-TABLE STATEMENTS WITH RECOMMENDED PARTITIONING KEYS AND TABLESPACES AND/OR RECOMMENDED MULTI-DIMENSIONAL CLUSTERINGS
-- ===========================
--  No new partitioning keys or tablespaces are recommended for this workload.


--
--
-- LIST OF RECOMMENDED MQTs
-- ===========================


--
--
-- RECOMMENDED EXISTING MQTs
-- ===========================


--
--
-- UNUSED EXISTING MQTs
-- ============================
-- DROP TABLE "ASRISK  "."ADEFUSR";


--
--
-- RECOMMENDED CLUSTERING INDEXES
-- ============================


--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1],    0.024MB
   CREATE INDEX "ASRISK  "."IDX003161830530000" ON "ASRISK  "."SYSINDEXES"
   ("CREATOR" ASC, "NAME" ASC, "TBCREATOR" ASC, "TBNAME"
   ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
   COMMIT WORK ;


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "SYSTOOLS"."POLICY" FOR SAMPLED DETAILED INDEX "SYSTOOLS"."POLICY_UNQ" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSTOOLS"."HMON_ATM_INFO" FOR SAMPLED DETAILED INDEX "SYSTOOLS"."ATM_UNIQ" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSDATAPARTITIONS" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDDATAPARTITIONS03" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSTABLES" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDTABLES01" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSTABLESPACES" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDTABLESPACES04" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSCOLUMNS" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDCOLUMNS01" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSINDEXES" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDINDEXES02" ;
-- COMMIT WORK ;
-- RUNSTATS ON TABLE "SYSIBM  "."SYSTRIGGERS" FOR SAMPLED DETAILED INDEX "SYSIBM  "."INDTRIGGERS02" ;
-- COMMIT WORK ;


--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "ASRISK  "."PKGCACHE_EVENT_IND1";
-- ===========================
--

-- ====ADVISOR DETAILED XML OUTPUT=============
-- ==(Benefits do not include clustering recommendations)==
  ⋮

Note: The output from the Design Advisor has been truncated for presentation purposes.

What to do next

Use the output from the Design Advisor to help when deciding what changes to make to your database to improve performance.