Friday, July 15, 2011

Answer to Q3 from Vijay



To find the user who is taking up most CPU time, you need to look in V$SYSSTAT to get the number of the statistic in question e.g.
 
SQL> select statistic# from v$sysstat
  2  where name = 'CPU used by this session';
 
STATISTIC#
----------
        12
 
SQL>
 
The above step is important as the numbers have a habit of changing from one Oracle version to the next. Then you can get the SIDs of the sessions that have used most CPU since they logged in e.g.
 
  1  select * from
  2  (select sid, value/100 from v$sesstat
  3   where statistic# = 12
  4   order by 2 desc)
  5* where rownum <=10
SQL> /
 
       SID  VALUE/100
---------- ----------
        22     379.49
        15     310.84
        13     301.48
        33     287.93
        53     247.32
        79     176.91
        56     166.92
       153     161.46
        96     141.22
        36     136.64
 
10 rows selected.
 
SQL>
 
The values are in hundredths of a second so dividing them by 100 gives the results in seconds. If you want to get figures for users who are currently using most CPU, you will need to repeat the query after a while and just look at the users whose CPU usage has increased in the meantime. Once you have decided which SID you want to investigate you can find out who it is as follows:
 
  1  select sid, username from v$session
  2* where sid in (13, 15, 22)
SQL> /
 
       SID USERNAME
---------- ------------------------------
        13 ANDREW
        15 VIJAY
        22 FRED
 
SQL>

No comments: