4 Replies Latest reply on Nov 21, 2013 4:56 PM by 997554

    How to calculate % of Busy CPU used for Instance in statspack report

    997554

      Hi ,

       

      In the statspack report we know how to calculate %of time Host is busy but don't know how to calculate %Busy CPU used for instance.

       

      Instance CPU

      ~~~~~~~~~~~~                                       % Time (seconds)

                                                  -------- --------------

                           Host: Total time (s):                 28,835.3

                        Host: Busy CPU time (s):                  7,147.6

                         % of time Host is Busy:      24.8

                   Instance: Total CPU time (s):                  5,234.5

                % of Busy CPU used for Instance:      73.2

              Instance: Total Database time (s):                  9,495.5

        %DB time waiting for CPU (Resource Mgr):       0.0

       

      In above , 28,835.3 comes from stats$osstat and to get  24.8% oracle use this seconds but Where can i get  5,234.5 seconds and how to calculate 73.2.

       

      We have multiple databases on the same server and it's very import to know instance wise cpu utilization.

       

      Regards,

      chirag

        • 1. Re: How to calculate % of Busy CPU used for Instance in statspack report
          Aliyev Chinar

          997554 wrote:

           

          Hi ,

           

          In the statspack report we know how to calculate %of time Host is busy but don't know how to calculate %Busy CPU used for instance.

           

          Instance CPU

          ~~~~~~~~~~~~                                       % Time (seconds)

                                                      -------- --------------

                               Host: Total time (s):                 28,835.3

                            Host: Busy CPU time (s):                  7,147.6

                             % of time Host is Busy:      24.8

                       Instance: Total CPU time (s):                  5,234.5

                    % of Busy CPU used for Instance:      73.2

                  Instance: Total Database time (s):                  9,495.5

            %DB time waiting for CPU (Resource Mgr):       0.0

           

          In above , 28,835.3 comes from stats$osstat and to get  24.8% oracle use this seconds but Where can i get  5,234.5 seconds and how to calculate 73.2.

           

          We have multiple databases on the same server and it's very import to know instance wise cpu utilization.

           

          Regards,

          chirag

           

          Total CPU time (s) is a service time which all server processes actually worked(without waiting like I/O or others) within specified time period. You can see that using time model statistics(DB CPU statname) or  this is sum value of "CPU used by this session"  static of all sessions.

          Also here 73.2 is equal 100*(Total CPU time (s)/Busy CPU time (s)) =100(5,234.5/7,147.6)

          • 2. Re: How to calculate % of Busy CPU used for Instance in statspack report
            Ozgur Umut Vurgun

            Hi ,

             

            I agreed to AliyevChinar reply  and  I'd like to add little query about CPU Usage,

             

            with AASSTAT as (

              select

              decode(n.wait_class,'User I/O','User I/O',

              'Commit','Commit',

              'Wait') CLASS,

              sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS,

              BEGIN_TIME ,

              END_TIME

              from v$waitclassmetric m,

              v$system_wait_class n

              where m.wait_class_id=n.wait_class_id

              and n.wait_class != 'Idle'

              group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME

              union

              select 'CPU_ORA_CONSUMED' CLASS,

              round(value/100,3) AAS,

              BEGIN_TIME ,

              END_TIME

              from v$sysmetric

              where metric_name='CPU Usage Per Sec'

              and group_id=2

              union

              select 'CPU_OS' CLASS ,

              round((prcnt.busy*parameter.cpu_count)/100,3) AAS,

              BEGIN_TIME ,

              END_TIME

              from

              ( select value busy, BEGIN_TIME,END_TIME from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,

              ( select value cpu_count from v$parameter where name='cpu_count' ) parameter

              union

              select

              'CPU_ORA_DEMAND' CLASS,

              nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,

              cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,

              cast(max(SAMPLE_TIME) as date) END_TIME

              from v$active_session_history ash

              where SAMPLE_TIME >= (select BEGIN_TIME from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )

              and SAMPLE_TIME < (select END_TIME from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )

            )

            select

              to_char(BEGIN_TIME,'HH:MI:SS') BEGIN_TIME,

              to_char(END_TIME,'HH:MI:SS') END_TIME,

              ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +

              CPU_ORA_CONSUMED +

              decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,

              decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,

              CPU_ORA_CONSUMED CPU_ORA,

              decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,

              COMMIT,

              READIO,

              WAIT

            from (

            select

              min(BEGIN_TIME) BEGIN_TIME,

              max(END_TIME) END_TIME,

              sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,

              sum(decode(CLASS,'CPU_ORA_DEMAND' ,AAS,0)) CPU_ORA_DEMAND,

              sum(decode(CLASS,'CPU_OS' ,AAS,0)) CPU_OS,

              sum(decode(CLASS,'Commit' ,AAS,0)) COMMIT,

              sum(decode(CLASS,'User I/O' ,AAS,0)) READIO,

              sum(decode(CLASS,'Wait' ,AAS,0)) WAIT

            from AASSTAT)

            /

             

            you can check  https://github.com/khailey/ashmasters/blob/master/cpu_consumed_verses_cpuwait.sql and http://www.kylehailey.com/oracle-cpu-time/

            • 3. Re: How to calculate % of Busy CPU used for Instance in statspack report
              997554

              Hi AliyevChinar,

               

               

              Which statspack table give me 5,234.5 value .I checked DB CPU in stats$sys_time_model but it didn't match with this value. May be i don't know i to calculate it ? Could you please help me to get that value ?

               

              Regards,

              Chirag

              • 4. Re: How to calculate % of Busy CPU used for Instance in statspack report
                997554

                Hi Aliyev,

                 

                We can get the % of Busy CPU used for Instance by adding DB CPU and background cpu time from stats$sys_time_model and divide that by busy_time from stats$ostat.

                 

                so % of total CPU for Instance: ((:dbcpu+:bgcpu)/1000000)/(:ttics).

                 

                Refer the Interpret INSTANCE CPU of statspack report (Doc ID 405073.1).

                 

                Still i don't know how to get Instance: Total CPU time (s): 5,234.5

                 

                Regards,

                Chirag