CPU Time calculation in AWR Reports

  • From: "Luis Fernando Cerri" <lfcerri@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 1 Nov 2007 12:20:10 -0300

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

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.


Other related posts: