Db2 GETPAGE Efficiency Integrating Dataset Performance (42.6) and Db2 IFCID 199 Data

This video shows how integrating Db2 IFCID 199 and SMF 42 data can be leveraged to create a view of overall Db2 GETPAGE efficiency, reflecting the percentages of GETPAGES that are resolved with a buffer pool hit, that result in an I/O but are satisfied by a disk cache hit, and the remaining percentage that generate an actual disk I/O.

More Integrated Visibility Resources

1. Leveraging XCF Message Activity for CPU Efficiency
2. Troubleshooting WLM Missed Goals with CICS Transaction Data
3. Address Space and Db2 Accounting Data
4. Dataset Performance (42.6) and Db2 Buffer Pools & Databases (SMF 102/IFCID 199)
5. Db2 GETPAGE Efficiency – Dataset Performance (42.6) and Db2 IFCID 199
6. Elapsed Time Profiles by Correlation ID: CICS Transaction (110.1) and Db2 Accounting (101) Part 1
7. Analysis of CPU By Plan: CICS Transaction (110.1) and Db2 Accounting (101) Part 2
8. Insights You Can Gain from Integrated Visibility Across Types of SMF Data

Video Transcript

Okay, another way to leverage, bringing, integrating this data between the IFCID 199 and the SMF 42 data is this view of overall buffer efficiency. GETPAGE efficiency. In other words, what percentage of my GETPAGES are resolved with the buffer pool hit in red here, right? That’s my ideal situation. What percentage result in an IO? But it’s a cache hit that’s in blue. And then what percentage actually end up being an IO and it’s a miss. And so I actually have to do a disk IO.

So, let’s go ahead and look at that information here. Break it out by buffer pool size first, and then now let’s start out by buffer pool. So, you know, here’s that profile across the buffer pools and initially this is sorted as it comes in the order of most GETPAGES.

And so if we wanted to, another way to look at this data would be to look at it by IO rate. Often in buffer pool tuning methodology. Sometimes the focus is on, you know, where am I doing the most IO’s today? Cuz I’m trying to minimize those. So if I do that, I come with a similar view, just kind of the first two pairs of buffer pools, just kind of swapped places when we did that.

All right, so let’s take this again, we saw earlier buffer pool 20 was the one that we’re doing the most IO’s to. So let’s look at that over time. And I’m gonna, I’m gonna remove the buffer pool hits for right now. So I’m, now, I’m just looking at the times I had to do an IO, the percentage of the total GETPAGES that resulted in an IO.

And then also let’s for context, let’s also put the IO rate on here so we have an idea what, how many IO’s we’re talking about. And then finally, I’m gonna just expand this. Instead of looking at a single day, I’m gonna look at a full week. I’ve got a week’s worth of data in this demo database. So here we can see then the pattern of, in terms of the number of IO’s and for the total GETPAGES, what percentage are cache hits and cache misses. So we got kind of 10 to 12% much of the time they’re 10 to 12% were misses at the buffer pool. And then I had to do an IO, but you know, in most cases here they were cache hits.

Okay, and then another way to look at that data instead of by buffer pool, we could look at it by Db2 database. And same, you know, same kind of outlook. And again, we saw earlier this database here, this fourth one was the one that had by far the most IO’s of any database. And we can see here from a percentage point of view that represented slightly, represents slightly more than 10% of the total GETPAGES end up turning into an IO.

Okay, let’s again, let’s look at that profile over time. And again, I’m gonna remove the hits buffer hit so I can see the picture a little bit better. And then again, I’m gonna add in the IO rate here. And again, if I look at this data across the week, I see there was a dramatic drop during the week in the IO rate. And that from other sources, we learned that there was a SQL tuning change that happened during the week that significantly reduced GETPAGES and thus IO’s, so the actual percentage of IO’s is a little bit higher – percentage of GETPAGES that turned into an IO – but the rate of IO’s had decreased dramatically.

And then let’s actually look at the GETPAGE rate here. So now I see the GETPAGES and the IO’s kind of together. And the last, I’ve got midnight spikes going on here, so I’m gonna just kind of zero in on the day shift. So we can see here that the rate of GETPAGES dropped dramatically as a result of that SQL tuning change, which is goodness. And it had lots of other good downstream effects that we do not have opportunity to look at today. And the IO rate is also down not quite as much, which is just a reflection of the fact that there’s a little bit more, little bit higher percentage when there was a GETPAGE miss in the buffer pool.

Okay, so we’ll just look at this dashboard as an example of the kinds of views that we walked through and again, brought together buffer pool data and database data and IO performance and cache data. And being able to bring all those together then gives us, again, additional insights into what’s happening. And even could potentially, as we talked about, inform buffer pool tuning in ways that just one set of data would not provide.