FAQ
Hello, list.

At Statspack age, 'CPU Time' value in section 'Top 5 Timed Events' was
calculated as the difference of 'CPU used by this session' values
between two snapshots:

select 'CPU', (e.value-b.value)/100 time
from stats$sysstat b, stats$sysstat e
where e.snap_id = :v_eid
and b.snap_id = :v_bid
and e.name = 'CPU used by this session'
and b.name = 'CPU used by this session';

Applying the same theory against DBA_HIST_SYSSTAT table (10gR2), I
can't get the same results of CPU Time in AWR reports:

select 'CPU', (e.value-b.value)/100 time

from dba_hist_sysstat b, dba_hist_sysstat e
where e.snap_id = :v_eid
and b.snap_id = :v_bid
and e.stat_name = 'CPU used by this session'
and b.stat_name = 'CPU used by this session';

From my first observations, CPU Time in AWR Reports is something like
75% of the value gotten with the above select. Probably it's not
relevant.

Do you know if CPU Time calculation formula has changed from Statspack
to AWR and how it has changed? Or am I missing something?

I tried to get the formula from DBMS_WORKLOAD_REPOSITORY source, but
guess what? Yes, it's wrapped. :)

Thanks in advance for any input.

Cerri

Search Discussions

  • Tanel Poder at Nov 1, 2007 at 5:10 pm
    Well you can trace the awrrpt run...

    I suspect (but don't know for sure) that AWR uses DBA_HIST_SYS_TIME_MODEL
    for its CPU info... V$SYS_TIME_MODEL CPU values can differ from V$SYSSTAT
    ones...

    SQL> select

    2 a.value/1000000 time_model_seconds,
    3 b.value/10000 sysstat_seconds,
    4 a.value/1000000 - b.value/10000 missing_seconds
    5 from
    6 v$sys_time_model a,
    7 v$sysstat b
    8 where
    9 a.stat_name = 'DB CPU'

    10 and b.name = 'CPU used by this session'
    11 /

    TIME_MODEL_SECONDS SYSSTAT_SECONDS MISSING_SECONDS

    ------------------ --------------- ---------------
    126.997282 4.6276 122.369682

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Luis
    Fernando Cerri
    Sent: Thursday, November 01, 2007 23:20
    To: oracle-l_at_freelists.org
    Subject: CPU Time calculation in AWR Reports

    Hello, list.

    At Statspack age, 'CPU Time' value in section 'Top 5 Timed
    Events' was calculated as the difference of 'CPU used by this
    session' values between two snapshots:

    select 'CPU', (e.value-b.value)/100 time
    from stats$sysstat b, stats$sysstat e
    where e.snap_id = :v_eid
    and b.snap_id = :v_bid
    and e.name = 'CPU used
    by this session'
    and b.name = 'CPU used
    by this session';
    --
    http://www.freelists.org/webpage/oracle-l
  • Luis Fernando Cerri at Nov 1, 2007 at 5:25 pm
    Thanks for your valuable and precise suggestion, Tanel.

    In a nutshell, this is what AWR does:

    select 'CPU', (e.value-b.value)/1000000 time

    from dba_hist_sys_time_model b, dba_hist_sys_time_model e
    where e.snap_id = :v_eid
    and b.snap_id = :v_bid
    and e.stat_name = 'DB CPU'
    and b.stat_name = 'DB CPU';

    Best regards,
    Cerri

    2007/11/1, Tanel Poder :
    Well you can trace the awrrpt run...

    I suspect (but don't know for sure) that AWR uses DBA_HIST_SYS_TIME_MODEL
    for its CPU info... V$SYS_TIME_MODEL CPU values can differ from V$SYSSTAT
    ones...

    SQL> select
    2 a.value/1000000 time_model_seconds,
    3 b.value/10000 sysstat_seconds,
    4 a.value/1000000 - b.value/10000 missing_seconds
    5 from
    6 v$sys_time_model a,
    7 v$sysstat b
    8 where
    9 a.stat_name = 'DB CPU'
    10 and b.name = 'CPU used by this session'
    11 /

    TIME_MODEL_SECONDS SYSSTAT_SECONDS MISSING_SECONDS
    ------------------ --------------- ---------------
    126.997282 4.6276 122.369682


    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Luis
    Fernando Cerri
    Sent: Thursday, November 01, 2007 23:20
    To: oracle-l_at_freelists.org
    Subject: CPU Time calculation in AWR Reports

    Hello, list.

    At Statspack age, 'CPU Time' value in section 'Top 5 Timed
    Events' was calculated as the difference of 'CPU used by this
    session' values between two snapshots:

    select 'CPU', (e.value-b.value)/100 time
    from stats$sysstat b, stats$sysstat e
    where e.snap_id = :v_eid
    and b.snap_id = :v_bid
    and e.name = 'CPU used
    by this session'
    and b.name = 'CPU used
    by this session';
    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at Nov 2, 2007 at 10:47 am
    I think the difference may be because v$sysstat is refreshed after a call is
    completed but in SYS_TIME_MODEL_CPU it gets refreshed every 3 seconds

    Thanks

    --
    LSC
    On Nov 1, 2007 6:10 PM, Tanel Poder wrote:

    Well you can trace the awrrpt run...

    I suspect (but don't know for sure) that AWR uses DBA_HIST_SYS_TIME_MODEL
    for its CPU info... V$SYS_TIME_MODEL CPU values can differ from V$SYSSTAT
    ones...

    SQL> select
    2 a.value/1000000 time_model_seconds,
    3 b.value/10000 sysstat_seconds,
    4 a.value/1000000 - b.value/10000 missing_seconds
    5 from
    6 v$sys_time_model a,
    7 v$sysstat b
    8 where
    9 a.stat_name = 'DB CPU'
    10 and b.name = 'CPU used by this session'
    11 /

    TIME_MODEL_SECONDS SYSSTAT_SECONDS MISSING_SECONDS
    ------------------ --------------- ---------------
    126.997282 4.6276 122.369682


    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Luis
    Fernando Cerri
    Sent: Thursday, November 01, 2007 23:20
    To: oracle-l_at_freelists.org
    Subject: CPU Time calculation in AWR Reports

    Hello, list.

    At Statspack age, 'CPU Time' value in section 'Top 5 Timed
    Events' was calculated as the difference of 'CPU used by this
    session' values between two snapshots:

    select 'CPU', (e.value-b.value)/100 time
    from stats$sysstat b, stats$sysstat e
    where e.snap_id = :v_eid
    and b.snap_id = :v_bid
    and e.name = 'CPU used
    by this session'
    and b.name = 'CPU used
    by this session';
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Vlad Sadilovskiy at Nov 5, 2007 at 1:47 pm
    In this case it should be the same at the end of the day. Shouldn't it be?

    After Jonathan posted his AWR observations, I couldn't reproduce similar
    behavior and don't believe the time is calculated as *CPU used by this
    session + recursive calls cpu usage*. At least not always. Here is a
    post Recursive
    Calls <http://vsadilovskiy.wordpress.com/2007/11/05/recursive-calls/> that
    demonstrates differences in reporting recursive calls CPU usage in various
    tools.

    I agree that the source of *CPU used by this session* value in v$sysstat
    that is *definitely used* to report *CPU time* by Statspack might
    be different from the source of the *DB CPU* from v$sys_time_model that *I
    think* is used by AWR to report the same. The lack of definitive information
    about different DB usage patterns, doesn't allow clear demonstration of
    their correlation.

    Vlad Sadilovskiy
    Oracle Database Tools
    Web site: http://www.fourthelephant.com
    Blog: http://vsadilovskiy.wordpress.com
    On 11/2/07, LS Cheng wrote:

    I think the difference may be because v$sysstat is refreshed after a call
    is completed but in SYS_TIME_MODEL_CPU it gets refreshed every 3 seconds


    Thanks

    --
    LSC
    On Nov 1, 2007 6:10 PM, Tanel Poder < tanel.poder.003_at_mail.ee> wrote:

    Well you can trace the awrrpt run...

    I suspect (but don't know for sure) that AWR uses
    DBA_HIST_SYS_TIME_MODEL
    for its CPU info... V$SYS_TIME_MODEL CPU values can differ from
    V$SYSSTAT
    ones...

    SQL> select
    2 a.value/1000000 time_model_seconds,
    3 b.value/10000 sysstat_seconds,
    4 a.value/1000000 - b.value/10000 missing_seconds
    5 from
    6 v$sys_time_model a,
    7 v$sysstat b
    8 where
    9 a.stat_name = 'DB CPU'
    10 and b.name = 'CPU used by this session'
    11 /

    TIME_MODEL_SECONDS SYSSTAT_SECONDS MISSING_SECONDS
    ------------------ --------------- ---------------
    126.997282 4.6276 122.369682


    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Luis
    Fernando Cerri
    Sent: Thursday, November 01, 2007 23:20
    To: oracle-l_at_freelists.org
    Subject: CPU Time calculation in AWR Reports

    Hello, list.

    At Statspack age, 'CPU Time' value in section 'Top 5 Timed
    Events' was calculated as the difference of 'CPU used by this
    session' values between two snapshots:

    select 'CPU', (e.value-b.value)/100 time
    from stats$sysstat b, stats$sysstat e
    where e.snap_id = :v_eid
    and b.snap_id = :v_bid
    and e.name = 'CPU used
    by this session'
    and b.name = 'CPU used
    by this session';

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


    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Nov 1, 2007 at 6:32 pm
    There's a bug in AWR - it seems to calculate
    CPU time as

    CPU used by this session +
    recursive cpu usage

    Worst case, if all you ever do is call pl/sql to wrap
    SQL, is that the CPU time is double-counted.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
  • Jonathan Lewis at Nov 2, 2007 at 9:54 am
    I think I'm about to have demonstrated to me (offline)
    that the comment below is complete garbage. So my
    apologies and please ignore it.

    It's a bit sad in some ways because I'd already blogged about it:
    (http://jonathanlewis.wordpress.com/2007/11/01/awr-oddity/) -
    but I'll be recanting on he blog as well.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Original Message -----
    From: "Jonathan Lewis"
    Subject: RE: CPU Time calculation in AWR Reports
    Date: Thu, 1 Nov 2007 18:32:05 -0000

    There's a bug in AWR - it seems to calculate
    CPU time as

    CPU used by this session +
    recursive cpu usage

    Worst case, if all you ever do is call pl/sql to wrap
    SQL, is that the CPU time is double-counted.


    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 1, '07 at 3:20p
activeNov 5, '07 at 1:47p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase