To compute this metric, use Database, Bufferpool, and Tablespace snapshots as described by the preparation instructions.
To compute this metric, use Database, Bufferpool, and Tablespace snapshots as described by the preparation instructions.
To compute BPLRTX, first find the number of transactions from the Database Snapshot ("dbsnap2.txt") by adding together the number of Commit Statements Attempted plus Rollback Statements Attempted.
TXCNT = Commits Attempted + Rollbacks Attempted
Next, find the total number of Logical Bufferpool Reads by adding together "Buffer pool data logical reads" plus "Buffer pool index logical reads".
BPLR = Buffer pool data logical reads + Buffer pool index logical reads
BPLR can be computed at the database level from the Database Snapshot, at the Bufferpool level, for each bufferpool, from the Bufferpool Snapshot, and for each tablespace, at the tablespace level, from the tablespace snapshot. Unfortunately, DB2 does not report logical reads at the table level so a level of granularity is lost unless you assign one table per tablespace.
Finally, computing BPLRTX is easy:
BPLRTX = BPLR / TXCNT
So, why is BPLRTX the Most Important Cost metric? Glad you asked.
When DB2 wants to access data (either index pages or data pages), a Logical Read is performed against the bufferpool. If the data requested is not present in the bufferpool per the logical read request, then a Physical Read must be performed to disk to retrieve the page that was logically requested. If the logically requested data is already in the bufferpool, then the physical disk read is avoided. So, you will note that a request for data typically begins with a Logical request which may, or may not, result in a physical request.
There is a very strong correlation between Logical Reads and CPU consumption. The more Logical Reads that are performed in support of a transaction, the more CPU that is consumed. By improving physical design to reduce BPLRTX, you will successfully reduce CPU consumption, improve response times and query throughput, and avoid costly and unnecessary hardware upgrades.
Just remember: Logical Reads = CPU Consumption
So, if you want a gold star for your forehead and possibly receive a bonus from your manager, you should first measure BPLRTX, then improve the database physical design, then measure BPLRTX again. If BPLRTX is substantially reduced as a consequence of your tuning change, you have likely successfully completed a tuning iteration towards becoming a Performance Hero in your organization.
Some transaction costs the DBA cannot easily control; for example, the number of Selects or DML per transaction. However, two key costs that the DBA can influence are BPLRTX and the Number of Sorts per Transaction (SRTTX) - SRTTX is described in this blog post.
Shameless marketing moment:
BPLRTX, SRTTX, and many other important performance metrics are automatically computed for you by DBI's Brother-Eagle(TM) and Brother-Panther(TM) performance solutions. A free version (Standard Edition) of Brother-Eagle is available at www.Brother-Eagle.com. While initial award supplies last, you can become a DBI Certified Performance Hero and earn $100USD gift certificates, ceramic coffee mugs, and cool Performance Hero T-Shirts by simply trying Brother-Panther and demonstrating performance tuning success - it's easy! Become a Performance Hero! ( PS - Hardware vendors hate this campaign, but, go ahead, save $250-500K or more on avoided hardware upgrades courtesy of DBI - we are glad to help! )
Just for fun:
Well, first my apologies for being away from the blog for so long. I actually took a vacation in Montana and went White Water Rafting with Montana River Guides, visited Yellowstone National Park (in Wyoming), took the Missoula Snow Bowl Chair lift to 7000' then hiked to the mountain peak at 7950', and drove the Going to the Sun Highway through Glacier National Park. We saw bison, elk, moose, bald eagles, mountain goats, geysers, and spectacular scenery that words simply can't describe. If you haven't visited Yellowstone National Park or Glacier National Park yet, I suggest you add these parks to your "to do" list.
Until next time,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.database-performance.info