CPU Used By This Instance

CPU Used By The Instance

(Tested on an Oracle 10 database.)

The "CPU used by this session" statistic in V$SYSSTAT shows how much CPU time the database has used since it was started. It is recorded in hundredths of a second. First set timed_statistics to false. This stops Oracle incrementing the statistic:

SQL> alter system set timed_statistics = false

  2  /

System altered.

SQL> col value format a10

SQL> l

  1  select value from v$parameter

  2* where name = 'timed_statistics'

SQL> /

VALUE

----------

FALSE

SQL>

Check the starting value:

SQL> l

  1  select value/100 SECONDS from v$sysstat

  2* where name = 'CPU used by this session'

SQL> /

   SECONDS

----------

  14756.13

SQL>

Do some work:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     44734

SQL>

Check the statistic again. It will not have changed:

SQL> select value/100 SECONDS from v$sysstat

  2  where name = 'CPU used by this session';

   SECONDS

----------

  14756.13

SQL>

Now set timed_statistics to true:

  1* alter system set timed_statistics = true

SQL> /

System altered.

SQL>

Check the starting value again. It has already gone up a little:

  1  select value/100 SECONDS from v$sysstat

  2* where name = 'CPU used by this session'

SQL> /

   SECONDS

----------

  14756.35

SQL>

Do some work again:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     44734

SQL>

Check the statistic again. This time it will have increased:

SQL> select value/100 SECONDS from v$sysstat

  2  where name = 'CPU used by this session';

   SECONDS

----------

  14756.96

SQL>