7 Replies Latest reply: Mar 12, 2011 1:31 AM by user3206995 RSS

    Performance View V$OSSTAT

    user3206995
      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
          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
            Charles Hooper
            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
              Thanks Charles.
              • 4. Re: Performance View V$OSSTAT
                Jonathan Lewis
                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
                  Charles Hooper
                  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
                    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
                      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