Wednesday, March 16, 2011

CPU Used By This Session



Tested on an Oracle 10 database.

This statistic is in V$SESSTAT and can be checked as follows:

(1) Get the number of the statistic from V$SYSSTAT:

SQL> col name format a40
SQL> l
  1  select name, statistic# from v$sysstat
  2* where upper(name) like '%CPU%'
SQL> /

NAME                                     STATISTIC#
---------------------------------------- ----------
recursive cpu usage                               8
CPU used when call started                       11
CPU used by this session                         12
IPC CPU used by this session                     43
global enqueue CPU used by this session          46
gc CPU used by this session                     163
parse time cpu                                  336
OS User level CPU time                          355
OS System call CPU time                         356
OS Other system trap CPU time                   357
OS Wait-cpu (latency) time                      363

11 rows selected.

SQL>

The statistic we want is 12.

(2) Determine the SID you want to look at using the information at your disposal e.g:

  1  select sid from v$session
  2* where username = 'ORACLE'
SQL> /

       SID
----------
        90

SQL>

(3) Use the results from (1) and (2) above to interrogate V$SESSTAT. The value is in hundredths of a second:

SQL> l
  1  select value/100 SECONDS from v$sesstat
  2  where sid = 90
  3* and statistic# = 12
SQL> /

   SECONDS
----------
   1497.63

SQL>

(4) If the session is active, if you wait a while and run the query again, the value will have increased:

SQL> l
  1  select value/100 SECONDS from v$sesstat
  2  where sid = 90
  3* and statistic# = 12
SQL> /

   SECONDS
----------
    1510.9

SQL>

No comments: