FAQ
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

Search Discussions

  • Martin Berger at Nov 9, 2011 at 6:08 am
    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 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
  • Laimutis Nedzinskas at Nov 9, 2011 at 6:22 am
    AWR samples (by default, which you can change, if you wanna to, etc, etc,
    etc) at 10 seconds.

    Hence this is :

    count(*) * 10/3600 - hours spent waiting for

    brgds, Laimis N



    ---------------------------------------------------------------------------------

    Please consider the environment before printing this e-mail



    From: Martin Berger <martin.a.berger@gmail.com>

    To: exriscer@gmail.com

    Cc: Oracle Mailinglist <oracle-l@freelists.org>

    Date: 2011.11.09 08:08

    Subject: Re: sql developer daily ash statistics chart query






    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 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
  • Laimutis Nedzinskas at Nov 9, 2011 at 11:41 am
    Actually, I remember Graham Wood's (the man behind ASH) presentation where
    he used integral symbol. ASH snapshots are used to aproximately calculate
    the integral which represents the real workload over time interval. dx
    (better dt) in this case is 10 seconds :)
    Once you know the theory it's quite easy to remember things :)



    ---------------------------------------------------------------------------------

    Please consider the environment before printing this e-mail
  • LS Cheng at Nov 9, 2011 at 7:13 am
    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 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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 9, '11 at 12:02a
activeNov 9, '11 at 11:41a
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase