Wednesday, October 7, 2009

Debugging with The Dynamic Statement Cache


In DB2 version 8 one of my favorite features was introduced and that is, the explain stmt cache statement. This statement provides information regarding the content of the dynamic statement cache (which statements are cached and what is the cached access path), and if you have ifcid 318 turned on, you also receive statistics such as number of executions, cpu time etc.

Using that information I was able to discover what I believe is a bug. I had a table with a varchar(100) column which was indexed. I issued the following query:
select *
from myTable
where varchar_col like 'mumu%'

this query returns no rows, but took rather long to execute considering the fact it was doing a matching index scan (you can get the chosen access path by explaining the statement from the dynamic cache).
Using the explain output, I was able to see that this query performed about 4000 getpage requests. This figure seemed strange since the index had about 4000 leaf pages, so the only idea I could come up with is that db2 performed a non-mathcing index scan.

When I tried a different query such as
Select *
from myTable
where varchar_col like 'knut%'

the query did not return anyrows either, but it only performed 3 getpage operations (which seems reasonable since my index level was 3).

I believe this is a bug in DB2 since I can't think of any explanation to the above phenomenon. I tried a some other strings as well (that returns no rows), some did 4000 getpage operations, others did 3.

The lesson to be learned here is, get to know your dynamic statement cache table. It has information that might become handy in time of need.


No comments:

Post a Comment