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.… Read the restContinue reading »
This technote caught my eye when @db2_support tweeted it the other day ago. It was titled as “DB2 might choose a sub-optimal query execution plan due to distribution statistics”. The title alone intrigued me. As I read through, I thought that I needed to do a bit of investigation to better understand the issue.… Read the restContinue reading »
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 was suddenly pegged at 100% CPU utilization.… Read the restContinue reading »
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 a rather busy database.… Read the restContinue reading »
This post is specific to DB2 9.7. In DB2 10.1 and above, Usage Lists would be a better way to handle this.
In the course of writing my recent developerWorks article on low-cardinality indexes and an associated presentation, I found that I have dozes of indexes with a cardinality of 1 that are also recently used.… Read the restContinue reading »
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.… Read the restContinue reading »
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.… Read the restContinue reading »
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.… Read the restContinue reading »
What is a table scan?
DB2 has a number of ways of accessing the data it needs, but the most basic one – the one that will always work to get what it needs – is the table scan. A Table scan means that DB2 will read through every single row in a table to get the data it needs.… Read the restContinue reading »