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
Procedure
Results
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.
Results: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
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:
Results: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
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:
Results: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
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:
Results: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
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)