Another DB2 magazine blog reader, Geoff, read the recent post "DB2 LUW Performance: Asked and Answered (BPLRTX)" and inquired about the optimum setting for NUMBLOCKPAGES. Let's take a closer look...

[ more .. ]

In DBI's Webinar "DB2 LUW Index Physical Design & DBI Performance Solutions: Your Roadmap to Becoming a Performance Hero", we discuss some important index physical design guidelines and techniques (the next Webinar is October 30th @ 10:30am CDT). Ideally, the FULLKEYCARD cardinality of an index should be at least 75% of the table's cardinality. Here is a sample SQL query that can help you do a quick cardinality check on your indexes...

[ more .. ]

I want to thank Brian Stewart, Greg Marino, and Marco Bartolli for their comments and questions posted in response to DB2 LUW Performance: The Most Important Cost. These comments include some very good questions that I'll attempt to answer in this blog post.

[ more .. ]

The Catalog Cache is like a special memory bufferpool dedicated to catalog objects; it stores information about tables, indexes, views, and other objects to speed up the BIND process for dynamic and static SQL. It CAN be changed online dynamically, but it DOES NOT participate in DB2 9 Autonomic Tuning - SO, you'll have to tune this one yourself. Here's how...

[ more .. ]

We've looked at several metrics that can help you discover the presence of physical design "opportunities for improvement", or problems. But, as many of you know, I don't like the word "problems" - 1) Problems is too negative sounding, and 2) Sometimes inefficiencies, or tuning opportunities, aren't severe enough to merit the label "problem". "Status Quo" operations for many databases may include many inefficiencies but the performance delivered is "good enough" - well, that is, it's "good enough" until the next hardware upgrade cost comes due or the application falls over and dies when 20 more users are added.

[ more .. ]

The database configuration parameter LOGBUFSZ controls the amount of memory that DB2 uses to buffer I/O to its recovery log files. The default size of 8 4K pages is grossly to small for most databases. This blog post introduces a new metric "Buffer Log Read hit Ratio" and offers tuning suggestions for Performance Heroes.

[ more .. ]

It's difficult to talk about DB2 performance and not have the subject of Bufferpool Hit Ratios come up. It's as if high bufferpool hit ratios are somehow capable of saving the planet from global warming. Yes, bufferpool performance is relevant, but we need to evaluate these with a dose of reality. Performance Heroes will spend much more time on workload analysis and physical design than twiddling memory bits.

[ more .. ]

In this post, I'll attempt to summarize the key metrics we've discussed so far and provide links back to the original posts for your reference. Database Files Closed will also be discussed. In upcoming posts, we'll look at time measurements, important ratios, workload analysis, and physical design techniques to reduce costs and improve performance. It would please me greatly for all of you to become Performance Heroes in your organizations.

[ more .. ]

Let's take a look at a cost measurement that can help you detect the presence of Index Leaf Page scans, BPLITX. While perhaps not as costly as data page scans, index leaf page scans can quickly suck your CPUs dry of processing capacity and rob your organization of performance that it would otherwise be entitled to. Performance Heroes will be successful at reducing the cost of both data and leaf page scans.

[ more .. ]

In this blog post, I will describe the most important cost metric that you MUST measure, and work to improve, to become a Performance Hero in your organization. This cost metrics is "Bufferpool Logical Reads per Transaction (BPLRTX)".

To compute this metric, use Database, Bufferpool, and Tablespace snapshots as described by the preparation instructions.

[ more .. ]

As described in my prior blog post, understanding workload costs is critical to successful database tuning. Performance Heroes diligently work to reduce costs of processing statement workloads. Here are two more important cost metrics.

[ more .. ]

The secret to successful database performance tuning and optimization requires an intimate understanding of workload costs. You can become a Performance Hero in your organization by determining current workload costs, and then making physical design and configuration changes to lower transaction costs - or the costs of doing business in the database.

Too often we find database people being excessively obsessed with rates, or chasing individual queries. Rates can vary day by day depending on the day of the week, the time of the month, or the hour of the day. Successful Performance Heroes will focus on reducing transaction costs to improve efficiency. Be a hero!

Cost Measurements:

[ more .. ]

Besides IREF, another key indicator of a database's health and efficiency is the Synchronous Read Percentage, or SRP. When DB2 has good indexes available to retrieve rows for result sets, it will use synchronous I/O to access precisely just the index and data pages required. When indexes are missing, or the physical design is otherwise sub-optimal, DB2 will resort to using asynchronous prefetch I/O to scan index or data pages. Scans are "evil" (a word borrowed from my teenage daughter) and should be avoided as much as possible, especially for OLTP databases.

The Synchronous Read Percentage (SRP) metric:

[ more .. ]

How many rows must be read (evaluated) to retrieve one row? If DB2 lacks sufficient indexes to filter the result set according to the WHERE predicates, then DB2 will have to evaluate many, possibly too many, rows from the data pages to find result sets.

The Index Read Efficiency (IREF) Metric:

[ more .. ]

What type of database do you have?

You might think you have an OLTP transactional database. Or, you might think you have a Data Warehouse database. But what does your database think? How is it, or the queries within it, really performing?

The Average Result Set Size

[ more .. ]

When Kim Moutsos contacted me about the opportunity to blog for DB2 Magazine, I was thrilled. What better way, I thought, could there be to help DB2 users improve the performance and security of their databases than to blog about these topics?

I told Kim that one of the first things I'd like to do would be to teach online excerpts from my IDUG Education Seminar "DB2 LUW Performance Diagnosis Lab".

So, let's get started.

[ more .. ]

I've been writing the DB2 LUW Performance blogs on www.IBMDatabaseMag.com for about three years. The site used to be called DB2Mag.com but IBM "upgraded" it about a year ago. Unfortunately, the IBM DB2 community is about to suffer the loss of a very valuable information asset --- IBM intends to stop printing IBM Database Magazine and www.ibmdatabasemag.com is scheduled to go off the air around 30 April 2009. So, the DB2 LUW Performance blogs need a new home - even if temporary. Here they are. I hope you continue to enjoy this valuable reference material.

Best regards,
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
IBM Data Champion