Topic
No replies
cbuzzo
cbuzzo
5 Posts
ACCEPTED ANSWER

Pinned topic Using Dynamic Statement Cache in DB2 for z/Os

‏2013-07-16T20:03:24Z |

 

There are a number of explain tables that can be used for diagnose or performance purposes. One of them is DSN_STATEMENT_CACHE_TABLE.

You can find the sql statements for creating an instance of this table and other objects in members DSNTESC and DSNTIJOS of the SDSNSAMP library.

 

This is the structure of the table according to IBM documentation:

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.perf%2Fsrc%2Ftpc%2Fdb2z_dsnstatementcachetable.htm

 

Once you have an instance of this table - remember that you can have as many as you want provided they have different qualifiers!, you can start using it. Your current SQLID determines the qualifier of DSN_STATEMENT_CACHE_TABLE your going to use.

 

Let's see some important steps to take into account before retrieving information from the table:

 

1)    Activation of performance trace (IFCID 318)

 

You need to start a performance trace with IFCID 318 in order to have the table DSN_STATEMENT_CACHE_TABLE populated with useful information after executing EXPLAIN STMTCACHE ALL.

 

Here is an example of the command to start the trace:

 

-START TRACE(PERFM) CLASS(30) IFCID(318)

 

Why is this trace so important? Well, if you do not start this trace, you can only get the sql text (STMT_TEXT column) of the statements  in cache, but the values for the rest of the numeric columns that account for performance statistics will remain null (or zero in fact). So, if you want to know how many times a sql statement was executed, or the number of getpage operations or index scans the statement performed, you need to start the trace. Statistics reset when the trace is stopped!

 

The column STAT_TS in DSN_STATEMENT_CACHE_TABLE contains the timestamp when IFCID 318 is started.

 

 

 

2)    ZPARM Parameters

 

CACHEDYN = YES is necessary for the dynamic statements to be inserted in the global cache.

 

EDMSTMTC: used to specify the EDM Statement Pool size.

 

MAXKEEPD: specifies the maximum number of prepared, dynamic SQL statements that are to be saved past a commit point. This parameter applies to applications that run with the KEEPDYNAMIC(YES) bind option.

 

 

3)    Take a snapshot of the statement cache into DSN_STATEMENT_CACHE_TABLE

 

Dynamic statements are cached in the EDM statement pool wich belongs to the EDM storage and resides entirely above the bar, so is not critical as a virtual storage constraint.

 

EXPLAIN STMTCACHE ALL: This is the sql statement that populates the table DSN_STATEMENT_CACHE_TABLE. It inserts one row for each entry in the dynamic statement cache.

 

The column EXPLAIN_TS in DSN_STATEMENT_CACHE_TABLE contains the time when EXPLAIN STMTCACHE ALL was executed. So, executing EXPLAIN STMTCACHE ALL is like taking a snapshot of your statement cache.

 

You can track a specific sql statement (filter by column STMT_ID) along different cache snapshots (filter by column EXPLAIN_TS), and calculate the activity that took place between both snapshots for the statement. As counters only increase with time (as long as the performance trace is active), you have to substract the values of the first snapshot to the values of the second one to get the net activity that took place for that statement for the period of time between both snapshots.

 

For example, the query below gets the net activity for all staments in cache that had some activity between cache snapshots (elapsed time of 24 hours in this case):

 

-- Get net activity for all statements between two cache snapshots
SELECTS2.STMT_ID,
       S2.PROGRAM_NAME,
       S2.PRIMAUTH,
       S2.CURSQLID,
       S2.BIND_QUALIFIER,
       S2.BIND_ISO,
       S2.SCHEMA,
       S2.STAT_TS,
       S2.CACHED_TS,
       S1.EXPLAIN_TS EXPLAN_TS_1,
       S2.EXPLAIN_TS EXPLAN_TS_2,
       S2.STAT_EXEC
-S1.STAT_EXEC DIF_STAT_EXEC,
       S2.STAT_GPAG
-S1.STAT_GPAG DIF_STAT_GPAG,
       S2.STAT_SYNR
-S1.STAT_SYNR DIF_STAT_SYNR,
       S2.STAT_WRIT
-S1.STAT_WRIT DIF_STAT_WRIT,
       S2.STAT_EROW
-S1.STAT_EROW DIF_STAT_EROW,
       S2.STAT_PROW
-S1.STAT_PROW DIF_STAT_PROW,
       S2.STAT_SORT
-S1.STAT_SORT DIF_STAT_SORT,
       S2.STAT_INDX
-S1.STAT_INDX DIF_STAT_INDX,
       S2.STAT_RSCN
-S1.STAT_RSCN DIF_STAT_RSCN,
       S2.STAT_PGRP
-S1.STAT_PGRP DIF_STAT_PGRP,
       S2.STAT_ELAP
-S1.STAT_ELAP DIF_STAT_ELAP,
       S2.STAT_CPU
-S1.STAT_CPU DIF_STAT_CPU,
       S2.STAT_SUS_SYNIO
-S1.STAT_SUS_SYNIO DIF_STAT_SUS_SYNIO,
       S2.STAT_SUS_LOCK
-S1.STAT_SUS_LOCK DIF_STAT_SUS_LOCK,
       S2.STAT_SUS_SWIT
-S1.STAT_SUS_SWIT DIF_STAT_SUS_SWIT,
       S2.STAT_SUS_GLCK
-S1.STAT_SUS_GLCK DIF_STAT_SUS_GLCK,
       S2.STAT_SUS_OTHR
-S1.STAT_SUS_OTHR DIF_STAT_SUS_OTHR,
       S2.STAT_SUS_OTHW
-S1.STAT_SUS_OTHW DIF_STAT_SUS_OTHW,
       S2.STAT_RIDLIMT
-S1.STAT_RIDLIMT DIF_STAT_RIDLIMT,
       S2.STAT_RIDSTOR
-S1.STAT_RIDSTOR DIF_STAT_RIDSTOR,
      
CAST(S2.STMT_TEXT AS VARCHAR(4000)) STMT_TEXT
FROMDSN_STATEMENT_CACHE_TABLE S2
    
LEFTOUTER JOIN DSN_STATEMENT_CACHE_TABLE S1 ONS2.STMT_ID =S1.STMT_ID
WHERE -- Date of the first snapshot

      CAST(S1.EXPLAIN_TS AS DATE) = CAST('6/18/2013' AS DATE)

      -- Time of the first snapshot
      AND CAST(S1.EXPLAIN_TS AS TIME) = CAST('09:01:12' AS TIME)     

      -- Date of the second snapshot

      AND CAST(S2.EXPLAIN_TS AS DATE) = CAST('6/19/2013' AS DATE)

      -- Time of the second snapshot
      AND CAST(S2.EXPLAIN_TS AS TIME) = CAST('09:03:24' AS TIME)

      -- Snapshots are different and S1 preceeds S2
      ANDS1.EXPLAIN_TS <S2.EXPLAIN_TS                  

      -- Required performance trace active before first snapshot

      ANDS1.EXPLAIN_TS >=S1.STAT_TS  

      -- Required performance trace active before second snapshot
      ANDS2.EXPLAIN_TS >=S2.STAT_TS

      -- Trace was running continuously before both snapshots    

      ANDS1.STAT_TS =S2.STAT_TS  

      -- Filter only statements that had activity between snapshots                                 

      AND(S2.STAT_EXEC -S1.STAT_EXEC >0
          
ORS2.STAT_GPAG -S1.STAT_GPAG >0
          
ORS2.STAT_SYNR -S1.STAT_SYNR >0
          
ORS2.STAT_WRIT -S1.STAT_WRIT >0
          
ORS2.STAT_EROW -S1.STAT_EROW >0
           
ORS2.STAT_PROW -S1.STAT_PROW >0
          
ORS2.STAT_SORT -S1.STAT_SORT >0
          
ORS2.STAT_INDX -S1.STAT_INDX >0
          
ORS2.STAT_RSCN -S1.STAT_RSCN >0
          
ORS2.STAT_PGRP -S1.STAT_PGRP >0
          
ORS2.STAT_ELAP -S1.STAT_ELAP >0
          
ORS2.STAT_CPU -S1.STAT_CPU >0
          
ORS2.STAT_SUS_SYNIO -S1.STAT_SUS_SYNIO >0
          
ORS2.STAT_SUS_LOCK -S1.STAT_SUS_LOCK >0
          
ORS2.STAT_SUS_SWIT -S1.STAT_SUS_SWIT >0
          
ORS2.STAT_SUS_GLCK -S1.STAT_SUS_GLCK >0
          
ORS2.STAT_SUS_OTHR -S1.STAT_SUS_OTHR >0
          
ORS2.STAT_SUS_OTHW -S1.STAT_SUS_OTHW >0
          
ORS2.STAT_RIDLIMT -S1.STAT_RIDLIMT >0
          
ORS2.STAT_RIDSTOR -S1.STAT_RIDSTOR >0)

WITH UR

 

 

The only drawback of the above query is that some statements may have been flushed out of cache by the time of the second snapshot. This can be caused by:

 

-          Drop or Alter of an object

-          Revoke authorization

-          Runstats

-          LRU: Least recently used (take a look at EDMSTMTC parameter)

 

 

Below there is another query to get the average of all statistics columns for a snapshot:

 

-- Get average values for the different statistics columns of a cache snapshot

SELECTSTMT_ID,
       PROGRAM_NAME,
       PRIMAUTH,
       CURSQLID,
       BIND_QUALIFIER,
       BIND_ISO,
       SCHEMA,
       STAT_TS,
       CACHED_TS,
       EXPLAIN_TS,
       STAT_EXEC,
      
CAST((STAT_GPAG /STAT_EXEC) AS NUMERIC(30,2)) AVG_GPAG,
      
CAST((STAT_SYNR /STAT_EXEC) AS NUMERIC(30,2)) AVG_SYNR,
      
CAST((STAT_WRIT /STAT_EXEC) AS NUMERIC(30,2)) AVG_WRIT,
      
CAST((STAT_EROW /STAT_EXEC) AS NUMERIC(30,2)) AVG_EROS,
      
CAST((STAT_PROW /STAT_EXEC) AS NUMERIC(30,2)) AVG_PROW,
      
CAST((STAT_SORT /STAT_EXEC) AS NUMERIC(30,2)) AVG_SORT,
      
CAST((STAT_INDX /STAT_EXEC) AS NUMERIC(30,2)) AVG_INDX,
      
CAST((STAT_RSCN /STAT_EXEC) AS NUMERIC(30,2)) AVG_RSCN,
      
CAST((STAT_PGRP /STAT_EXEC) AS NUMERIC(30,2)) AVG_PGRP,
      
CAST((STAT_ELAP /STAT_EXEC) AS NUMERIC(30,2)) AVG_ELAP,
      
CAST((STAT_CPU /STAT_EXEC) AS NUMERIC(30,2)) AVG_CPU,
      
CAST((STAT_SUS_SYNIO /STAT_EXEC) AS NUMERIC(30,2)) AVG_SUS_SYNIO,
      
CAST((STAT_SUS_LOCK /STAT_EXEC) AS NUMERIC(30,2)) AVG_SUS_LOCK,
      
CAST((STAT_SUS_SWIT /STAT_EXEC) AS NUMERIC(30,2)) AVG_SUS_SWIT,
      
CAST((STAT_SUS_GLCK /STAT_EXEC) AS NUMERIC(30,2)) AVG_SUS_GLCK,
      
CAST((STAT_SUS_OTHR /STAT_EXEC) AS NUMERIC(30,2)) AVG_SUS_OTHR,
      
CAST((STAT_SUS_OTHW /STAT_EXEC) AS NUMERIC(30,2)) AVG_SUS_OTHW,
      
CAST((STAT_RIDLIMT /STAT_EXEC) AS NUMERIC(30,2)) AVG_RIDLIMT,
      
CAST((STAT_RIDSTOR /STAT_EXEC) AS NUMERIC(30,2)) AVG_RIDSTOR,
      
CAST(STMT_TEXT AS VARCHAR(2000)) STMT_TEXT
FROMDSN_STATEMENT_CACHE_TABLE
WHERE -- Date of the snapshot

      CAST(EXPLAIN_TS AS DATE) = CAST('6/19/2013' AS DATE)

      -- Time of the snapshot
      AND CAST(EXPLAIN_TS AS TIME) = CAST('15:25:12' AS TIME)     

      -- Required performance trace active before snapshot

      ANDEXPLAIN_TS >=STAT_TS

      -- This condition is necessary to avoid division by zero!

      ANDSTAT_EXEC >0                                       

 

 

Therea are also other EXPLAIN options like EXPLAIN STMTCACHE STMTID and EXPLAIN STMTCACHE STMTTOKEN that populate other tables besides DSN_STATEMENT_CACHE_TABLE, and that can be of valuable help for specific sql statements analysis, but they are out of the scope of this document.

 

 

Recommendations:

 

- Keep an eye on the number of rows of DSN_STATEMENT_CACHE.

Every snapshot can add thousands of rows to the table, so I recommend to delete rows belonging to old snapshots from time to time.

 

 

Below is a query to identify the snapshots in the table and the statements (rows) that each one has. Note that the rows of a snapshot have the same value for EXPLAIN_TS column.

 

SELECTEXPLAIN_TS, COUNT(*) TOTALROWS

FROM DSN_STATEMENT_CACHE_TABLE

GROUP BYEXPLAIN_TS

ORDER BYEXPLAIN_TS

WITH UR

 

Delete a snapshot from time to time:

 

DELETE FROMDSN_STATEMENT_CACHE_TABLE
WHERE -- Date of the snapshot

      CAST(EXPLAIN_TS AS DATE) = CAST('6/18/2013' AS DATE)

      -- Time of the snapshot
      AND CAST(EXPLAIN_TS AS TIME) = CAST('09:01:12' AS TIME)     

 

- As a consequence of the previous recommendation, it is also advisable to REORG the tablespace that contains DSN_STATEMENT_CACHE_TABLE, specially after deleting snapshots.

 

Updated on 2013-08-15T13:36:50Z at 2013-08-15T13:36:50Z by cbuzzo