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