Re: awr history

From: ed lewis <eglewis71_at_gmail.com>
Date: Thu, 13 Jan 2011 05:27:15 -0500
Message-ID: <F40D011AACBB4D3AA802926B86D86F5D_at_CATHY>

Hi Kyle,

    Thanks for your response.

    We do have grid control, which I should have mentioned. It was recently installed so I'm still learning my way around. Some of these reports to be generated will be required for management. So, we'll need to create these reports outside of grid control, I would think. So, are there ways to take advantage of the data stored in GC ? In our case, to generate customized reports with graphs.

    thanks again.

        ed        

• Original Message ----- From: kyle Hailey To: andrew.kerber_at_gmail.com Cc: eglewis71_at_gmail.com ; oracle-l_at_freelists.org Sent: Wednesday, January 12, 2011 9:18 PM Subject: Re: awr history

  Interestingly enough, I just started a thread along similar lines at   http://dboptimizer.com/?p=460
  My questions was "what are the best Oracle performance tools"

  As far as reporting, what are you looking for exactly?

  There is always OEM. OEM doesn't have official reports but the graphs are pretty good for explaining load and bottlenecks.

  Of course there is AWR report and ASH report.

  As far as other tools, there is http://www.spviewer.com which I've never used and does trends for AWR and statspack.   I know Enteros had some tool for viewing AWR data.

  ASHMon , http://ashmasters.com/ash-tools/, will read ASH and display it graphically.   Some ASH queries are on http://ashmasters.com/ash-queries/

• Kyle http://dboptimizer.com

  On Wed, Jan 12, 2011 at 5:25 PM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

    Below is one of my favorites. IO over time. There are many variations you can do on this theme:

    select

       to_char(sn.end_interval_time,'yyyymmddhh24') the_date,
       sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)) reads,
       sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value)) writes,


       (sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)))+

       (sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value))) total

    from
       dba_hist_sysstat oldreads,
       dba_hist_sysstat newreads,
       dba_hist_sysstat oldwrites,
       dba_hist_sysstat newwrites,
       dba_hist_snapshot   sn,
       dba_hist_snapshot   sn2,
       dba_hist_snapshot   sn3
    where sn.instance_number=dbms_utility.current_instance
       and sn.instance_number=sn2.instance_number
       and sn2.instance_number=sn3.instance_number
       and oldreads.instance_number=sn3.instance_number
       and newreads.instance_number=oldreads.instance_number
       and oldreads.instance_number=oldwrites.instance_number
       and oldwrites.instance_number=newwrites.instance_number
       and newreads.snap_id=sn.snap_id
       and newwrites.snap_id=newreads.snap_id
       and sn.instance_number=oldreads.instance_number
       and oldreads.instance_number=newreads.instance_number
       and sn.instance_number=oldwrites.instance_number
       and oldwrites.instance_number=newwrites.instance_number
       and oldreads.snap_id = (select max(sn.snap_id) from dba_hist_snapshot sn where sn.snap_id<newreads.snap_id and
     sn.instance_number=newreads.instance_number and newreads.instance_number=oldreads.instance_number)
       and oldreads.snap_id=sn2.snap_id
       and newreads.snap_id=sn3.snap_id
       and oldwrites.snap_id = (select max(sn.snap_id) from dba_hist_snapshot sn where sn.snap_id<newwrites.snap_id and
     sn.instance_number=newwrites.instance_number and newwrites.instance_number=oldwrites.instance_number)
       and oldreads.stat_name = 'physical reads'
       and newreads.stat_name = 'physical reads'
       and oldwrites.stat_name = 'physical writes'
       and newwrites.stat_name = 'physical writes'

    group by to_char(sn.end_interval_time,'yyyymmddhh24')     order by to_char(sn.end_interval_time,'yyyymmddhh24')     ;

    On Wed, Jan 12, 2011 at 7:21 PM, ed lewis <eglewis71_at_gmail.com> wrote:

      Hello,
          I'm looking for a way to query the AWR, and ASH tables
      to create customized reports. I would also like to present
      some of the results in graphs.
          
          Can anyone recommend any sources for this ?
      Scripts, websites, or 3rd party tools.

          Thanks in advance.

              Ed

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

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

Received on Thu Jan 13 2011 - 04:27:15 CST