Meaning of "%Total CPU" and "%Busy CPU" in AWR

User_A7RKT

Hi guys,

Following is an excerpt from an AWR  report.

My questions are:

(1) What is the "%Total CPU" under  the Instance CPU?

Obviously it isn't how much CPU is consumed by the instance because the "%User" is 10.7% and there is no way for the Instance CPU consumption to be more than %User.

(2) What is the "%Busy CPU"? How come it is more than 100?

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
2	2	1	0.30	1.23	10.7	5.6	5.3	77.7

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
29.8	133.8	0.0

It's Oracle 12c R2 on Linux x86-64.

Jonathan Lewis

Franck Pachot wrote:
I think the formulas are something like: %Total CPU = (instance CPU from Time Model) / (num cpu * elapsed time)  %Busy CPU = (instance CPU from Time Model) / (OS non-idle time)

What it should be and what it seems to be can be two very different things.

I'm looking at a few AWR reports from a 12.1 system where

%Total CPU seems to be ("Total CPU time" from Time Model Statistics) / ("BUSY_TIME" + "IDLE_TIME" from Operating System Statistics)

and

%Busy CPU = ("Total CPU time" from Time Model Statistics) / "BUSY_TIME" from Operating System Statistics)

However, the Total CPU time is roughly 40%  greater than the BUSY_TIME (22,380 seconds vs. 15,893 seconds)! so the %Busy CPU is (quite correctly, for some value of "correct")  140.8%)

Picking just one report, though, I can see BUSY_TIME  + IDLE_TIME = 106,398, while elapsed time * CPU count = 114,416 so there's more than 8,000 seconds "lost" time.  (Naturally the confusion is compounded by the fact that it's 8 cores and 16 CPUs).

Regards

Jonathan Lewis

Jonathan Lewis

Unfortunately the numbers look almost self-consistent - provided you assume a programmer made silly mistake:

• "non-idle" CPU is (100 - 77.7)% = 22.3%  (Note that the sum of the 3 non-idle CPU values is NOT 22.3%)
• 29.8 / 22.3 = 1.3363

It looks to me as if the 1.3363 is supposed to be the percentage of the total non-idle CPU that has been used by your instance - but the programmer has got the division upside down. For an intuitive understanding the number should have been 22.3 / 29.8 = 74.8%.

(I think the difference between 1.3362 and 1.338 can be explained by the fact that the aritimetic being done to far more decimal places than the 1 d.p. shown for the Host CPU reports.)

Regards

Jonathan Lewis

Franck Pachot

Hi,

Can you show the time model statistics ('DB CPU' and 'background cpu time') and Operating System Statistics (BUSY_TIME, IDLE_TIME and NUM_CPUS) from the same report? And the elapsed time of the report

I think the formulas are something like:

  %Total CPU = (instance CPU from Time Model) / (num cpu * elapsed time)

  %Busy CPU = (instance CPU from Time Model) / (OS non-idle time)

Thanks,

Franck.

Jonathan Lewis

Franck Pachot wrote:
I think the formulas are something like: %Total CPU = (instance CPU from Time Model) / (num cpu * elapsed time)  %Busy CPU = (instance CPU from Time Model) / (OS non-idle time)

What it should be and what it seems to be can be two very different things.

I'm looking at a few AWR reports from a 12.1 system where

%Total CPU seems to be ("Total CPU time" from Time Model Statistics) / ("BUSY_TIME" + "IDLE_TIME" from Operating System Statistics)

and

%Busy CPU = ("Total CPU time" from Time Model Statistics) / "BUSY_TIME" from Operating System Statistics)

However, the Total CPU time is roughly 40%  greater than the BUSY_TIME (22,380 seconds vs. 15,893 seconds)! so the %Busy CPU is (quite correctly, for some value of "correct")  140.8%)

Picking just one report, though, I can see BUSY_TIME  + IDLE_TIME = 106,398, while elapsed time * CPU count = 114,416 so there's more than 8,000 seconds "lost" time.  (Naturally the confusion is compounded by the fact that it's 8 cores and 16 CPUs).

Regards

Jonathan Lewis

User_A7RKT

Elapsed time:

Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	888	06-Jun-19 22:30:11	56	.9
End Snap:	889	06-Jun-19 23:00:14	54	.9
Elapsed:		30.05 (mins)
DB Time:		27.36 (mins)

Time Model Statistics

Statistic Name	Time (s)	% of DB Time	% of Total CPU Time
DB CPU	778.03	47.39	84.42
sql execute elapsed time	667.41	40.66
PL/SQL execution elapsed time	264.23	16.10
parse time elapsed	10.95	0.67
repeated bind elapsed time	0.46	0.03
hard parse elapsed time	0.14	0.01
connection management call elapsed time	0.07	0.00
PL/SQL compilation elapsed time	0.03	0.00
sequence load elapsed time	0.00	0.00
DB time	1,641.63
background elapsed time	950.95
background cpu time	143.59		15.58
total CPU time	921.62

Operating System Statistics

Statistic	Value	End Value
FREE_MEMORY_BYTES	39,464,960	41,435,136
INACTIVE_MEMORY_BYTES	1,314,881,536	1,247,432,704
SWAP_FREE_BYTES	5,960,953,856	5,921,521,664
BUSY_TIME	68,905
IDLE_TIME	240,014
IOWAIT_TIME	16,408
NICE_TIME	0
SYS_TIME	17,172
USER_TIME	32,987

My observations:

DB CPU (778 seconds) is longer than the BUSY_TIME (68905/100= 689.05 seconds) which doesn't make sense

Jonathan proposed formulas applied in my report.