Re: Time Series Format of Operating System Statistics

I already got what I need.. see the following query:

select
s1t0.snap_id,
to_char(s0.BEGIN_INTERVAL_TIME,'YYYY-Mon-DD HH24:MI:SS') time,

s1t1.value - s1t0.value as busy_time,
s2t1.value as load,
s3t1.value as num_cpus,
s4t1.value as physical_memory_bytes
     from dba_hist_snapshot s0,
          dba_hist_snapshot s1,
          dba_hist_osstat s1t0,
          dba_hist_osstat s1t1,
          dba_hist_osstat s2t1,
          dba_hist_osstat s3t1,
          dba_hist_osstat s4t1
     where
       s0.dbid = 2607950532
       and s1t0.dbid = s0.dbid
       and s1t1.dbid = s0.dbid
       and s2t1.dbid = s0.dbid
       and s3t1.dbid = s0.dbid
       and s4t1.dbid = s0.dbid
       and s0.instance_number = 1
       and s1t0.instance_number = s0.instance_number
       and s1t1.instance_number = s0.instance_number
       and s2t1.instance_number = s0.instance_number
       and s3t1.instance_number = s0.instance_number
       and s4t1.instance_number = s0.instance_number
       and s1.snap_id = s0.snap_id + 1
       and s1t0.snap_id = s0.snap_id
       and s1t1.snap_id  = s0.snap_id + 1
       and s2t1.snap_id  = s0.snap_id
       and s3t1.snap_id  = s0.snap_id
       and s4t1.snap_id  = s0.snap_id
       and s1t0.stat_name = 'BUSY_TIME'
       and s1t1.stat_name = s1t0.stat_name
       and s2t1.stat_name = 'LOAD'
       and s3t1.stat_name = 'NUM_CPUS'
       and s4t1.stat_name = 'PHYSICAL_MEMORY_BYTES'
     order by snap_id asc

and the output ...

   SNAP_ID TIME BUSY_TIME LOAD NUM_CPUS PHYSICAL_MEMORY_BYTES

---------- -------------------- ---------- ---------- ----------
---------------------
       245 2010-Jan-14 12:38:36       1603 .239257813       1 169520
       246 2010-Jan-14 13:03:23      28415 .049804688       1 154464
       247 2010-Jan-14 13:10:38       8993 .059570313       1  5148
       248 2010-Jan-14 14:00:39     -46770    0       1 29292
       249 2010-Jan-15 23:45:26      17722 .049804688       1 311216
       250 2010-Jan-15 23:56:37       7089 .659179688       1 109880

and compared to the original query and output ... plus compare it to the generated AWR report...

select

e.snap_id, substr(e.stat_name, 1, 35) as name,
       (case when e.stat_name like 'NUM_CPU%'           then e.value
             when e.stat_name = 'LOAD'                 then e.value
             when e.stat_name = 'PHYSICAL_MEMORY_BYTES' then e.value
             else e.value - b.value
        end) as value
     from dba_hist_osstat b,
          dba_hist_osstat e
     where
      b.stat_name = 'LOAD' and
       b.dbid = 2607950532
       and e.dbid = 2607950532
       and b.instance_number = 1
       and e.instance_number = 1
       and e.snap_id = b.snap_id + 1
       and b.stat_id = e.stat_id
     order by snap_id, name asc

   SNAP_ID NAME     VALUE
---------- ----------------------------------- ----------
       245 LOAD        .239257813
       246 LOAD        .049804688
       247 LOAD        .059570313
       248 LOAD 0
       249 LOAD        .049804688
       250 LOAD        .659179688

Yeah! I got it right ;)

• Karl Arao karlarao.wordpress.com

--
http://www.freelists.org/webpage/oracle-l