Re: AWR report generator for past periods (with graphs)?
Date: Fri, 7 Sep 2012 00:05:04 -0700
Message-ID: <CADsdiQgNwo45YeUHA2c6LSuWYbGupNFyjDc4CMPWU+nB=rms3A_at_mail.gmail.com>
Using DBA_HIST_SYSTEM_EVENT along with CPU from DBA_HIST_SYSSTAT should be fine. I would recommend dba_hist_active_sess_history as the primary source for database load for a few reasons though
- clearer understanding of cpu demands
- richer source of data
If there is an issue the ASH data is multidimensional and allows asking and getting answers to many different questions from the same data source. Events only point out the problem. ASH allows one to find the solutions.
ASH data will show CPU demand whether CPU actually get's used or not. This is important for identifying CPU starvation. Here is an example of using ASH along with system stats to determine what is wait for CPU and what is CPU burned:
http://dboptimizer.com/2011/07/21/oracle-cpu-time/
In the past the CPU counters from v$sysstat, the basis for most of DBA_HIST_SYSTAT, had problems where CPU wasn't reported correctly. For example a hour long PL/SQL block would report 0 CPU until it finished and then in one lump sum report all it's CPU. It was an issue in 9 and some versions of 10. This *should* be fixed by the time model CPU and in v$systat but I haven't taken the time to figure which versions exactly because I've switch over to ASH for the majority of data queries.
The big question that DBA_HIST_SYSTEM_EVENT and v$system_event can answer is "what is the average I/O latency for various I/O waits". This isn't in ASH. ASH does report wait times but these will be skewed heavily towards the longer waits. JB and Uri presented paper recently on how to extact average I/O latency from ASH but upon reading the PPT I see the idea, but didn't see the exact formula to harnessing it.
Here is an example of comparing ASH to v$system_event and v$sysstat data where the ASH data is more "real time" but all in all the data is pretty close. ASH data is more jittery as it's on 15 second average intervals where as the wait data is on 1 minute.
http://farm9.staticflickr.com/8451/7948002836_493034a4e2_z.jpg[image: Inline image 1]
- Kyle
On Thu, Sep 6, 2012 at 10:48 PM, Petr Novak <Petr.Novak_at_trivadis.com> wrote:
> Hallo Cheng
>
> I would not use dba_hist_active_sess_history for load overview.
> Even if you would take wait time into account.
> ASH make samples , which are biased, because short time waits
> (latches,cache buffer chains) are not so often catched as longer waits
> (locks).
> I would suggest query based on DBA_HIST_SYSTEM_EVENT (Waits) and
> DBA_HIST_SYSSTAT (for CPU - stat_name='CPU used by this session').
>
> Best Regards,
> Petr
>
> ________________________________________
> Von: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org]"
> im Auftrag von "Ls Cheng [exriscer_at_gmail.com]
> Gesendet: Donnerstag, 6. September 2012 23:35
> An: Christopher.Taylor2_at_parallon.net
> Cc: oracle-l_at_freelists.org
> Betreff: Re: AWR report generator for past periods (with graphs)?
>
> I use this sql
>
> select TO_CHAR(TRUNC(sample_time, 'hh'), 'mm-dd hh24') sample_timestamp,
> state,
> count(*)/360 avg_active_sess
> from
> (select sample_time, sample_id,
> CASE
> WHEN session_state = 'ON CPU' THEN 'CPU'
> WHEN session_state = 'WAITING' THEN wait_class
> ELSE 'Misc Waits'
> END state
> from dba_hist_active_sess_history
> where session_type IN ('FOREGROUND')
> and sample_time > TO_DATE (:start_time, 'yyyymmdd hh24mi')
> and sample_time < TO_DATE (:end_time, 'yyyymmdd hh24mi')
> )
> group by TO_CHAR(TRUNC(sample_time, 'hh'), 'mm-dd hh24'), state
> order by TO_CHAR(TRUNC(sample_time, 'hh'), 'mm-dd hh24'), state;
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 07 2012 - 02:05:04 CDT