This discussion is archived
7 Replies Latest reply: Mar 11, 2011 11:31 PM by user3206995 RSS

Performance View V$OSSTAT

user3206995 Newbie
Currently Being Moderated
I am trying to understand the busy_time column in the v$osstat view.

I took a before and snap of this view.

VALUE STAT_NAME
---------- ----------------------------------------------------------------
64 NUM_CPUS
7776667365 IDLE_TIME
357220150 BUSY_TIME
159550984 USER_TIME
197669166 SYS_TIME
0 IOWAIT_TIME
121468398 AVG_IDLE_TIME
5539550 AVG_BUSY_TIME
2451063 AVG_USER_TIME
3046655 AVG_SYS_TIME
0 AVG_IOWAIT_TIME
11140800 OS_CPU_WAIT_TIME
0 RSRC_MGR_CPU_WAIT_TIME
5.10546875 LOAD
8 NUM_CPU_CORES
1 NUM_CPU_SOCKETS
6.8585E+10 PHYSICAL_MEMORY_BYTES
1.6960E+10 VM_IN_BYTES
8192 VM_OUT_BYTES
49152 TCP_SEND_SIZE_DEFAULT
1048576 TCP_SEND_SIZE_MAX
49152 TCP_RECEIVE_SIZE_DEFAULT
1048576 TCP_RECEIVE_SIZE_MAX


After :

VALUE STAT_NAME
---------- ----------------------------------------------------------------
64 NUM_CPUS
7814976860 IDLE_TIME
360357230 BUSY_TIME
160430595 USER_TIME
199926635 SYS_TIME
0 IOWAIT_TIME
122066763 AVG_IDLE_TIME
5588356 AVG_BUSY_TIME
2464596 AVG_USER_TIME
3081702 AVG_SYS_TIME
0 AVG_IOWAIT_TIME
11206700 OS_CPU_WAIT_TIME
0 RSRC_MGR_CPU_WAIT_TIME
5.21484375 LOAD
8 NUM_CPU_CORES
1 NUM_CPU_SOCKETS
6.8585E+10 PHYSICAL_MEMORY_BYTES
1.7078E+10 VM_IN_BYTES
8192 VM_OUT_BYTES
49152 TCP_SEND_SIZE_DEFAULT
1048576 TCP_SEND_SIZE_MAX
49152 TCP_RECEIVE_SIZE_DEFAULT
1048576 TCP_RECEIVE_SIZE_MAX


I had elapsed time of 5Mins

I now attempted to calculate the system CPU Utilization over this period.

I am using hyperthreading which gives me 64 CPUs


My calculation is:
substract After busy_time - Before busy_time

36035723 - 357220150 = 3137080 hundredeths of a sec

available CPU time 64 * 5*60 = 19200 secs

now I am trying to calculate CPU Utilization

U=R/C 31370.80/19200 = 1.63 or 163%.

Is this possible or is my calculation incorrect ?

Thank you in adavnce
u
  • 1. Re: Performance View V$OSSTAT
    sb92075 Guru
    Currently Being Moderated
    now I am trying to calculate CPU Utilization
    What about processes other than Oracle that consume CPU cycles?

    IMO, OS statistics should be reported by the OS & not some client application.
  • 2. Re: Performance View V$OSSTAT
    CharlesHooper Expert
    Currently Being Moderated
    user3206995 wrote:
    I am trying to understand the busy_time column in the v$osstat view.

    I took a before and snap of this view.
    VALUE STAT_NAME
    ---------- ----------------------------------------------------------------
    64 NUM_CPUS
    7776667365 IDLE_TIME
    357220150 BUSY_TIME
    159550984 USER_TIME
    197669166 SYS_TIME
    0 IOWAIT_TIME
    121468398 AVG_IDLE_TIME
    5539550 AVG_BUSY_TIME
    2451063 AVG_USER_TIME
    3046655 AVG_SYS_TIME
    0 AVG_IOWAIT_TIME
    11140800 OS_CPU_WAIT_TIME
    0 RSRC_MGR_CPU_WAIT_TIME
    5.10546875 LOAD
    8 NUM_CPU_CORES
    1 NUM_CPU_SOCKETS
    6.8585E+10 PHYSICAL_MEMORY_BYTES
    1.6960E+10 VM_IN_BYTES
    8192 VM_OUT_BYTES
    49152 TCP_SEND_SIZE_DEFAULT
    1048576 TCP_SEND_SIZE_MAX
    49152 TCP_RECEIVE_SIZE_DEFAULT
    1048576 TCP_RECEIVE_SIZE_MAX
    After :
    VALUE STAT_NAME
    ---------- ----------------------------------------------------------------
    64 NUM_CPUS
    7814976860 IDLE_TIME
    360357230 BUSY_TIME
    160430595 USER_TIME
    199926635 SYS_TIME
    0 IOWAIT_TIME
    122066763 AVG_IDLE_TIME
    5588356 AVG_BUSY_TIME
    2464596 AVG_USER_TIME
    3081702 AVG_SYS_TIME
    0 AVG_IOWAIT_TIME
    11206700 OS_CPU_WAIT_TIME
    0 RSRC_MGR_CPU_WAIT_TIME
    5.21484375 LOAD
    8 NUM_CPU_CORES
    1 NUM_CPU_SOCKETS
    6.8585E+10 PHYSICAL_MEMORY_BYTES
    1.7078E+10 VM_IN_BYTES
    8192 VM_OUT_BYTES
    49152 TCP_SEND_SIZE_DEFAULT
    1048576 TCP_SEND_SIZE_MAX
    49152 TCP_RECEIVE_SIZE_DEFAULT
    1048576 TCP_RECEIVE_SIZE_MAX  
    I had elapsed time of 5Mins

    I now attempted to calculate the system CPU Utilization over this period.

    I am using hyperthreading which gives me 64 CPUs


    My calculation is:
    substract After busy_time - Before busy_time

    36035723 - 357220150 = 3137080 hundredeths of a sec

    available CPU time 64 * 5*60 = 19200 secs

    now I am trying to calculate CPU Utilization

    U=R/C 31370.80/19200 = 1.63 or 163%.

    Is this possible or is my calculation incorrect ?

    Thank you in adavnce
    u
    Here is the formula that you should use:
    BUSY_TIME_delta / (BUSY_TIME_delta + IDLE_TIME_delta)
    Calculating the utilization at the server-wide scope:
    SELECT
      (360357230 - 357220150) / ( (360357230 - 357220150) + (7814976860 - 7776667365) ) * 100 UTIL
    FROM
      DUAL;
     
          UTIL
    ----------
    7.56897283
    So, just over 7.5% utilization.

    Part 1 of a 4 part series that breaks down CPU utilization and wait events:
    http://hoopercharles.wordpress.com/2011/02/28/oracle-database-time-model-viewer-in-excel-1/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 3. Re: Performance View V$OSSTAT
    user3206995 Newbie
    Currently Being Moderated
    Thanks Charles.
  • 4. Re: Performance View V$OSSTAT
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Charles Hooper wrote:

    Calculating the utilization at the server-wide scope:
    SELECT
    (360357230 - 357220150) / ( (360357230 - 357220150) + (7814976860 - 7776667365) ) * 100 UTIL
    FROM
    DUAL;
    
    UTIL
    ----------
    7.56897283
    So, just over 7.5% utilization.

    Part 1 of a 4 part series that breaks down CPU utilization and wait events:
    http://hoopercharles.wordpress.com/2011/02/28/oracle-database-time-model-viewer-in-excel-1/
    Charles,

    Do you have any thoughts on the observation that the time recorded in this view is supposed to be in hundredths of a second ?

    Using the figures from the OP: we get: ( (360357230 - 357220150) + (7814976860 - 7776667365) ) / (64 CPUs * 5 minutes * 60 seconds ) = 2158 when it should be 100.


    I'd be interested to see what the "CPU time" figures was in the Top 5 (or the "cpu used by this session" in Instance Activity, if CPU time does appear in Top 5).

    Regards
    Jonathan Lewis
  • 5. Re: Performance View V$OSSTAT
    CharlesHooper Expert
    Currently Being Moderated
    Jonathan,

    Nice sanity check.

    Elapsed time between the capture start and stop times, based on the BUSY_TIME and IDLE_TIME statistics:
    SELECT
      ( (360357230 - 357220150) + (7814976860 - 7776667365) ) / 100 / 64 / 60 MINUTES
    FROM
      DUAL;
     
       MINUTES
    ----------
    107.933789
    The OP stated that the elapsed time was roughly 5 minutes, so the statistics are inconsistent.

    The OS_CPU_WAIT_TIME statistic seems to indicate that processes spent almost 11 minutes in this 5 minute time period waiting to be scheduled to run - that seems to be inconsistent with the 7.6% average CPU utilization, unless the OS nice utility were used, or the processes were caged to a small number of CPUs, or a reporting bug was encountered.
    SELECT
      (11206700 - 11140800) / 100 / 60 OS_CPU_WAIT_TIME_MINUTES
    FROM
      DUAL;
     
    OS_CPU_WAIT_TIME_MINUTES
    ------------------------
                  10.9833333
    Using just the USER_TIME and SYS_TIME statistics, if the elapsed time was 5 minutes, the server's CPUs would have to be 163.4% busy:
    SELECT
      ( (160430595 - 159550984) + (199926635 - 197669166) ) / 100 / 64 / 300 * 100 AVG_CPU_BUSY_PER
    FROM
      DUAL;
     
    AVG_CPU_BUSY_PER
    ----------------
          163.389583
    The OP might want to verify that the actual elapsed time between the statistics is 5 minutes. After verifying that, the OP might want to check out the following Metalink (MOS) articles:
    Bug 7430365: INCORRECT VALUES FOR USER_TIME IN V$OSSTAT (3.79 hours per CPU per elapsed hour)
    Bug 3574504: INCORRECT STATISTICS IN V$OSSTAT IN HP-UX
    Bug 5933195: NUM_CPUS VALUE IN V$OSSTAT IS WRONG
    Bug 5639749: CPU_COUNT NOT SHOWN PROPERLY FROM THE DATABASE
    Bug 10427553: HOW DOES V$OSSTAT GET IT'S INFORMATION ON AIX
    Bug 9228541: CPU TIME REPORTED INCORRECTLY IN V$SYSMETRIC_HISTORY (3.75 hours per CPU per elapsed hour)
    Doc ID 889396.1: Very large value for OS_CPU_WAIT_TIME FROM V$OSSTAT / AWR Report
    Bug 7447648: OS_CPU_WAIT_TIME VALUE FROM V$OSSTAT IS INCORRECT

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 6. Re: Performance View V$OSSTAT
    user3206995 Newbie
    Currently Being Moderated
    Guys

    Can some one explain jonathan's comment

    "( (360357230 - 357220150) + (7814976860 - 7776667365) ) / (64 CPUs * 5 minutes * 60 seconds ) = 2158 when it should be 100" ????

    This other thing is the calculation that Jonathan used using sys_time and user_time

    gives the same answer as I calculated.

    SELECT
    ( (160430595 - 159550984) + (199926635 - 197669166) ) / 100 / 64 / 300 * 100 AVG_CPU_BUSY_PER
    FROM
    DUAL;

    AVG_CPU_BUSY_PER
    ----------------
    163.389583



    My calculation is:
    substract After busy_time - Before busy_time

    36035723 - 357220150 = 3137080 hundredeths of a sec

    available CPU time 64 * 5*60 = 19200 secs

    now I am trying to calculate CPU Utilization

    U=R/C 31370.80/19200 = 1.63389583 or 163.389583

    Is this possible or is my calculation incorrect ?

    Thank you in adavnce
  • 7. Re: Performance View V$OSSTAT
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user3206995 wrote:
    Guys

    Can some one explain jonathan's comment

    "( (360357230 - 357220150) + (7814976860 - 7776667365) ) / (64 CPUs * 5 minutes * 60 seconds ) = 2158 when it should be 100" ????
    Just turning the equation round, and following up my comment that: +"the time recorded in this view is supposed to be in hundredths of a second."+

    You have 64 CPUs, 5 minutes, 60 seconds to a minute, and time ticks at 100 to the second, so:
    ((change in busy time) + (change in idle time)) should match (64 * 5 * 60 * 100)

    Regards
    Jonathan Lewis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points