Integrating IFCID 199 and SMF 42 data provides a view of buffer efficiency enabling buffer misses (I/Os) to be further subdivided into cache hits and misses, which can be leveraged to further refine buffer pool tuning methodologies.

 

More Db2 Statistics Videos

  1. Exploring Assessments of Key Metrics
  2. Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 1)
  3. Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 2)
  4. Integrating Db2 IFCID 199 with SMF 42 Data
  5. Buffer Efficiency from IFCID 199 and SMF 42
  6. Measuring Benefits of Db2 Buffer Pool Tuning
  7. Exploring Other Db2 Statistics Metrics
  8. Sample Dashboard of Db2 Statistics Metrics
  9. Integrating with RMF Metrics (70, 71, 72, 74)
  10. Integrating with RMF 70 and 72 CPU and WLM Metrics
  11. Integrating with RMF 71 Memory and Large Frame Metrics
  12. Integrating with RMF 74.4 Coupling Facility Metrics

 

Video Transcript

Another powerful way to leverage this integration of the Db2 IFCID 1 99 and SMF 42 data is this view of overall Db2 get page efficiency. It indicates the percentage of get pages resolved with a buffer pool hit, the ideal situation in red. This is the random buffer hit ratio, just as we discussed earlier. But the additional insight here is that for those get pages not satisfied from a buffer and thus that generate an I/O, what percentage of those I/Os result in a disk cache hit in yellow, and what’s the remaining percentage that generate an actual disk I/O in blue.

So, let’s drill into this data initially by buffer pool size. And then let’s go ahead and look at the 4K buffer pools by buffer pool number. So we’ll compare the efficiencies across the buffer pools and we’ll capture this in our dashboard.

So, we select a buffer pool with a lower percentage of hits here, like buffer pool 16. We might be interested in seeing how many I/Os are occurring. So in this case, it’s in the range of about 1500 to 2000 per second, except in the evening. Or another way we can look at this data is by the database, the buffer efficiency by database. Let’s go ahead and compare that across the various databases. And again, let’s capture that in our dashboard. Selecting this database with a lower buffer hit percentage, we see an I/O rate approaching 20,000 per second, but that are fortunately almost all cash hits. So that I/O rate led me to be curious about the buffer pool connection. So let’s go ahead and just hang on to those cache hits and now look at it by buffer pool. And when we do that, we see that almost all the I/Os to this database are coming out of buffer pool 20 for the data and 21 for the index.

And so that leads us to the topic we mentioned earlier, the potential application of this cache hit data to enhance a buffer pool tuning methodology. Disk cache hits reflect a second tier of access. The page did not reside in the Db2 buffer pool long enough to be accessed there, but the page was accessed soon enough thereafter, such that it was still present in the disk cache. So cache hit volumes as seen here can be an indicator of buffer pools that could potentially benefit from being enlarged, indicating a likelihood that the page may be accessed in the buffer if it’s able to remain resident a bit longer. Again, not surprisingly we see buffer pool 21 at the top of this list of disk cache hits again, suggesting it as a prime candidate to benefit from additional memory.

So more broadly we’ve seen how integrating the Db2 IFCID 1 99 data with SMF 42 dataset I/O performance data provides extensive insights into the I/O cache and response time characteristics at the Db2 buffer pool and Db2 database levels.

Learn More About Analyzing Db2 Statistics and Accounting Data

Speak to a Technical Expert Today

Whether you are conducting product research, need support on a project, are experiencing downtime, or want to learn more about how IntelliMagic can support your business, our experts are here to help.

You May Also Be Interested In:

Webinar

Implementing Asynchronous Duplexing in a Production Db2 Lock Structure - User Experience

Analyze the performance impacts of implementing asynchronous duplexing for Db2 lock structure in a high volume Db2 data sharing environment.

Watch Webinar
Whitepaper

How to Achieve Higher Availability and Lower Performance Costs with Asynchronous Db2 Lock Structure Duplexing

In this Cheryl Watson Tuning Letter Reprint, Todd Havekost and Frank Kyne detail the unexpected performance benefits gained after one site implemented Asynchronous duplexing.

Download
Video

Db2 Accounting Data: Customized Dashboard Recap

A survey of a sample customized dashboard where key Accounting data charts have been collected. These dashboards can promote collaboration across teams, as well as serving as a springboard for additional analysis as the views are applied to other time intervals.

Watch video

Explore Db2 Performance Management and Monitoring