Friday, May 18, 2012

V$MYSTAT

Tested on Oracle 11.2. This view stores information for the current session:
 
SQL> desc v$mystat
Name                       Null?    Type
-------------------------- -------- ------------------
SID                                 NUMBER
STATISTIC#                          NUMBER
VALUE                               NUMBER
 
SQL>
 
Although there is a column called SID in the view, it only has 1 value, the SID for the current session:
 
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       394
 
SQL>
 
If you join it with v$statname, you can pick out individual statistics like this:
 
SQL> select value/100 CPU_used
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and name = 'CPU used by this session'
  5  /
 
  CPU_USED
----------
       .03
 
SQL>
 
An ideal way to use lots of CPU time is to have a join without a join condition:
 
SQL> set timing on
SQL> select count(*)
  2  from dba_tables a, dba_tables b
  3  /
 
  COUNT(*)
----------
   9375844
 
Elapsed: 00:03:29.23
SQL> set timing off
 
Now that the SQL has finished, you can see that the elapsed time was 209 seconds and the CPU time used was 144 seconds:
 
SQL> select value/100 CPU_used
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and name = 'CPU used by this session'
  5  /
 
  CPU_USED
----------
    144.23
 
SQL>

No comments: