Oracle Scratchpad

AWR Correlate

Taking the basic text from trend_awr_stat.sql and trend_awr_osstat.sql (purely as examples of a technique) it is easy to use layers of subquery factoring to compare two sets of results. In this example we can see how CPU used by Oracle sessions compares with the total CPU used by the system.

rem
rem     Script:         trend_cpu_stat.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2006
rem     Purpose:        
rem
rem     Last tested 
rem             10.2.0.3
rem     Not tested
rem     Not relevant
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Warning:
rem     Requires licence for Diagnostic and Performance packs
rem

define m_stat_name_s = 'CPU used by this session'
define m_stat_name_o = 'BUSY_TIME'

set timing off
set linesize 180
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  os_cpu      format  999,999,999,999
column  sys_cpu     format  999,999,999,999
column  delta       format  999,999,999,999

spool trend_cpu_stat

with base_line_s1 as (
        select
                /*+ materialize */
                snp.snap_id,
                to_char(snp.end_interval_time,'Mon-dd hh24:mi:ss')     end_time,
                sst.value
        from
                dba_hist_snapshot       snp,
                dba_hist_sysstat        sst
        where
                snp.dbid            = &m_dbid
        and     snp.instance_number = &m_instance
        and     end_interval_time   between sysdate - 30 and sysdate
        /*                                                        */
        and     sst.dbid            = snp.dbid
        and     sst.instance_number = snp.instance_number
        and     sst.snap_id         = snp.snap_id
        and     sst.stat_name       = '&m_stat_name_s'
        /*                                                        */
),
base_line_s2 as (
        select
                b1.snap_id,
                b1.end_time             start_of_delta,
                b1.value                prev_value,
                b2.value                curr_value,
                b2.value - b1.value     delta
        from
                base_line_s1        b1,
                base_line_s1        b2
        where
                b2.snap_id = b1.snap_id + 1
),
base_line_o1 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_o'
        /*                                                        */
),
base_line_o2 as (
        select
                b1.snap_id,
                b1.end_time           start_of_delta,
                b1.value              prev_value,
                b2.value              curr_value,
                b2.value - b1.value   delta
        from
                base_line_o1        b1,
                base_line_o1        b2
        where
                b2.snap_id = b1.snap_id + 1
)
select
        sys.start_of_delta,
        os.delta                os_cpu,
        sys.delta               sys_cpu,
        os.delta - sys.delta    delta
from
        base_line_s2    sys,
        base_line_o2    os
where
        os.snap_id = sys.snap_id
order by
        sys.snap_id
;

spool off

It took about 20 minutes to hack this script together from the other two scripts. Other variations that spring to mind and assume that you have the default 24 snapshots per day:

  • Use the dba_hist_sysstat twice and make the last join from snap_id to snap_id – 24 … compare with previous day.
  • Use the dba_hist_sysstat twice and make the last join from snap_id to snap_id – 168 … compare with previous week.

Update April 2021

I wrote this note many years ago (shortly after the notes on trending through the sysstat and osstat histories) but had forgotten that I hadn’t yet published it until a question came up on the Oracle-L list server where I wanted to say in one of the later responses: “compare two statistics over time, there’s a sample query at …”

In the recent case the statistics were about transaction table read-consistency: “transaction tables consistent reads – undo records applied” and “transaction tables consistent read rollbacks” – how many undo records were applied to take transaction table entries (the records in the undo segment header block) into the past to find a commit SCN, and how many times did you need to do this. In this case the final output would be a division rather than a subtraction – perhaps using nvl2() to avoid a “divide by zero” error.

1 Comment »

  1. […] trend_awr_os_stat.sql: Report a single statistic across time from the AWR history of v$osstat. trend_cpu_stat.sql: Demonstration of a method of correlating two statistics from AWR […]

    Pingback by Simple scripts | Oracle Scratchpad — March 7, 2022 @ 10:23 am GMT Mar 7,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: