Oracle Scratchpad

AWR OS Stats

A simple script to show a trend through the AWR OS Stats.

rem
rem     Script:       trend_awr_osstat.sql
rem     Author:       Jonathan Lewis
rem     Dated:        Sep 2006
rem     Purpose:
rem
rem     Last tested
rem             10.2.0.3
rem     Not Tested
rem             11.2.0.1
rem             11.1.0.7
rem     Not relevant
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Warning:
rem     Requires licence for Diagnostic Pack.
rem
rem     Notes:
rem     Trendline through AWR OS stats
rem
rem     Accesses data for current instance and DBID
rem
rem     Hard coded to go 30 days back in history
rem     Ignores database restarts
rem
rem     Reports a timestamp at the start of an interval
rem     with the activity that happened in that interval
rem
rem     The join on snap_id + 1 is valid for AWR because
rem     of the way AWR generates snapshot ids. (This is
rem     not safe for statspack unless you set the sequence
rem     to nocache).
rem
rem     Change the following define to pick a different statistic
rem     The available OS stats are platform dependent
rem

define m_stat_name = 'BUSY_TIME'

set timing off
set linesize 132
set pagesize 60
set trimspool on

column  instance_number   new_value m_instance  noprint
column  dbid              new_value m_dbid      noprint

select
        ins.instance_number,
        db.dbid
from
        v$instance        ins,
        v$database        db
;

column        value      format        999,999,999,999
column        curr_value format        999,999,999,999
column        prev_value format        999,999,999,999

spool trend_awr_osstat

with base_line as (
        select
                /*+ materialize */
                snp.snap_id,
                to_char(snp.end_interval_time,'Mon-dd hh24:mi:ss')     end_time,
                ost.value
        from
                dba_hist_snapshot       snp,
                dba_hist_osstat         ost
        where
                snp.dbid            = &m_dbid
        and     snp.instance_number = &m_instance
        and     end_interval_time   between sysdate - 30 and sysdate
        /*                                                        */
        and     ost.dbid            = snp.dbid
        and     ost.instance_number = snp.instance_number
        and     ost.snap_id         = snp.snap_id
        and     ost.stat_name       = '&m_stat_name'
        /*                                                        */
)
select
        b1.end_time           start_of_delta,
        b1.value              prev_value,
        b2.value              curr_value,
        b2.value - b1.value   value
from
        base_line        b1,
        base_line        b2
where
        b2.snap_id = b1.snap_id + 1
order by
        b1.snap_id
;

spool off

Update Feb 2010: Remember my warnings about testing any SQL you get from the Internet. This code has behaved reasonably well on most sites I’ve visited – but I was at a site recently that had set their retention time to 18 months, and performance was dire. Workaround: in the subquery, change the simple /*+ materialize */ hint to: /*+ materialize leading(snp ost) no_merge(snp) push_pred(ost) */. Ideally, of course, I’d write something that accessed the wrh$ table directly (in fact, I probably have done so a few times already).

2 Comments »

  1. Dear Jonathan
    what could be the to calculate the average time ,cpu_count is considered but now num_cpu_cores which is correct

    Also confirm if cpu time reported is over all time or time used by database ?

    SQL> select distinct value from     v$osstat where stat_name='NUM_CPU_CORES';
    
         VALUE
    ----------
             2
    
    1 row selected.
    
    SQL>
    SQL> SHOW PARAMETER CPU
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cpu_count                            integer     4
    parallel_threads_per_cpu             integer     2
    resource_manager_cpu_allocation      integer     4
    SQL>
    SQL> select snap_id,max(busy) secs_all_cpu,max(avg_busy) avg_secs_percpu from
      2  (
      3  select snap_id,
      4  (case when stat_name = 'BUSY_TIME' then value/100 else null end) busy,
      5  (case when stat_name = 'AVG_BUSY_TIME' then value/100 else null end) avg_busy
      6  from dba_hist_osstat where snap_id> 3)
      7  where busy is not null or avg_busy is not null
      8  group by snap_id
      9  order by 2
     10  /
    
       SNAP_ID SECS_ALL_CPU AVG_SECS_PERCPU
    ---------- ------------ ---------------
             4       912.15          227.56
             5      2924.04          729.83
             6       4526.2         1129.49
             7       5519.8         1376.92
             8      8303.98          2072.1
             9     11594.98         2893.92
            10        16097         4018.53
    
    7 rows selected.
    
    SQL>
    

    Comment by kg — October 5, 2012 @ 11:50 am BST Oct 5,2012 | Reply

    • CPU counts can get very confusing – and you need to think about: Sockets (number of chips), Cores (physical CPUs on chips), and “Logical CPUs”.
      When checking the cpu_count, Oracle trusts the operating system – and the operating system usually tells Oracle about logical CPUs.

      The stats you are reporting are figures from the view of the operating system, so it would be possible to see a BUSY_TIME that was much larger than then CPU time reported by Oracle in (say) the Top Timed Events. Depending on your platform you might see the same OSSTATS reporting sockets, cores and CPUs – although the titles may vary a little; the average busy time is total busy time divided by the number of CPUs (Personally, though, for CPU-intensive workloads I tend to worry more about CPU time / number of cores).

      Comment by Jonathan Lewis — December 13, 2012 @ 1:35 pm BST Dec 13,2012 | Reply


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,505 other followers