| CPU used by user SQLs [message #467116] |
Thu, 22 July 2010 05:05  |
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
|
Senior Member |
|
|
We have a two node RAC each with 8 CPUs. I run the following sql to get the number of sqls and CPU seconds consumed by two users.
Select to_char(dhs.begin_interval_time,'YYYY-MM-DD HH24') as time,sum(dhsq.executions_delta) as execs, round(sum(dhsq.cpu_time_delta)/1000000) as cpu From Dba_Hist_Snapshot Dhs, Dba_Hist_Sqlstat Dhsq Where Dhs.Snap_Id = Dhsq.Snap_Id And Dhsq.Parsing_Schema_Name in ('A','B') and dhsq.instance_number=1 GROUP BY to_char(dhs.begin_interval_time,'YYYY-MM-DD HH24') order by 1
another sql for instance number 2.
What does it mean in practice when the values in the CPU column exceed the total number of CPU available in the server? In this case each server will have 8 * 60 * 60 = 28800 CPU seconds per hour.
But I see over 30000 sometimes. But sar output doesn't seem to have 99% CPU, at highest we get 78%.
Since it's a RAC assuming some cpu time comes from the other node I've checked the combined values for the hour and even that at times exceeds 2 * 28800.
Found this on the web which says Quote: unless you use more than 28800 CPU seconds within the hour, you are under your 100% CPU limit.
So my questions are
1. Is the SQL written is valid to get number CPU seconds used?
2. When the cpu column value exceed the server cpu capacity does this means CPU saturation? How it is possible that more cpu seconds have been consumed than the system capacity?
[Updated on: Thu, 22 July 2010 11:20] Report message to a moderator |
|
|
|
| Re: CPU used by user SQLs [message #467227 is a reply to message #467116] |
Thu, 22 July 2010 14:55  |
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
|
Senior Member |
|
|
|
it seem oracle doesn't count the cpu seconds until call finishes, so a call can start in one hour and finish in another hour adding to the cpu usage of that hour so the total used cpu seconds seems higher than the available capacity. At least that's what said here
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
