DB CPU is much lower than CPU Time Reported by TOP SQL consumers

From: Ls Cheng <exriscer_at_gmail.com>
Date: Tue, 13 Nov 2012 13:06:02 +0100
Message-ID: <CAJ2-Qb_kAsmAOpQ3zEZtiDn8MMsk-_dzTVq32e2oip20mcaL9A_at_mail.gmail.com>



Hi
I have a 11.2.0.3 single instance running a WMS system called Selektron, which consumes tons of CPU. This database runs on IBM AIX 6.1 LPAR UnCapped configuration, using 16 Logical CPUs (SMT threads).

Since this is standard edition I have to use Statspack, no AWR or AS is available. I noticed in the rerports that in 1 hour period CPU time reports 8500 seconds but when I go to the top CPU consuming SQL statements the first 8 statements consumes a lot CPU more than the reported CPU usage.

  • CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
    ---------- ------------ ---------- ------ ---------- ---------------

    4407.08 20,294 0.22 51.6 10006.66 1,228,369,784 3703299877 3943.14 157,316 0.03 46.2 6915.60 1,034,202,723 1127338565 2358.20 269,711 0.01 27.6 4095.76 1,508,308,542 1995656981 2315.44 20,297 0.11 27.1 5370.62 890,383,597 945861069 1305.21 9,932 0.13 15.3 2483.90 331,327 1310406159 1125.79 230,223 0.00 13.2 1993.35 647,419,878 3511238358 1028.07 231,655 0.00 12.0 1765.88 672,812,377 932949595 1006.29 220,850 0.00 11.8 1727.03 641,393,198 3441867039*

Anyone seen this behaviour before?

TIA
--

LSC
--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 13 2012 - 13:06:02 CET

Original text of this message