CPU time and Statspack

[Bore]

Hello,
I am looking at a statspack output and what I see is
....
Elapsed: 291.62 (mins)
....
Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Ela Time

-------------------------------------------- ------------ ----------- --------

CPU time 439,177 86.13
.....
The machine has 8 CPUs .
a time of 439177 sec is around 7319 min.
On the other hand, the total maximum CPU processing time is 291.62*8 CPUs= 2332.96 min.
1)Can someone clarify that?
Appears that CPU Time reffers not to the CPU processing time, but to the time session waits for CPU.
2) If so, with these figures can we conclude that the machine is highly overloaded in terms of CPU?
3) Any idea what could cause the CPU overloading


Thanks

[pando]

interval time?

[Bore]

Elapsed: 291.62 (mins)

[abhaysk]

too high GETS can cause CPU overloading. btw, for your first and second point

1)Can someone clarify that?
Appears that CPU Time reffers not to the CPU processing time, but to the time session waits for CPU.

-- It means total CPU spent for parse/recursive call/execute.. In simple terms 'CPU used by this session' (Service Time).

2) If so, with these figures can we conclude that the machine is highly overloaded in terms of CPU?

it may not necessarily be so, it may be due to cumulative CPU Time for N sessions.

But since your "% Total Ela Time" for CPUT time is 86.13, you may have to look at SQL's ordered by GETS ( i would particualrly see GETS PER EXEC )


Abhay

[Bore]

Thanks for the replay,
but still..
my point is that for 291 min ( which is the time period we measure) 8 processors may spend no more than 2332.96 minutes in processing ( i.e 291 min * 8 processors)
How can we then claim that even the comulative CPU is 439177 sec (or 7319 min.)
It is much jigher than physically possible CPU time

Thanks
Boris

[reydp]

may I ask where did you get this figuer?
---------
the total maximum CPU processing time is 291.62*8 CPUs= 2332.96 min.
------
for 291 min ( which is the time period we measure) 8 processors may spend no more than 2332.96 minutes in processing ( i.e 291 min * 8 processors)
How can we then claim that even the comulative CPU is 439177 sec (or 7319 min.)
-----------
Can this be seen in a statspack output or something?

[Bore]

Not sure, that's why I'm asking.
The Statspack, in the section Top 5 wait events says
CPU Time 439177
I understand that as time spent by all the sessions during the measuring period, in CPU processing.
If I am wrong, please correct me.
Also, my understanding is that for 291 min, 8 processors can spend no more than 291 mins in processing. If I am wrong again, please let me know and clarify that

Thanks

[reydp]

as what Krishna said. Service Time. and follow the explaination.
--------------
If you are not satisfied try to find docs that explain the interpretation of statspack output.

[abhaysk]

ok i think u didnt get "cumulative CPU Time for N sessions", well i will give you a simple example

Consider 2 sessions seeking CPU simultaneouly and have spent N seconds each.. Actual time elapsed is N seconds, but CPU Time computed by ORACLE and as shown in AWR/Statspack report is 2N.

Abhay

[Bore]

OK thanks, that's my understanding as well,
The point is that since the CPU time reported in the statspack is lot more than the physical time between the snapshots, multiplied by the number of processors, that would mean that the time sessions waits actually for CPU but not using CPU is also counted in the statspack as CPU time.
That on the other hand will mean that CPU is a bottleneck since sessions spends lots of time waiting for CPU right?

P.S.
I see lots of db buffer gets and I identified the SQL causing the number of gets, but I am interested generally does the fact that CPU Time in the statspack is lot more that the intterval multiplied by processots, means that CPU is a bottleneck

Thanks for the discussion
Boris