Re: DB CPU is much lower than CPU Time Reported by TOP SQL consumers
From: Karl Arao <karlarao_at_gmail.com>
Date: Tue, 13 Nov 2012 15:13:57 -0600
Message-ID: <CACNsJnfADQba8iwisNf2t5diPhzX6jc0c5jd25ypSqrS10K5yg_at_mail.gmail.com>
Can you post the Top 5 timed events and Begin,End,Elapsed, DB Time sections of your AWR?
most likely this is a CPU starvation related.. In AWR/Statspack, that "Captured SQL.. CPU" section is being pulled from sum(cpu_time_delta) of dba_hist_sqlstat and divided by 'DB CPU' by the time model which only accounts for the "real CPU cycles" which gives you a lower value for the denominator since most of the CPU time is spent on run queue and not accounted
Date: Tue, 13 Nov 2012 15:13:57 -0600
Message-ID: <CACNsJnfADQba8iwisNf2t5diPhzX6jc0c5jd25ypSqrS10K5yg_at_mail.gmail.com>
Can you post the Top 5 timed events and Begin,End,Elapsed, DB Time sections of your AWR?
most likely this is a CPU starvation related.. In AWR/Statspack, that "Captured SQL.. CPU" section is being pulled from sum(cpu_time_delta) of dba_hist_sqlstat and divided by 'DB CPU' by the time model which only accounts for the "real CPU cycles" which gives you a lower value for the denominator since most of the CPU time is spent on run queue and not accounted
But the beauty about the time model is when database foreground goes from on-CPU to on-run queue to on-CPU the time spent on run queue will be added to 'DB Time' but not visible in 'DB CPU' or in any wait event... and that's what you see as "CPU wait" as a light green color in the OEM.
you can use the following to compute for CPU wait..
CPU Wait = DB Time – ( sum(events) + DB CPU )
You should be able to account for this even if you are using just the Statspack this link contains some background http://goo.gl/trwKp
-- Karl Arao karlarao.wordpress.com karlarao.tiddlyspot.com twitter.com/karlarao -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 13 2012 - 22:13:57 CET