IBMDatabaseMagazine.com Blogs

In the prior blog post, we learned how to determine if your database is CPU bound, lock bound, sort bound, or I/O bound, and how to determine if a performance problem is attributable to the database or not. We will now turn our attention to statement analysis methodologies so that we can discover the sources of bottlenecks. “Statements” is broadly defined to include both classic SQL and newer XML queries.

Continue reading "DB2 LUW Performance: Statement Analysis Introduction - Hunting Elephants and Mosquitoes..."

Comments(1)


DB2 LUW Performance: The DNA Test of Performance Accountability

The database is often presumed guilty if there is a performance issue. But your database seems fine; all the symptoms of good performance are present, so now how do you prove your database’s innocence?

In the prior blog post, I provided a checklist of some metrics that would help you assemble your defense if it was asserted that your database was the source of performance problems. But those ratios and indicators are just circumstantial evidence of probable innocence. Here comes the DNA test. It’s hard. It’s time consuming. It’s complex. But the analytical effort just might help get you out of the hot seat and properly direct a performance issue to application or networking teams...

Continue reading "DB2 LUW Performance: The DNA Test of Performance Accountability..."

Comments(5)


DB2 LUW Performance: DB2 is ALIVE and WELL and IT’S NOT YOUR FAULT!

It is an unfortunate reality that the database and the DBA are too often presumed guilty by default. Everyone tends to want to blame the database first, even though performance degradation could be caused by network problems, storage problems, the Web server, sun spots, or poor application coding. So, as a database professional, how do you get yourself out of the hot seat and prove your database’s innocence? Here's a checklist to assemble your defense:

Continue reading "DB2 LUW Performance: DB2 is ALIVE and WELL and IT’S NOT YOUR FAULT!..."

Comment on this blog entry


DB2 LUW Performance: Write I/O Optimization Part 2

It's said that there is more than one way to skin a cat, meaning there are multiple ways to accomplish the same objective. As for the saying, I don't think this is very kind to cats even though I prefer dogs. The prior blog post discussed making adjustments to CHNGPGS_THRESH to reduce, avoid, or mitigate transient "brown outs" in transaction throughput. There is another way...

Continue reading "DB2 LUW Performance: Write I/O Optimization Part 2..."

Comments(2)


DB2 LUW Performance: Write I/O Optimization

A student who took this class last week at WDUG emailed me the day after to thank me. He said he enjoyed the class and successfully reduced the elapsed time of a troublesome query from two hours to three minutes! If you missed the WDUG session, I hope you will be able to join my Ed Seminar at IDUG "DB2 LUW Performance Diagnosis Learning Lab". This post covers topics related to optimizing Write I/O performance.

Continue reading "DB2 LUW Performance: Write I/O Optimization..."

Comment on this blog entry


DB2 LUW Performance: Table Read I/O and Overflows

Understanding Table I/O performance is critically important to properly diagnosing the health and efficiency of a database and pinpointing problems. In fact, if I only had just a few minutes to quickly assess a database, I'd look at 3 key measurements...

Continue reading "DB2 LUW Performance: Table Read I/O and Overflows..."

Comments(4)


DB2 LUW Performance: The Death of DB2

Disclaimer up front: This contains a rant, but it may be relevant to the performance of your organization or your databases. No children or animals were harmed in the writing of this blog. In a departure from my 21 prior posts, there are no formulas herein. I'm just going to vent about events in our industry. Feel free to skip to the next blog post or post a comment if you are so inclined.

Continue reading "DB2 LUW Performance: The Death of DB2..."

Comments(12)


DB2 LUW Performance: Direct I/O Times

Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.

Continue reading "DB2 LUW Performance: Direct I/O Times..."

Comments(6)


DB2 LUW Performance: More on Locks

First, my apologies for being away from the blog keyboard for so long. Kim Moutsos actually contacted me to see if I was still alive. Truth be known, my grandmother died, my father is in the hospital battling cancer, and I've been traveling the US States quite a bit helping companies save millions in software and hardware costs. Nonetheless, here's a quickie on some lock formulas and other updates. The good news is, I suppose, I'm accumulating a great deal of new material to share with you in future posts.

Continue reading "DB2 LUW Performance: More on Locks..."

Comment on this blog entry


DB2 LUW Performance: Fighting Over Data - LOCKS

Every once in a while I hear a DBA say they are having Lock problems. Since read-only or read-mostly Data Warehouse databases rarely have lock problems, I quickly assume they have an OLTP database. It is my opinion that locks are rarely, if ever, a PROBLEM. Locks are a SYMPTOM of another very real problem.

Continue reading "DB2 LUW Performance: Fighting Over Data - LOCKS..."

Comments(2)


DB2 LUW Performance: Sorts - The silent performance killer

In one of the earlier blog posts "DB2 LUW Performance: Key Cost Measures", we introduced the number of sorts per transaction (SRTTX). In a more recent post "DB2 LUW Performance: The Most Important Cost", we looked at the importance of measuring Bufferpool Logical Reads per Transaction (BPLRTX). If performing excessive and unnecessary logical I/O is the number one performance killer for a database (and it usually is), then performing excessive and unnecessary sorts is the number two performance killer in most databases.

Continue reading "DB2 LUW Performance: Sorts - The silent performance killer..."

Comment on this blog entry


DB2 LUW Performance: I/O Write Times (OWMS)

If your database updates its data via Inserts, Updates, Deletes, Imports, or Loads, then this blog post is for you. Write times tend to be slower than read times, and synchronous writes can be particularly painful. When tuning your databases, it is desirable to achieve a high percentage of Asynchronous writes as this type of write is faster. We need to learn the average write time for the database overall, and write times for each tablespace.

Continue reading "DB2 LUW Performance: I/O Write Times (OWMS)..."

Comments(3)


DB2 LUW Performance: I/O Read Times (ORMS)

In the next few blog posts, we'll take a look at formulas for time metrics so that we can understand "where the time goes" and uncover bottlenecks. Since both OLTP and Data Warehouse databases perform a great deal of I/O read activity, we'll begin by looking at metrics for computing important read times.

Continue reading "DB2 LUW Performance: I/O Read Times (ORMS)..."

Comment on this blog entry


DB2 LUW Performance: Building Trust with DB2 9 Autonomic Tuning

During a recent Webinar, and reiterated by attendees at this weeks IDUG Conference in Athens Greece, we've heard that very few organizations are taking advantage of DB2 9 autonomic tuning as implemented by the Self Tuning Memory Manager, or STMM. WHY? Because they don't trust it.

As with relationships between people, building trust takes time and requires a series of reliable and favorable experiences. In this blog post, we'll consider when and how to best engage STMM, and discuss how to build trust with this new DB2 9 capability.

Continue reading "DB2 LUW Performance: Building Trust with DB2 9 Autonomic Tuning..."

Comments(2)


DB2 LUW Performance: NUMBLOCKPAGES and APPR

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...

Continue reading "DB2 LUW Performance: NUMBLOCKPAGES and APPR..."

Comments(1)


DB2 LUW Performance: Index Cardinality

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...

Continue reading "DB2 LUW Performance: Index Cardinality..."

Comment on this blog entry


DB2 LUW Performance: Asked and Answered (BPLRTX)

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.

Continue reading "DB2 LUW Performance: Asked and Answered (BPLRTX)..."

Comments(1)


DB2 LUW Performance: Catalog Cache

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...

Continue reading "DB2 LUW Performance: Catalog Cache..."

Comments(1)


DB2 LUW Performance: Index Design Tips 1

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.

Continue reading "DB2 LUW Performance: Index Design Tips 1..."

Comment on this blog entry


DB2 LUW Performance: Tuning LOGBUFSZ

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.

Continue reading "DB2 LUW Performance: Tuning LOGBUFSZ..."

Comment on this blog entry


DB2 LUW Performance: Bufferpool Hit Ratios and Folly

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.

Continue reading "DB2 LUW Performance: Bufferpool Hit Ratios and Folly..."

Comments(3)


DB2 LUW Performance: Progress Review plus Closing Files

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.

Continue reading "DB2 LUW Performance: Progress Review plus Closing Files..."

Comment on this blog entry


DB2 LUW Performance: Detecting Index Leaf Page Scans

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.

Continue reading "DB2 LUW Performance: Detecting Index Leaf Page Scans..."

Comment on this blog entry


DB2 LUW Performance: The Most Important Cost

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.

Continue reading "DB2 LUW Performance: The Most Important Cost..."

Comments(8)


DB2 LUW Performance: More Key Costs

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.

Continue reading "DB2 LUW Performance: More Key Costs..."

Comment on this blog entry


DB2 LUW Performance: Key Cost Measures

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:

Continue reading "DB2 LUW Performance: Key Cost Measures..."

Comment on this blog entry


DB2 LUW Performance: Synchronous Read Percent (SRP)

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:

Continue reading "DB2 LUW Performance: Synchronous Read Percent (SRP)..."

Comments(1)


DB2 LUW Performance: Index Read Efficiency (IREF)

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:

Continue reading "DB2 LUW Performance: Index Read Efficiency (IREF)..."

Comments(1)


DB2 LUW Performance: Average Result Set Size (ARSS)

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

Continue reading "DB2 LUW Performance: Average Result Set Size (ARSS)..."

Comments(2)


Who Wants to Learn about DB2 LUW Performance?

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.

Continue reading "Who Wants to Learn about DB2 LUW Performance?..."

Comment on this blog entry