Re: sql developer daily ash statistics chart query

From: LS Cheng <exriscer_at_gmail.com>
Date: Wed, 9 Nov 2011 08:12:17 +0100
Message-ID: <CAJ2-Qb9MApYf11rUcZHxtB0RU7vhS2V8ZG8kBOjxHmg4ZqP9Og_at_mail.gmail.com>

yes, I forgot that in ASH hist table the interval is increased from 1 to 10 seconds so it divides by 360 instead of 3600 Thanks!

On Wed, Nov 9, 2011 at 7:06 AM, Martin Berger <martin.a.berger_at_gmail.com>wrote:

> I'd say it is " 1/10 of an hour"
> => 360 sec -> 6 min
>
> I've not checked the Report right now - is it dividing an hour into 10
> parts?
>
> that's my guess,
> Martin
>
> On Wed, Nov 9, 2011 at 01:01, LS Cheng <exriscer_at_gmail.com> wrote:
> > Hi
> > In SQL Developer there are few AWR/ASH report available, one of them is
> > daily ash statistics chart and made up by following query
> >
> > select to_char(trunc((sample_time),'HH'),'HH24:MI'), state, count(*)/360
> > from
> > (select sample_time, sample_id
> > , CASE WHEN session_state = 'ON CPU' THEN 'CPU'
> > WHEN session_state = 'WAITING' AND wait_class IN ('User I/O')
> > THEN 'IO'
> > WHEN session_state = 'WAITING' AND wait_class IN ('Cluster')
> > THEN 'CLUSTER'
> > ELSE 'WAIT' END state
> > from DBA_HIST_ACTIVE_SESS_HISTORY
> > where session_type IN ( 'FOREGROUND')
> > and sample_time between trunc(sysdate,'HH') - 25/24 and
> > trunc(sysdate,'HH') - 1/24 )
> > group by trunc((sample_time),'HH'), state order by
> trunc((sample_time),'HH')
> >
> > Does anyone know why we divide count(*) by 360?
> >
> > Thanks
>
>
> http://www.freelists.org/webpage/oracle-l
>

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

Received on Wed Nov 09 2011 - 01:12:17 CST