This discussion is archived
4 Replies Latest reply: Nov 21, 2013 8:56 AM by 997554 RSS

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

997554 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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
    ozgurumut Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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