Script to Get the Most CPU Consuming Sessions from Oracle Database

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (578) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips
 

acehints.com Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer