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!