Wednesday, March 16, 2011

CPU Used By This 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>

No comments: