query response time and cpu usage

[shawno]

Hello,

We are currently in the process of migrating our databases from a relic
of a server to a new 4 processor dual-core box with 4 gigs of RAM.
Overall, database performance is obviously dramatically improved.
However, I've noticed something odd with some of our larger tables
(which have between 1 and 1.5 million records). Performance can differ
greatly when performing simple LIKE queries such as the following:

select record_id from <tablenamewhere <fieldnamelike '%blah%'

Sometimes a result set will be returned very quickly (2 seconds or
almost immediately) while other times the exact same query will take 20
seconds to a minute. There does not appear to be any pattern to when
it is fast and when it is not. (I've done a re-org and runstats)

Now here's where it gets interesting. If I bring up task manager on
the server and monitor the cpu usage when doing such queries, on the
queries that return fast there is an expected large spike in the graph
across all the cpus. On the queries that take a long time, the cpu
usage appears to be very minimal (just little bumps).

Keep in mind that I am no DBA, just a java developer so maybe I'm
missing something really simple here or totally misinterpreting what I
see. Any suggestions on what is going on here would be greatly
appreciated, thanks!

[Art S. Kagel]

shawno wrote:

Hello,

We are currently in the process of migrating our databases from a relic
of a server to a new 4 processor dual-core box with 4 gigs of RAM.
Overall, database performance is obviously dramatically improved.
However, I've noticed something odd with some of our larger tables
(which have between 1 and 1.5 million records). Performance can differ
greatly when performing simple LIKE queries such as the following:

select record_id from <tablenamewhere <fieldnamelike '%blah%'

Sometimes a result set will be returned very quickly (2 seconds or
almost immediately) while other times the exact same query will take 20
seconds to a minute. There does not appear to be any pattern to when
it is fast and when it is not. (I've done a re-org and runstats)

Now here's where it gets interesting. If I bring up task manager on
the server and monitor the cpu usage when doing such queries, on the
queries that return fast there is an expected large spike in the graph
across all the cpus. On the queries that take a long time, the cpu
usage appears to be very minimal (just little bumps).

Sounds to me like the slower queries are IO bound ie are not finding the
data and/or index pages they need in cache and so are going to disk. The
faster ones are simply finding the data already in the cache so CPU time is
burning filtering rows for the LIKE clause.

Keep in mind that I am no DBA, just a java developer so maybe I'm
missing something really simple here or totally misinterpreting what I
see. Any suggestions on what is going on here would be greatly
appreciated, thanks!

Art S. Kagel

[shawno]

Hi,
That was my initial thought, but sometimes doing the exact same query
takes just a long as the first time. Other times if I try querying on
something I have not tried before, its blazing fast, and than the next
time (same query) its slow which is just the opposite of what you would
expect.
The columns I am querying are not indexed since I am performing a full
like ('%whatever%') and it is my understanding that DB2 will not use an
index for such a query since it would not help.

Art S. Kagel wrote:

Sounds to me like the slower queries are IO bound ie are not finding the
data and/or index pages they need in cache and so are going to disk. The
faster ones are simply finding the data already in the cache so CPU time is
burning filtering rows for the LIKE clause.

[ChrisC]

shawno wrote:

Hi,
That was my initial thought, but sometimes doing the exact same query
takes just a long as the first time. Other times if I try querying on
something I have not tried before, its blazing fast, and than the next
time (same query) its slow which is just the opposite of what you would
expect.

That can still happen, IF you are not the sole user of the system. So,
if another query from somewhere else does a full scan of the table, it
will put those rows into the db buffers, and when you run your first
new query, it will find all of the needed data in memory and run fast
(using lots of CPU). Then, some other query runs against other tables,
needs pages in the buffer, and so DB2 starts tossing data from you
table out of memory, and when you run the exact same query later, DB2
needs to pull that data back in off of disc, which is slow and doesn't
use as much CPU.

-Chris

[shawno]

Hi,
Actually, at this point I am the only user accessing the database as it
still in the development phase.
I did notice something else that maybe somebody can shed some light on
(maybe I'll start a new thread as well). In command editor, I tried
doing an 'execute and access plan' so that it created the little
flowchart diagram and there was a difference.
For the fast queries, the access plan looked like:

[database name] ---[tbscan(3) 531,908] ---[return(1) 531,908]

The slow queries were as follows:

[database name] ---[tbscan(5) 1,013,551] ---[tqueue(3)] --->
[return(1) 1,013,551]

The slower queries had a tqueue entry, whatever that is.
ChrisC wrote:

That can still happen, IF you are not the sole user of the system. So,
if another query from somewhere else does a full scan of the table, it
will put those rows into the db buffers, and when you run your first
new query, it will find all of the needed data in memory and run fast
(using lots of CPU). Then, some other query runs against other tables,
needs pages in the buffer, and so DB2 starts tossing data from you
table out of memory, and when you run the exact same query later, DB2
needs to pull that data back in off of disc, which is slow and doesn't
use as much CPU.

-Chris