Re: CPU Per Exec - SQL Ordered By CPU Time

  • From: Vladimir Andreev <vandreev@xxxxxxxxx>
  • To: Oracle Dba Wannabe <oracledbawannabe@xxxxxxxxx>
  • Date: Thu, 25 Nov 2010 16:01:22 +0100

On Thu, Nov 25, 2010 at 15:24, Oracle Dba Wannabe <
oracledbawannabe@xxxxxxxxx> wrote:

> thanks - which we could say is brought on by the increase in cpu
> utilization that has resulted from an increase in executions of all sql's
> due to the increased number of users (user count increased by about 400
> during this interval)? Utilization is seen about 75% peak at 100% (for short
> intervals).
>

No, an increase of CPU utilization would only increase your time waiting for
CPU, which is not recorded in the CPU time statistic (it is included in the
elapsed time, though).

Increased concurrency leads to increased latch contention for all sorts of
latches (library cache, shared pool, buffer cache), which leads to a)
increased CPU times from spinning and b) much more increased elapsed times
from sleeping.
Increased user concurrency may also lead to increased I/O wait times, both
from thrashing the disks (longer waits) and from filling up of the cache
(more waits).
I guess this is why you're seeing a ~ 4-fold increase in CPU/exec, but an
11-fold increase in elapsed/exec (peak vs. off-peak).
CPU time accounting has its own problems on the OS level, too, whereas
elapsed time is measured much simpler and therefore more exact.

Cheers!
Flado

Other related posts: