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>