Time Series Format of Operating System Statistics

From: Karl Arao <karlarao_at_gmail.com>
Date: Fri, 29 Jan 2010 03:14:42 +0800
Message-ID: <12ee65601001281114obdaa719w6b94197f5de1a05a_at_mail.gmail.com>

Hi List,

On the AWR report we see the "Operating System Statistics" section.. since generating multiple AWR reports is daunting and takes a lot of time and I'm only interested on particular columns of dba_hist_osstat (where that particular section of AWR pulls the data)... I'd like to have an output like this:

SNAP_ID BUSY_TIME LOAD NUM_CPUS PHYSICAL_MEMORY_BYTES

---

244            6792                .23         1                     169520
245            1603                .04         1                     154464
246            28415              .05         1                     5148

Problem is, the dba_hist_osstat values are stored as rows...

select * from dba_hist_osstat where snap_id = 244;

244 2607950532 1 0 NUM_CPUS 1

244 2607950532 1 1 IDLE_TIME 57153
244 2607950532 1 2 BUSY_TIME 5339
244 2607950532 1 3 USER_TIME 1189

244 2607950532 1 4 SYS_TIME 4077
244 2607950532 1 5 IOWAIT_TIME 2432
244 2607950532 1 6 NICE_TIME 0
244 2607950532 1 14 RSRC_MGR_CPU_WAIT_TIME 0 244 2607950532 1 15 LOAD 0.099609375
244 2607950532 1 1008 PHYSICAL_MEMORY_BYTES 300536 So I got this query to walk through all the SNAP_IDs with the following output. I'd just like to format particular columns like the one I mentioned
(above) for the data to be more meaningful and to be easily loaded on excel
for visualization. Well I can also do this on "Instance Activity Stats"
(dba_hist_sysstat) and other stuff..

select

b.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 = 'BUSY_TIME' 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
-----------------------------------------------------
244            BUSY_TIME  6792
245            BUSY_TIME  1603
246            BUSY_TIME  28415

BTW, try to generate AWR reports on a particular SNAP_IDs, the value you get from the query will be the same on the report...

• Karl Arao karlarao.wordpress.com

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

Received on Thu Jan 28 2010 - 13:14:42 CST