Wednesday, May 23, 2012

CPU Usage



I read about this in the book above and tested it on Oracle 11.2. You need to be careful when looking at Oracle statistics on CPU usage. Here is an idle session (in red):

SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
         6
 
SQL>
 
Here is its CPU usage as seen from another session (in blue). The CPU used when call started and CPU used by this session figures are in hundredths of a second. The DB CPU figure is in microseconds:
 
SQL> select
  2  (select value from v$sesstat a, v$statname b
  3   where sid = 6
  4   and a.statistic# = b.statistic#
  5   and name = 'CPU used when call started') as CUWCS,
  6  (select value from v$sesstat a, v$statname b
  7   where sid = 6
  8   and a.statistic# = b.statistic#
  9   and name = 'CPU used by this session') as CUBTS,
10  (select value
11   from v$sess_time_model
12   where sid = 6
13   and stat_name = 'DB CPU') as "DB CPU"
14  from dual
15  /
 
     CUWCS      CUBTS     DB CPU
---------- ---------- ----------
     41669      41669  416810000
 
SQL>
 
The first session runs a query:
 
SQL> set timing on
SQL> select count(*) from dba_tables a, dba_tables b
  2  /
 
While this is going on, the second session monitors it every 30 seconds The  CPU used when call started figure stays the same while the SQL is running. This is reasonable. The CPU used by this session figure goes up a little then stays the same. It is not clear what this is measuring at all. The DB CPU figure appears to be updated regularly:
 
SQL> /
 
     CUWCS      CUBTS     DB CPU
---------- ---------- ----------
     41669      41752  441270000
 
SQL> /
 
     CUWCS      CUBTS     DB CPU
---------- ---------- ----------
     41669      41752  470980000
 
SQL> /
 
     CUWCS      CUBTS     DB CPU
---------- ---------- ----------
     41669      41752  494630000
 
SQL> /
 
     CUWCS      CUBTS     DB CPU
---------- ---------- ----------
     41669      41752  522780000
 
SQL>
 
The first session’s query finishes:
 
SQL> select count(*) from dba_tables a, dba_tables b
  2  /
 
  COUNT(*)
----------
   9369721
 
Elapsed: 00:02:25.93
SQL>
 
Then the figures in the second session match again:
 
SQL> /
 
     CUWCS      CUBTS     DB CPU
---------- ---------- ----------
     54891      54891  549030000
 
SQL>

No comments: