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

  • From: Ls Cheng <exriscer@xxxxxxxxx>
  • To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 13 Nov 2012 13:06:02 +0100

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


Other related posts: