Performance Monitoring, Tuning, and Statistics
This domain tests how to measure Db2 12 behavior and feed the optimizer with good statistics.
Core rules
- Sources include Db2 statistics and accounting traces, SMF records, RMF, the Db2 performance monitor, and real-time statistics. DISPLAY STATISTICS and DISPLAY BUFFERPOOL show current activity.
- Buffer pool metrics include hit ratio, synchronous reads, sequential prefetch, and deferred write. Tune VPSEQT, DWQT, VDWQT, and page-fixed pools.
- EDM pool, sort pool, RID pool, and DBM1 storage are common constraints. Check for pool expansion failures and storage shortages.
- RUNSTATS with COLGROUP helps multi-column predicates; HISTOGRAM helps skewed data; SAMPLE must be large enough. Autonomic statistics and profiles can keep statistics fresh.
- Access-path tuning uses EXPLAIN, statement cost, package rebind, and plan stability.
Worked example
A query joining ORDERS and CUSTOMERS on CUSTOMER_ID runs slowly after data growth. Real-time statistics show stale ORDERS statistics and no COLGROUP on (STATUS, REGION). The DBA runs RUNSTATS with COLGROUP and HISTOGRAM, then rebinds. Reasoning: the optimizer's cardinality estimate was wrong; multi-column statistics improve selectivity estimates, and the rebind lets Db2 choose a better join order.
Traps
- A high buffer pool hit ratio can still hide a bad access path.
- Sorting and RID list processing consume pool storage; check failures.
- RUNSTATS with a tiny SAMPLE can produce unstable plans.
- Statistics become stale after LOAD or REORG unless collected.