Question & Answer
Question
If a temporary tablespace has a very large bufferpool, and temporary tables are often dropped, CPU usage might be high. (Even if your application does not explicitly drop temporary tables, DB2 might still create and drop temporary tables internally in normal query processing.)
Cause
DB2 maintains internally a "dirty list" of pages for each bufferpool. The "dirty list" is a list of pages that have been modified in memory and so need to be flushed to disk at some time. The larger the bufferpool, the longer its "dirty list". When a temporary table is dropped, the entire "dirty list" has to be scanned to find all the pages that belong to the table being dropped. The longer the "dirty list", the more time it takes to scan it. If temporary tables are dropped many times, and the temporary tablespace has a very large bufferpool, there might be latch contention and high CPU usage.
Answer
You can avoid this slow performance by creating a smaller bufferpool dedicated to the temporary tablespaces. All the temporary table activity will then take place in this small bufferpool, which has a shorter "dirty list". That avoids costly scans of the long "dirty list" belonging to a large bufferpool.
But do not make the bufferpool too small; otherwise pages may have to be written often to disk . You can use bufferpool snapshots to monitor the ratios of logical reads to physical reads (hit ratios). If the hit ratios are unacceptably low, you can make the bufferpool larger.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21255078