High Execution Time--Low CPU time for statements

[Evan Smith]

Scenario: new functionality was recently deployed in a 3-tier business
application. Since deployment, CPU use has shot up to very high
levels.

Using event monitors to try to track down the culprits hasn't fingered
any bad query in particular who's hogging CPU. One interesting anomaly
is that there is a single type of query that averages 70 seconds of
real time (with highs of 100-300 seconds) while expending zero seconds
of CPU. The query in question takes the form "SELECT * FROM TABLE
WHERE COLUMN = 'VALUE'" where the value is the first element in the
primary key. (The other part of key is a sequence number--with an
average of 2 rows for each main value.) We suspected waiting on locks
at first, but none of our attempts to monitor locking through snapshot
monitor has really shown anything conclusive.

Our suspicion is that if we can figure out what's causing the high
execution times for this query, will probably get us to the solution
of high CPU utilization. Does anyone have any suggestions for tracing
this other than event monitors (for statements and deadlocks) snapshot
monitors for locks?

Platform is 7.2 FP 8 on AIX 4.3

Thanks,
Evan

[Philip Sherman]

Do you have multiple requestors (different hardware boxes) going after
the database concurrently? I would normally expect this in a multi-tier
environment. A simple example would be two web servers, sharing a
request load, each directly accessing the same database.

Phil Sherman
Evan Smith wrote:

Scenario: new functionality was recently deployed in a 3-tier business
application. Since deployment, CPU use has shot up to very high
levels.

Using event monitors to try to track down the culprits hasn't fingered
any bad query in particular who's hogging CPU. One interesting anomaly
is that there is a single type of query that averages 70 seconds of
real time (with highs of 100-300 seconds) while expending zero seconds
of CPU. The query in question takes the form "SELECT * FROM TABLE
WHERE COLUMN = 'VALUE'" where the value is the first element in the
primary key. (The other part of key is a sequence number--with an
average of 2 rows for each main value.) We suspected waiting on locks
at first, but none of our attempts to monitor locking through snapshot
monitor has really shown anything conclusive.

Our suspicion is that if we can figure out what's causing the high
execution times for this query, will probably get us to the solution
of high CPU utilization. Does anyone have any suggestions for tracing
this other than event monitors (for statements and deadlocks) snapshot
monitors for locks?

Platform is 7.2 FP 8 on AIX 4.3

Thanks,
Evan

[Evan Smith]

There are multiple requestors. There are 3 web servers talking to 3
application servers which in turn, each have a connection pool to a single
database in which connections are reused by different threads. All
transactions are done with auto-commit, so we don't expect any big
transactions with wide windows of exposure.

In this particular scenario, the items which are experiencing high execution
times are queries with a highly selective predicate, so we don't expect
large numbers of rows to be locked (one row usually, sometimes two).

This is a mature application which hasn't experienced anything out of the
ordinary as far as this goes. We would get a deadlock once in a blue moon
before with extremely high volume, but now it's rampant. With a continous
snapshot for locks monitor running, we see a little bit of lock wait on a
different resource, but that generally clears itself up by the next snapshot
a few seconds later. We are pretty sure it's a problem somewhere in the new
modules deployed, but are just having a bear of a time trying to pin it
down.

A side question here. Does waiting on locks burn a lot of CPU?

Thanks,
Evan

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:FC***************@newssvr19.news.prodigy.com. ..

Do you have multiple requestors (different hardware boxes) going after
the database concurrently? I would normally expect this in a multi-tier
environment. A simple example would be two web servers, sharing a
request load, each directly accessing the same database.

Phil Sherman

[Philip Sherman]

Where's the high cpu? UDB server? Application server? Web server? Does
your application use dynamic SQL or bound plans? If bound; what
language? All of these can supply clues to help resolve this type of
problem.

You may need to run some statement traces and perform a bit of analysis
on them to determine the culprit.

Phil Sherman
Evan Smith wrote:

There are multiple requestors. There are 3 web servers talking to 3
application servers which in turn, each have a connection pool to a single
database in which connections are reused by different threads. All
transactions are done with auto-commit, so we don't expect any big
transactions with wide windows of exposure.

In this particular scenario, the items which are experiencing high execution
times are queries with a highly selective predicate, so we don't expect
large numbers of rows to be locked (one row usually, sometimes two).

This is a mature application which hasn't experienced anything out of the
ordinary as far as this goes. We would get a deadlock once in a blue moon
before with extremely high volume, but now it's rampant. With a continous
snapshot for locks monitor running, we see a little bit of lock wait on a
different resource, but that generally clears itself up by the next snapshot
a few seconds later. We are pretty sure it's a problem somewhere in the new
modules deployed, but are just having a bear of a time trying to pin it
down.

A side question here. Does waiting on locks burn a lot of CPU?

Thanks,
Evan

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:FC***************@newssvr19.news.prodigy.com. ..

Do you have multiple requestors (different hardware boxes) going after
the database concurrently? I would normally expect this in a multi-tier
environment. A simple example would be two web servers, sharing a
request load, each directly accessing the same database.

Phil Sherman