Chapter 25 Quiz: Buffer Pool and Memory Tuning
Instructions: Choose the single best answer for each multiple-choice question. For short-answer questions, write 2-4 sentences. Click the disclosure triangle to reveal the answer after attempting each question.
Question 1
What is a buffer pool hit ratio?
- A) The percentage of physical reads that complete within 4 ms
- B) The percentage of logical reads (getpages) satisfied from memory without physical I/O
- C) The percentage of dirty pages that are written asynchronously
- D) The ratio of index reads to data reads in the buffer pool
Answer
**B** — The hit ratio is the percentage of logical reads (getpage requests) satisfied from the buffer pool memory without needing a physical I/O to disk. Formula: `(1 - Physical Reads / Logical Reads) * 100`.Question 2
A buffer pool has 2,000,000 logical reads and 40,000 physical reads in a monitoring interval. What is the hit ratio?
- A) 96%
- B) 97%
- C) 98%
- D) 99%
Answer
**C** — Hit Ratio = (1 - 40,000 / 2,000,000) * 100 = (1 - 0.02) * 100 = 98%.Question 3
What is the primary difference between a synchronous (random) read and a prefetch (sequential) read?
- A) Synchronous reads are faster than prefetch reads
- B) Synchronous reads suspend the query thread until the page is loaded; prefetch reads are asynchronous
- C) Prefetch reads can only read data pages, not index pages
- D) Synchronous reads use larger block sizes
Answer
**B** — Synchronous reads suspend the application thread until the page is loaded into the buffer pool. Prefetch reads are initiated asynchronously by DB2's prefetch engine, which reads pages ahead of the query thread so they are already in the pool when needed. The thread does not wait.Question 4
On z/OS, what does the VPSEQT parameter control?
- A) The total number of pages in the virtual buffer pool
- B) The maximum percentage of the buffer pool that sequential prefetch can consume
- C) The threshold at which deferred writes begin
- D) The page stealing algorithm
Answer
**B** — VPSEQT (Virtual Pool Sequential Steal Threshold) sets the maximum percentage of the buffer pool that sequentially accessed pages can occupy. Once this threshold is reached, DB2 preferentially steals sequential pages to protect randomly accessed pages.Question 5
A z/OS buffer pool shows frequent "VPSEQT reached" events. What does this indicate?
- A) The buffer pool is too large
- B) Sequential prefetch is consuming a significant portion of the pool, and DB2 is throttling it
- C) The deferred write threshold needs adjustment
- D) The hiperpool needs to be enabled
Answer
**B** — VPSEQT reached events indicate that sequential prefetch hit the threshold, meaning sequential I/O is consuming enough of the pool to trigger DB2's protection mechanism. This is working as designed — DB2 is protecting random pages — but frequent occurrences suggest the pool may benefit from workload separation (move sequential objects to a dedicated pool).Question 6
On z/OS, what happens when dirty pages reach the DWQT (Deferred Write Queue Threshold)?
- A) DB2 stops accepting new queries
- B) DB2 begins asynchronously writing dirty pages to disk
- C) DB2 switches to synchronous writes only
- D) The buffer pool is flushed completely
Answer
**B** — When dirty pages reach the DWQT percentage, DB2 triggers asynchronous (deferred) write processing to flush dirty pages to disk. This is normal, expected behavior that keeps clean pages available for stealing. The goal is to prevent the more harmful synchronous writes.Question 7
What is the recommended approach for hiperpool on modern z/OS systems (z/OS 2.3+, DB2 12+)?
- A) Always enable hiperpool for maximum caching
- B) Set hiperpool to 50% of the virtual pool size
- C) Generally disable hiperpool and allocate the memory to larger virtual buffer pools
- D) Enable hiperpool only for index pools
Answer
**C** — On modern z/OS systems, expanded storage (which hiperpool was designed to exploit) no longer exists as a separate hardware tier. The CPU overhead of managing hiperpool provides no benefit when virtual pool pages and hiperpool pages are backed by the same physical memory. Disable hiperpool (HPSIZE 0) and increase VPSIZE instead.Question 8
On LUW, what does ALTER BUFFERPOOL bp_oltp SIZE AUTOMATIC do?
- A) Sets the buffer pool to use all available memory
- B) Enables the Self-Tuning Memory Manager (STMM) to dynamically adjust the buffer pool size
- C) Automatically doubles the buffer pool every hour
- D) Sets the buffer pool to the DB2 default size
Answer
**B** — Setting a buffer pool to SIZE AUTOMATIC enables STMM to dynamically adjust the pool size based on observed workload patterns, hit ratio analysis, and the benefit each consumer would gain from additional memory.Question 9
Why should OLTP tables and batch-scanned tables be in separate buffer pools?
- A) Different tables require different page sizes
- B) Sequential batch scans evict OLTP hot pages, destroying the OLTP hit ratio
- C) Batch tables cannot use indexes
- D) OLTP tables require synchronous writes
Answer
**B** — Sequential batch scans read pages once and move on, evicting frequently-reused OLTP hot pages from the LRU chain. In a shared pool, a single large scan can destroy the entire OLTP cache, causing OLTP response times to spike. Separate pools isolate the two access patterns completely.Question 10
On z/OS, what does the EDM pool cache?
- A) Buffer pool pages and index pages
- B) Plans, packages, database descriptors (DBDs), and dynamic SQL statements
- C) Sort work areas and temporary tables
- D) Lock information and log records
Answer
**B** — The EDM (Environmental Descriptor Manager) pool caches compiled SQL artifacts (CT/PT skeletons from bound packages), Database Descriptors (DBDs), and prepared dynamic SQL statements (in the EDMSTMTC portion). These are the metadata objects needed to execute SQL without re-parsing and re-optimizing.Question 11
On z/OS, what happens when the RID pool is too small during a list prefetch operation?
- A) The query fails with a SQL error
- B) DB2 falls back from list prefetch to a table space scan (RID failure)
- C) The buffer pool hit ratio drops
- D) Sorts spill to work files
Answer
**B** — When the RID pool is too small, DB2 cannot build the sorted RID list needed for list prefetch. It silently falls back to a table space scan, which may read far more pages. The query returns correct results but runs dramatically slower. This is called a RID failure.Question 12
On LUW, what is the relationship between DATABASE_MEMORY and buffer pool sizes?
- A) DATABASE_MEMORY must equal the sum of all buffer pool sizes
- B) DATABASE_MEMORY must be large enough to hold all buffer pools plus other database heaps
- C) Buffer pool sizes are independent of DATABASE_MEMORY
- D) DATABASE_MEMORY controls only sort heaps, not buffer pools
Answer
**B** — DATABASE_MEMORY is the total memory budget for a database and must be large enough to hold all buffer pools plus the lock list, package cache, catalog cache, sort heaps (shared), and other database-level memory consumers. Buffer pools are the largest component but not the only one.Question 13
What is the primary benefit of STMM (Self-Tuning Memory Manager) on LUW?
- A) It automatically increases total system memory when needed
- B) It dynamically redistributes memory between consumers based on observed workload demands
- C) It eliminates the need for buffer pools entirely
- D) It replaces the query optimizer's cost model
Answer
**B** — STMM monitors resource utilization across memory consumers (buffer pools, sort heap, package cache, lock list, etc.) and redistributes memory from consumers where additional memory would provide little benefit to consumers where it would provide significant benefit. It optimizes within the DATABASE_MEMORY ceiling.Question 14
A buffer pool's hit ratio increases from 98% to 99% when you double its size from 4 GB to 8 GB. Is this a good use of the additional 4 GB?
- A) Yes, because every percentage point of hit ratio improvement is equally valuable
- B) It depends — the 1% improvement halves physical reads, but 4 GB of memory might produce more benefit elsewhere
- C) Yes, because 99% is the universal target for all buffer pools
- D) No, because hit ratios above 95% never matter
Answer
**B** — Going from 98% to 99% cuts physical reads in half (from 2% to 1%), which is meaningful. However, the absolute number of remaining reads is small. If another buffer pool is at 85% or the sort heap has a 15% overflow rate, that 4 GB would produce much more benefit there. Tuning is about allocating limited memory where it provides the greatest overall improvement.Question 15
On z/OS, what does PGFIX(YES) do, and why is it recommended for production?
- A) It fixes the page size at 4 KB permanently
- B) It page-fixes buffer pool pages in real storage, preventing z/OS from paging them to auxiliary storage
- C) It prevents any page from being stolen from the buffer pool
- D) It fixes corrupted pages automatically
Answer
**B** — PGFIX(YES) tells z/OS to keep buffer pool pages in real (central) storage, preventing them from being paged to auxiliary storage (disk). Without PGFIX, z/OS may page out buffer pool pages during memory pressure, causing "double paging" — DB2 thinks the page is cached but accessing it triggers an OS page fault. This is invisible to DB2 monitoring and causes unpredictable latency.Question 16 (Short Answer)
Explain why synchronous writes are a more serious performance problem than synchronous reads. What buffer pool configuration causes synchronous writes?
Answer
Synchronous reads are expected in a buffer pool that cannot cache the entire working set — they occur when a needed page is not in the pool. Synchronous writes are more serious because they indicate the buffer pool has run out of clean (non-dirty) pages to steal. The application thread must wait for a dirty page to be written to disk before the incoming page can occupy its slot. This is a sign that either the pool is too small, the deferred write threshold (DWQT) is too high, or page cleaners (LUW) are insufficient. The write latency (typically 2-10 ms) is added directly to the application's response time, and unlike a synchronous read (which at least fetches the needed data), the synchronous write produces no direct benefit to the requesting thread.Question 17 (Short Answer)
On LUW, explain the difference between setting a buffer pool to SIZE 500000 versus SIZE AUTOMATIC. When would you choose each approach?
Answer
`SIZE 500000` sets a fixed size — the pool always has exactly 500,000 pages, and STMM cannot adjust it. `SIZE AUTOMATIC` lets STMM dynamically increase or decrease the pool based on workload analysis. Use fixed size for critical OLTP pools where you need a guaranteed minimum — you do not want STMM to shrink the pool during a quiet period and then have a cold cache when peak load returns. Use AUTOMATIC for pools where flexibility is acceptable (batch, temp, analytics) or when you trust STMM to balance competing demands across multiple consumers.Question 18 (Short Answer)
Describe the "cold cache" problem that occurs after a database restart. How long might it take for hit ratios to recover, and what can be done to mitigate it?
Answer
After a restart, every buffer pool is empty. Every page request results in a physical read until the working set is loaded — a "cold cache." Recovery time depends on workload intensity and working set size; a busy OLTP system may take 15-30 minutes to reach steady-state hit ratios, while a large analytics database may take hours. Mitigation strategies include: (1) running warm-up queries that touch critical tables immediately after activation, (2) on z/OS, using buffer pool SAVE/RESTORE to reload the pre-restart cache contents, (3) gradually ramping up traffic (routing only a fraction of users to the instance initially), and (4) temporarily lowering connection pool sizes during the warm-up period.Question 19 (Short Answer)
On z/OS, why is it recommended to separate index pages into a dedicated buffer pool? What makes index I/O patterns different from data I/O patterns?
Answer
Index pages — especially the non-leaf (upper-level) pages of the B-tree — have extremely high reuse. The root page and first-level pages of a busy index may be accessed millions of times per hour, and they should never be evicted. A dedicated index pool ensures that these high-value pages are not displaced by data page activity (especially sequential scans). Additionally, index access is almost always random (probing from root to leaf), so an index pool benefits from VPSEQT set very low (0-10%) and can be sized precisely based on the known number of index pages.Question 20 (Short Answer)
Explain why setting INSTANCE_MEMORY to 100% of physical memory is dangerous, even on a dedicated database server.
Answer
The operating system needs memory for its kernel, TCP/IP network stack, filesystem cache, process management, and other essential services. If DB2 consumes 100% of physical memory, the OS will be forced to page its own structures (and potentially DB2's structures) to swap/auxiliary storage. This creates a cascading performance collapse — the OS is slow because it is paging, which makes DB2 I/O slow (because I/O goes through the OS), which increases response times. The typical recommendation is 70-80% of physical RAM for DB2, leaving 20-30% for the OS and a safety margin for unexpected memory spikes.Question 21 (Short Answer)
What is the purpose of NUMBLOCKPAGES in a DB2 LUW buffer pool? When would you use it and when would you avoid it?
Answer
NUMBLOCKPAGES reserves a portion of the buffer pool for block-based I/O, where DB2 reads multiple contiguous pages into contiguous memory slots. This improves sequential prefetch efficiency because the prefetch engine can perform larger, more efficient I/O operations. Use NUMBLOCKPAGES for buffer pools that serve sequential workloads (batch processing, data warehouse scans, reporting). Avoid it for OLTP pools where access is primarily random — the block reservation wastes memory that would be better used for caching randomly accessed pages, and random access does not benefit from contiguous memory layout.Question 22 (Short Answer)
A DBA notices that the BP_BATCH buffer pool on z/OS has a hit ratio of 72% and proposes increasing its size from 200,000 to 1,000,000 pages. The batch workload performs sequential scans of tables totaling 5,000,000 pages. Will this help significantly? Explain your reasoning.
Answer
Likely not significantly. The batch workload scans 5,000,000 pages sequentially, but even with 1,000,000 pages, the pool can only cache 20% of the data. Since sequential scans read pages once and move forward, caching earlier pages provides no benefit — they will not be re-read. The hit ratio improvement will be minimal because the working set vastly exceeds the pool size. A better approach is to keep the batch pool moderate, use PGSTEAL(FIFO), set VPSEQT high, and rely on sequential prefetch for throughput. The money would be better spent increasing the OLTP pool.Question 23
Which of the following memory consumers on LUW can be managed by STMM?
- A) Buffer pools, sort heap, package cache, lock list
- B) Buffer pools only
- C) Sort heap and lock list only
- D) All memory consumers including log buffer and FCM
Answer
**A** — STMM can manage buffer pools (SIZE AUTOMATIC), sort heap (SORTHEAP AUTOMATIC), package cache (PCKCACHESZ AUTOMATIC), lock list (LOCKLIST AUTOMATIC), and database memory (DATABASE_MEMORY AUTOMATIC). It cannot manage log buffer (LOGBUFSZ), FCM buffers, or other fixed-allocation consumers.Question 24
On z/OS, what does PGSTEAL(NONE) do, and what is the risk of using it?
- A) Disables page stealing; risk is that the pool may run out of space if data grows
- B) Uses the fastest page steal algorithm; no risk
- C) Prevents dirty page writes; risk is data loss
- D) Disables prefetch; risk is slower sequential scans
Answer
**A** — PGSTEAL(NONE) means no pages are ever evicted from the buffer pool. The pool must be large enough to hold every page of every table space assigned to it. The risk: if data grows beyond the pool capacity, DB2 cannot bring in new pages. Use PGSTEAL(NONE) only for small, stable reference tables where you can guarantee the data size will not exceed the pool size.Question 25
You have 16 GB available for buffer pools. Your database has two workloads: OLTP (working set: 6 GB, current hit ratio: 94%) and batch (working set: 50 GB, current hit ratio: 65%). How should you allocate the 16 GB?
- A) 8 GB to OLTP, 8 GB to batch (equal split)
- B) 10-12 GB to OLTP, 4-6 GB to batch (favor the OLTP workload)
- C) 4 GB to OLTP, 12 GB to batch (the batch pool needs more help)
- D) 16 GB to a single shared pool