Forum Stats

  • 3,853,636 Users
  • 2,264,249 Discussions
  • 7,905,421 Comments

Discussions

Categories

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

User_A7RKT
User_A7RKT Member Posts: 230 Blue Ribbon
edited Jun 9, 2019 5:20PM in General Database Discussions

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

CPUsCoresSocketsLoad Average BeginLoad Average End%User%System%WIO%Idle
2210.301.2310.75.65.377.7

Instance CPU

%Total CPU%Busy CPU%DB time waiting for CPU (Resource Manager)
29.8133.80.0

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

Franck Pachot
· Share on TwitterShare on Facebook

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,059 Blue Diamond
    edited Jun 9, 2019 11:59AM Answer ✓ 
    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

    Franck Pachot
    · Share on TwitterShare on Facebook

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,059 Blue Diamond
    edited Jun 9, 2019 5:56AM

    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

    · Share on TwitterShare on Facebook
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited Jun 9, 2019 10:33AM

    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.

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,059 Blue Diamond
    edited Jun 9, 2019 11:59AM Answer ✓ 
    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

    Franck Pachot
    · Share on TwitterShare on Facebook
  • User_A7RKT
    User_A7RKT Member Posts: 230 Blue Ribbon
    edited Jun 9, 2019 5:17PM

    Elapsed time:

    Snap IdSnap TimeSessionsCursors/Session
    Begin Snap:88806-Jun-19 22:30:1156.9
    End Snap:88906-Jun-19 23:00:1454.9
    Elapsed:30.05 (mins)
    DB Time:27.36 (mins)

    Time Model Statistics

    Statistic NameTime (s)% of DB Time% of Total CPU Time
    DB CPU778.0347.3984.42
    sql execute elapsed time667.4140.66
    PL/SQL execution elapsed time264.2316.10
    parse time elapsed10.950.67
    repeated bind elapsed time0.460.03
    hard parse elapsed time0.140.01
    connection management call elapsed time0.070.00
    PL/SQL compilation elapsed time0.030.00
    sequence load elapsed time0.000.00
    DB time1,641.63
    background elapsed time950.95
    background cpu time143.5915.58
    total CPU time921.62

    Operating System Statistics

    StatisticValueEnd Value
    FREE_MEMORY_BYTES39,464,96041,435,136
    INACTIVE_MEMORY_BYTES1,314,881,5361,247,432,704
    SWAP_FREE_BYTES5,960,953,8565,921,521,664
    BUSY_TIME68,905
    IDLE_TIME240,014
    IOWAIT_TIME16,408
    NICE_TIME0
    SYS_TIME17,172
    USER_TIME32,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.

    · Share on TwitterShare on Facebook