Re: Time Series Format of Operating System Statistics

From: Karl Arao <karlarao_at_gmail.com>
Date: Sat, 30 Jan 2010 23:29:36 +0800
Message-ID: <12ee65601001300729o790a3286yfbed5fb5e130295d_at_mail.gmail.com>

Hi Niall,

I got the final version of the script...

SELECT s0.snap_id,
  TO_CHAR(s0.END_INTERVAL_TIME,'YYYY-Mon-DD HH24:MI:SS') snap_start,   TO_CHAR(s1.END_INTERVAL_TIME,'YYYY-Mon-DD HH24:MI:SS') snap_end,   round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60 +
EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) +
EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2)
ela_min,
  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 + 1
AND s3t1.snap_id         = s0.snap_id + 1
AND s4t1.snap_id         = s0.snap_id + 1
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

   SNAP_ID SNAP_START SNAP_END ELA_MIN BUSY_TIME LOAD NUM_CPUS PHYSICAL_MEMORY_BYTES

---------- -------------------- -------------------- ---------- ----------
---------- ---------- ---------------------
       244 2010-Jan-14 12:38:36 2010-Jan-14 13:03:23  24.78       6792


.239257813       1 169520

       245 2010-Jan-14 13:03:23 2010-Jan-14 13:10:38   7.25       1603


.049804688       1 154464

       246 2010-Jan-14 13:10:38 2010-Jan-14 14:00:39  50.02      28415


.059570313       1  5148

       247 2010-Jan-14 14:00:39 2010-Jan-14 15:00:42  60.04       8993    0
    1 29292
       248 2010-Jan-14 15:00:42 2010-Jan-15 23:56:37 1975.92     -46770


.049804688       1 311216

       249 2010-Jan-15 23:56:37 2010-Jan-16 01:00:40  64.05      17722


.659179688       1 109880

       250 2010-Jan-16 01:00:40 2010-Jan-16 02:00:42  60.03       7089


.229492188       1 43576

       251 2010-Jan-16 02:00:42 2010-Jan-16 10:05:01 484.31     -23928    0
    1 310720
       252 2010-Jan-16 10:05:01 2010-Jan-16 11:01:02  56.03       8906


.099609375       1 186432

From the AWR...

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       244 14-Jan-10 12:38:36        25       1.8
     <-- same from above output (start & end)
  End Snap:       245 14-Jan-10 13:03:23        24       2.0
   Elapsed:               24.78 (mins)
                 <-- same from above output (4th col)
   DB Time:                0.32 (mins)

... output snipped ...

Operating System Statistics                 DB/Inst: IVRS/ivrs  Snaps:

244-245

Statistic                                       Total
-------------------------------- --------------------
BUSY_TIME                                       6,792
       <-- same from above output (5th col)
IDLE_TIME                                     141,649
IOWAIT_TIME                                     4,468
NICE_TIME                                           0
SYS_TIME                                        4,506
USER_TIME                                       2,115
LOAD                                                0
RSRC_MGR_CPU_WAIT_TIME                              0
PHYSICAL_MEMORY_BYTES                         169,520            <-- same
from above output (last col)
NUM_CPUS                                            1

And comming from the original query.. all output is similar above..

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 = '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
---------- ----------------------------------- ----------
       244 LOAD        .239257813
       245 LOAD        .049804688
       246 LOAD        .059570313
       247 LOAD 0
       248 LOAD        .049804688
       249 LOAD        .659179688
       250 LOAD        .229492188
       251 LOAD 0
       252 LOAD        .099609375

Hope this helps...

• Karl Arao karlarao.wordpress.com

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

Received on Sat Jan 30 2010 - 09:29:36 CST