RE: Awr waits over 100%
Date: Thu, 29 Sep 2011 12:39:06 -0400
Message-ID: <043701cc7ec6$4f89b130$ee9d1390$_at_gmail.com>
Thanks, that's a very interesting distinction. I'll look into it.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Marcus Mönnig
Sent: Thursday, September 29, 2011 2:12 AM
To: ORACLE-L
Subject: Re: Awr waits over 100%
Hi Kenneth,
2011/9/29 Kenneth Naim <kennethnaim_at_gmail.com>:
> But shouldn't the waits + cpu time be divided by the db time so it
> can't never exceed 100% unless it is double counting like Marcus said.
> If it were dividing by the elapsed time then it would routinely go
> over 100% but that isn't the case in hundreds of awr's I've looked at
> over the years. It begs the question isn't the double counting a bug?
the "DB time" value used as the divisor for calculating "% Total Call Time" value comes from Oracle's Time Model Statistics and is defined as:
"Database time is calculated by aggregating the CPU time and wait time of all user sessions not waiting for idle wait events (nonidle user sessions)."
So, it's not the sum for all sessions, but only for user sessions. All cpu and wait times for background sessions are in the numerator, but not in the denominator. In your case, the times for "log file parallel write" and possible parts of other wait events/CPU time refer to the LGWR background process.
You can look at the "Background Wait Events" section in the AWR report for all background wait events and "background cpu time" value in the "Time Model System Stats" to get the CPU times for background processes.
Cheers,
Marcus
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 29 2011 - 11:39:06 CDT