View Oracle sessions by CPU usage and status

As a database guy one of the most common questions I get asked is to display a list of Oracle processes sorted by CPU usage. This might not be entirely accurate but proves to be a bit useful when you do not have visibility of the operating system such as Amazon Oracle RDS. So whether looking which sessions consume the most CPU, or to get a list of all Oracle processes sorted by CPU usage, displaying machine they are coming from, their waiting time and whether they are active or not, run the following query:

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage", seconds_in_wait, machine, status
FROM v$session se, v$sesstat ss, v$statname st 
WHERE ss.statistic# = st.statistic# 
   AND name LIKE  '%CPU used by this session%' 
   AND se.sid = ss.SID  
   AND se.username IS NOT NULL 
  ORDER BY value DESC; 

And the result would be something like this:

Username SID  CPU Wait(s) Machine      Status
james    2841 81  937     AMAZONA-ABC2 INACTIVE
james    1436 68  78      machine1     INACTIVE
james    49   61  100     machine2     INACTIVE
james    47   45  3297    machine1     INACTIVE
james    48   36  4669    machine3     INACTIVE

Now you would ask me what sort of CPU gives you more than 100% total CPU Usage? A multicore one of course..

No comments: