Discussion
2
Replies
385
Views
Posted: June 22, 2016
Last activity: June 23, 2016
Closed
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
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.