Discussion
Volatile Tables (System Maintenance)
The tables pr_sys_locks, pr_sys_updatescache, pr_sys_context, etc are small and volatile, and thus vulnerable to fragmentation. Defragment them regularly by re-writing the table and rebuilding the indexes. In addition it may be advisable to cache them in a separate database buffer pool. Share the following list with your DBA for tuning.
Can someone elaborate please
Pega 718 added 'defrag syslocks daemon'. The daemon implements a 'global pause' via hazelcast which blocks all locking, then copies all rows out of pr_sys_locks to a temporary table, truncates table pr_sys_locks and copies the active lock rows back into pr_sys_locks.
If you are running with elastic search enabled on work or data, the ftsindexer queue table is very prone to 'fragmentation' to.
On Postgres, a table is defragmented with the 'VACUM' feature. Postgres explicitly does not re-use space after deletion or certain updates, and by default runs a vacum scheduler.
On Oracle, I find the best tool to be 'alter table shrink space cascade'.
On DB2/LUW the REORG utility handles space reclamation.
Databases store rows on data pages within a tablespace. When a row is deleted, the space on the particular page is typically not available to be reused. Because of this, a table with a small number of rows can end up occupying a large number of pages. The process to reclaim this space is via a Reorg / Defrag process. This process should be performed regularly by the DBA.
This database maintenance is important because the access path chosen by the DBMS could end up performing a table-scan to satisfy a query. A table-scan will look at all pages in the tablespace which belong to a table to find the candidate row or rows satisfying the query.
For example, lack of database maintenance of PR_Sys_Locks has been the root cause of Sev-1 outages.