When an application on a member needs to make a change to a page which is currently held exclusively by another member, the CF can request the p-lock is released early. This process of releasing a page early is called page reclaiming.
This help eliminating the page lock waits across members, but these reclaims are expensive and this can be monitored via SYSPROC.MON_GET_PAGE_ACCESS_INFO () table function.
SELECT VARCHAR(TABNAME,30) AS NAME, VARCHAR(OBJTYPE,10) AS TYPE, PAGE_RECLAIMS_X AS PGRCX, PAGE_RECLAIMS_S AS PGRCS, SPACEMAPPAGE_PAGE_RECLAIMS_X AS SMPPGRCX, SPACEMAPPAGE_PAGE_RECLAIMS_S AS SMPPGRCS, RECLAIM_WAIT_TIME FROM TABLE( MON_GET_PAGE_ACCESS_INFO('<SCHEMA>','<TABLE>',NULL) ) AS RECLAIMMETRICS ORDER BY RECLAIM_WAIT_TIME DESC;
How to Reduce the Page Reclaims?
- Smaller page size reduces false sharing conflicts and help reduces reclaims on tables and indexes
- Increase PCTFREE for tiny hot tables
- Adding CURRENT MEMBER or CURRENT NODE default column to the table and partition it on period and member.