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

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Tue, 13 Nov 2012 14:03:13 +0100
Message-ID: <CAJu8R6isCHoMryeehtq8+eDH8u6svnGO+5usOfsGx4=84T8=7w_at_mail.gmail.com>



How many CPU your system has?
With one hour of StatsPack report and 8500 CPU seconds you have

3600/8500 = 0,42 CPU used at 100%
Divide the above number with the number of CPU your system has, and you will have an idea about wether you are CPU bound or not

Best regards

Mohamed Houri
www.hourim.wordpress.com

2012/11/13 Ls Cheng <exriscer_at_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
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


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

Original text of this message