Thursday, September 06, 2012

DB time from V$SESSTAT

This was tested on Oracle 11.2. The DB time entry in V$MYSTAT (or V$SESSTAT) just seems to record the amount of time a session spends doing something in the database, irrespective of the amount of CPU time used. First reconnect to the database to zeroise the figures:

SQL> conn /
Connected.
SQL> select name, m.value/100
  2  from v$mystat m, v$sysstat s
  3  where m.statistic# = s.statistic#
  4  and name in
  5  ('DB time',
  6   'CPU used by this session')
  7  /

NAME                                M.VALUE/100
----------------------------------- -----------
CPU used by this session                    .01
DB time                                       0

SQL>

If you sleep in the OS for a few seconds:

SQL> host sleep 5

... this does not increment DB time or CPU time by much:

SQL> select name, m.value/100
  2  from v$mystat m, v$sysstat s
  3  where m.statistic# = s.statistic#
  4  and name in
  5  ('DB time',
  6   'CPU used by this session')
  7  /

NAME                                M.VALUE/100
----------------------------------- -----------
CPU used by this session                    .02
DB time                                     .02

SQL>

However, if you sleep in the database:

SQL> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL>

... CPU time hardly changes but DB time goes up by the full 5 seconds:

SQL> select name, m.value/100
  2  from v$mystat m, v$sysstat s
  3  where m.statistic# = s.statistic#
  4  and name in
  5  ('DB time',
  6   'CPU used by this session')
  7  /

NAME                                M.VALUE/100
----------------------------------- -----------
CPU used by this session                    .04
DB time                                    5.05

SQL>

No comments: