Category: Explains

4

DB2 Basics: db2top

There are a lot of things I can cover on db2top, and probably more tips and tricks using db2top than many other tools out there. Searching the web on db2top gets more good results than on many other db2 topics. I thought I’d start with some of the basics. Using db2top requires some general knowledge of how db2 works. I...

6

When Index Scans Attack!

We all know that table scans can be (but aren’t always) a negative thing. I have spent less time worrying about index scans, though. Index access = good, right? I thought I’d share a recent scenario where an index scan was very expensive. Maybe still better than a table scan, but with one index, I reduced the impact of a...

3

The Danger of FETCH FIRST ROW ONLY

There are a a vast array of things you can do in SQL. But just because you can doesn’t always mean that you should. I recently ran into an interesting situation with FETCH FIRST ROW ONLY. How the Problem Manifested The day after new code was deployed, a production database system that usually runs with 40% CPU utilization or less...

5

Using a Statistical View to Improve Query Performance

Sometimes just using the index advisor and a bit of common sense, you can address SQL performance issues. But other times, you have to dig a bit deeper. I recently found the following query and 300 of its closest cousins (differing only in the length of the second in-list) were eating 30% of the CPU time used by queries on...

2

Tips on Using db2advis with Multiple Queries

Many times, the SQL analysis I do is extremely focused – mostly on SQL that is a proven resource hog or a suspected problem. Analyzing all SQL used in a particular process can be a bit different, and may not be something that is frequently done in WebSphere Commerce databases. Recently, I had the output of statement event monitors while...

5

Explain Part 3 – Visual Explain

Please start with the first two articles in this series: Explain Part 1 – Explain and the DB2 Optimizer Explain Part 2 – Command Line Explain Plans Using db2exfmt Explain is one of the few areas where I can see the argument for using a GUI. There are some neat features in Visual Explain and I haven’t seen many bugs...

8

Explain Part 1 – Explain and the DB2 Optimizer

Generating an Explain Plan is easy… Properly reading an Explain Plan is harder… Taking appropriate action is harder still. When I started my career as a physical DBA with IBM Global Services, generating Explain Plans or even general analysis of SQL was not really a part of my job description. As I learned more about performance tuning and the limitiations...