Script to Get the Most CPU Consuming Sessions from Oracle Database

advertisements

_____________________________________________________________________________________________________________________

SQL Script 

This SQL query gives you output of 10 most CPU consuming oracle sessions.

Set pages 1000 lines 222
col program form a50 heading "Program"
col CPUMins form 99990 heading "CPU in Mins"
select rownum as rank, a.*
from (
SELECT v.sid, program, v.value / (100 * 60) CPUMins
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;

advertisements

 

Sample Output

      RANK        SID Program                                            CPU in Mins
---------- ---------- -------------------------------------------------- -----------
         1       4692 oracle@super-power1(LMS1)                               1906
         2       2682 oracle@super-power1(LMS0)                               1712
         3       2012 oracle@super-power1(LMD0)                                672
         4       1011 oracle@super-power1(SCM0)                                139
         5       2353 oracle@super-power1(CJQ0)                                 96
         6       1676 oracle@super-power1(DBRM)                                 73
         7       3689 oracle@super-power1(MMNL)                                 63
         8       2359 dbfs_client@xxxxxxxxxxqtv-vat(TNS V1-V3)                  57
         9       1691 dbfs_client@ xxxxxxxxxxqtv-vat (TNS V1-V3)                46
        10       4700 oracle@super-power1(Q006)                                 36

Unix Command to find out top CPU consuming sessions

_____________________________________________________________________________________________________________________

Website Stats