Monitoring page reclaiming between members

When examining where a particular application or statement is spending its time, in addition to the time spent waiting for locks, applications, or statements in a DB2® pureScale® environment might need to wait for a page to become available when it is in use by another member. You can use page reclaiming monitor elements to view the extent to which this type of wait might be affecting throughput on your system.

About this task

To view page reclaiming statistics, use the MON_GET_PAGE_ACCESS_INFO table function. This table function returns object-level information about the extent to which members both request pages currently in use by other members, and the extent to which members release those pages at the request of other members. You can also retrieve the wait times involved.

Procedure

  1. Determine what types of pages you are interested in viewing results for.
    The example that follows retrieves information about the number of times pages were reclaimed for all data and index pages, using the page_reclaims_x and page_reclaims_s monitor elements.
  2. Formulate an SQL statement that uses the MON_GET_PAGE_ACCESS_INFO table function.
    For example, to retrieve information about data and index pages reclaimed for all members, you can construct a statement like the one that follows:
    SELECT MEMBER,
       VARCHAR(TABNAME,30) AS TABLE,
       VARCHAR(OBJTYPE,8) AS OBJTYPE,
       PAGE_RECLAIMS_X,
       PAGE_RECLAIMS_S 
FROM TABLE(MON_GET_PAGE_ACCESS_INFO('DTW','', -2)) 
WHERE PAGE_RECLAIMS_X !=0 OR  PAGE_RECLAIMS_S !=0 
ORDER BY MEMBER ASC, PAGE_RECLAIMS_X ASC
  3. Run the query.
    In this case, the results returned would look like the following example: 
    MEMBER TABLE                          OBJTYPE  PAGE_RECLAIMS_X      PAGE_RECLAIMS_S     
------ ------------------------------ -------- -------------------- --------------------
     0 CUSTOMER                       TABLE                     196                    0
     0 STOCK_1_250                    TABLE                     213                    0
     0 STOCK_1251_1500                TABLE                     237                    0
     0 STOCK_251_500                  TABLE                     239                    0
     0 STOCK_501_750                  TABLE                     245                    0
     0 STOCK_1751_2000                TABLE                     253                    0
     0 STOCK_2001_2250                TABLE                     254                    0
     0 STOCK_751_1000                 TABLE                     259                    0
     0 STOCK_1501_1750                TABLE                     269                    0
     0 STOCK_2251_2500                TABLE                     274                    0
     0 STOCK_251_500                  INDEX                     276                 2934
     0 STOCK_1001_1250                TABLE                     280                    0
     0 STOCK_1501_1750                INDEX                     284                 3070
     0 STOCK_501_750                  INDEX                     294                 3029
     0 STOCK_1_250                    INDEX                     296                 2916
     0 STOCK_751_1000                 INDEX                     301                 3056
     1 STOCK_1001_1250                TABLE                     247                    0
     1 STOCK_501_750                  TABLE                     255                    0
     1 STOCK_751_1000                 TABLE                     257                    0
     1 STOCK_1501_1750                TABLE                     257                    0
     1 STOCK_251_500                  INDEX                     287                 2921
     1 STOCK_1_250                    INDEX                     292                 2916
     1 STOCK_751_1000                 INDEX                     316                 3190
     1 STOCK_501_750                  INDEX                     319                 2956
     1 ORDERS                         INDEX                   42434                 1416
     1 ORDER_LINE                     INDEX                  116107                 3731
     2 CUSTOMER                       TABLE                     180                    0
     2 STOCK_2001_2250                TABLE                     221                    0
     .                                  .                        .                     .
     .                                  .                        .                     .
     .                                  .                        .                     .
     2 STOCK_1501_1750                TABLE                     240                    0
     2 STOCK_2251_2500                TABLE                     247                    0
     2 STOCK_1251_1500                TABLE                     268                    0
     2 STOCK_251_500                  INDEX                     276                 2976
     2 STOCK_1_250                    INDEX                     284                 2846
     2 STOCK_501_750                  TABLE                     285                    0
     2 STOCK_501_750                  INDEX                     293                 3143
     2 DISTRICT                       TABLE                   18402                    0
     2 ORDERS                         INDEX                   41581                 1474
     2 ORDER_LINE                     INDEX                  114442                 3815
     3 CUSTOMER                       TABLE                     159                    0
     3 STOCK_251_500                  TABLE                     226                    0
     3 ORDERS                         INDEX                   42192                 1340
     3 ORDER_LINE                     INDEX                  115459                 3871

  112 record(s) selected.
    Note: Part of the lengthy output from the query has been excluded, as denoted by the vertical ellipsis.

Results

In the preceding example, you can see that information for data and index pages is returned separately. Also, the schema is specified to exclude data from objects associated with the SYSIBM schema from being reported.

Example

Example 1: Retrieving page reclaim wait times
The following SQL retrieves the total pages reclaimed and total wait time across all members.
SELECT 
     SUM(PAGE_RECLAIMS_X+PAGE_RECLAIMS_S+SPACEMAPPAGE_PAGE_RECLAIMS_X
       +SPACEMAPPAGE_PAGE_RECLAIMS_S)AS PAGE_RECLAIMS,
     SUM(RECLAIM_WAIT_TIME) AS RECLAIM_WAIT_TIME
FROM TABLE(MON_GET_PAGE_ACCESS_INFO('','', -2))
The results of this query would look like the following example::
PAGE_RECLAIMS        RECLAIM_WAIT_TIME
-------------------- --------------------
                 156                   91

  1 record(s) selected.

(Wait time is reported in milliseconds)

Example 2: Show the 10 tables that are associated with the highest number of pages reclaimed
This example shows how you can see which table objects are involved with page reclaiming.
SELECT SUBSTR(TABSCHEMA,1,8) AS TABSCHEMA, 
       SUBSTR(TABNAME,1,20) AS TABNAME, 
       RECLAIM_WAIT_TIME, 
       MEMBER,
       SUBSTR(OBJTYPE,1,10) AS OBJTYPE
FROM TABLE(MON_GET_PAGE_ACCESS_INFO(NULL,NULL,-2)) 
WHERE RECLAIM_WAIT_TIME > 0 
 ORDER BY RECLAIM_WAIT_TIME DESC 
 FETCH FIRST 10 ROWS ONLY
Results:
TABSCHEMA TABNAME              RECLAIM_WAIT_TIME    MEMBER OBJTYPE   
--------- -------------------- -------------------- ------ ----------
DTW       ORDER_LINE                        1307192      1 INDEX     
DTW       ORDER_LINE                        1250134      2 INDEX     
DTW       ORDER_LINE                        1249452      0 INDEX     
DTW       ORDER_LINE                        1159741      3 INDEX     
DTW       DISTRICT                           827598      0 TABLE     
DTW       DISTRICT                           785354      2 TABLE     
DTW       DISTRICT                           767148      1 TABLE     
DTW       DISTRICT                           687608      3 TABLE     
DTW       ORDERS                             556538      0 INDEX     
DTW       ORDERS                             539858      2 INDEX     

  10 record(s) selected.

(Wait time is reported in milliseconds)

Example 3: Show the 10 statements that are causing the highest number of pages reclaimed

This query is a variation on the preceding example; in this case, the query returns the 10 statements associated with the highest number of pages reclaimed:

SELECT SUBSTR(STMT_TEXT,1,50) AS STMT_TEXT,
       RECLAIM_WAIT_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT('D',NULL,NULL,-2))TABLE
       WHERE RECLAIM_WAIT_TIME > 0
ORDER BY RECLAIM_WAIT_TIME DESC
FETCH FIRST 10 ROWS ONLY
Results:
STMT_TEXT                                          RECLAIM_WAIT_TIME
-------------------------------------------------- --------------------
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03               796668
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03               785863
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03               746521
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03               623461
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?               610602
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?               522899
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?               518076
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID =               419022
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID =               406028
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID =               406006

  10 record(s) selected.

(Wait time is reported in milliseconds)

Example 4: Show the 10 statements that are causing the highest number of pages reclaimed, with the average wait time for each execution of each statement
In the preceding example, the wait time is expressed as an overall value per statement. The query does not take into account the fact that a given statement might have run numerous times. This example shows how you can examine the average wait time for each execution of each of the top 10 statements:
SELECT SUBSTR(STMT_TEXT,1,75) AS STMT_TEXT,
       NUM_EXECUTIONS,
       RECLAIM_WAIT_TIME, 
       DEC(FLOAT(RECLAIM_WAIT_TIME)/FLOAT(NUM_EXECUTIONS),10,8)
         AS AVG_WAIT_PEREXEC
FROM TABLE(MON_GET_PKG_CACHE_STMT('D',NULL,NULL,-2))TABLE
WHERE RECLAIM_WAIT_TIME > 0
ORDER BY AVG_WAIT_PEREXEC DESC
FETCH FIRST 10 ROWS ONLY
Results: 
STMT_TEXT                                                                   NUM_EXECUTIONS       RECLAIM_WAIT_TIME    AVG_WAIT_PEREXEC
--------------------------------------------------------------------------- -------------------- -------------------- ----------------
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID = ? and D_ID = ?                         157173               419497       2.66901439
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID = ? and D_ID = ?                         155752               397870       2.55450973
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID = ? and D_ID = ?                         155352               385613       2.48218883
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID = ? and D_ID = ?                         155151               347847       2.24199006
Insert into ORDERS values (?, ?, ?, ?, ?, ?, ?, ?)                                        157173               259076       1.64834927
Insert into ORDERS values (?, ?, ?, ?, ?, ?, ?, ?)                                        155752               253548       1.62789562
Insert into ORDERS values (?, ?, ?, ?, ?, ?, ?, ?)                                        155352               232300       1.49531386
Insert into ORDERS values (?, ?, ?, ?, ?, ?, ?, ?)                                        155151               219607       1.41544044
Delete from NEW_ORDER where NO_W_ID = ? and NO_D_ID = ? and NO_O_ID = ?                   152968               106525       0.69638747
Delete from NEW_ORDER where NO_W_ID = ? and NO_D_ID = ? and NO_O_ID = ?                   152591               101367       0.66430523

  10 record(s) selected.

(Wait time is reported in milliseconds)

A slightly different version of this query shows how long each statement took to execute: 
SELECT SUBSTR(STMT_TEXT,1,75) AS STMT_TEXT,
       NUM_EXECUTIONS,
       RECLAIM_WAIT_TIME, 
       DEC(FLOAT(RECLAIM_WAIT_TIME)/FLOAT(NUM_EXECUTIONS),10,8) 
       AS AVG_EXEC_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT('D',NULL,NULL,-2))TABLE
WHERE RECLAIM_WAIT_TIME > 0
ORDER BY RECLAIM_WAIT_TIME DESC
FETCH FIRST 10 ROWS ONLY
Results: 
STMT_TEXT                                                                   NUM_EXECUTIONS       RECLAIM_WAIT_TIME    AVG_EXEC_TIME
--------------------------------------------------------------------------- -------------------- -------------------- -------------
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S              1555470               755544    0.48573357
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S              1554405               754231    0.48522167
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S              1570256               741047    0.47192750
Select S_QUANTITY, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S              1550835               707148    0.45597887
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)                             1554392               508568    0.32718130
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)                             1555454               497197    0.31964751
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)                             1570245               493692    0.31440444
Insert into ORDER_LINE values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)                             1550813               465049    0.29987432
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID = ? and D_ID = ?                         157145               419283    2.66812816
Update DISTRICT set D_NEXT_O_ID = ? where D_W_ID = ? and D_ID = ?                         155719               397364    2.55180164

  10 record(s) selected.

(Wait time is reported in milliseconds)