Home » RDBMS Server » Performance Tuning » Find Session with high cpu usage
Find Session with high cpu usage [message #185667] Thu, 03 August 2006 00:16 Go to next message
kareem2020
Messages: 4
Registered: July 2006
Junior Member
In 9i & 8i,how to find a session which is utilizing high cpu & memory usage from the query?
Re: Find Session with high cpu usage [message #185695 is a reply to message #185667] Thu, 03 August 2006 02:35 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
From Unix, I run
ps -ef | sort +3n

to get the processes sorted by CPU usage.

Then I take the PID from that, and run it through the SQL:
set pages 10000
set long 65536
set linesize 80
break on spid skip page

column osuser   format a8          heading "O/SUser"
column process  format a7          heading "CPid"
column spid     format a7          heading "SPid"
column username   format a15         heading "DbUser"
column status   format a1          heading "S"
column event    format a15         heading "WaitResn"
column wait_time format 99        heading "Time"

break on osuser on process on spid on username on status on event on wait_time

accept response prompt "Enter OS User ID, Client PID, or Server PID: "

SELECT  s.osuser
,       s.process
,       p.spid
,       s.username
,       decode (s.status,'INACTIVE','I'
        ,       'ACTIVE'  ,'A'
        ,       'KILLED','K'
        ,       s.status)    status
,       w.event
,       w.wait_time
,       t.sql_text
FROM    sys.v_$session s
JOIN    sys.v_$process p ON (p.addr = s.paddr)
LEFT OUTER JOIN
        sys.v_$session_wait w ON (w.sid = s.sid)
LEFT OUTER JOIN
        sys.v_$sqltext t
        ON (
                t.address = s.sql_address
        AND     t.hash_value = s.sql_hash_value
        --AND   t.child_number = 0
        )
WHERE   p.background IS NULL
AND     s.audsid != userenv('SESSIONID')
AND     (
                upper(s.osuser) like upper('&response')
        OR      s.process = '&response'
        )
AND     (
                p.username = 'oracle'
        OR      p.username = s.osuser
        )
UNION ALL
SELECT  s.osuser
,       s.process
,       p.spid
,       s.username
,       decode (s.status,'INACTIVE','I'
        ,       'ACTIVE'  ,'A'
        ,       'KILLED','K'
        ,       s.status)    status
,       w.event
,       w.wait_time
,       t.sql_text
FROM    sys.v_$process p
JOIN    sys.v_$session s ON (p.addr = s.paddr)
LEFT OUTER JOIN
        sys.v_$session_wait w ON (w.sid = s.sid)
LEFT OUTER JOIN
        sys.v_$sqltext t
        ON (
                t.address = s.sql_address
        AND     t.hash_value = s.sql_hash_value
        --AND   t.child_number = 0
        )
WHERE   p.background IS NULL
AND     s.audsid != userenv('SESSIONID')
AND     p.spid = '&response'
AND     (
                p.username = 'oracle'
        OR      p.username = s.osuser
        )
ORDER BY 1,2,3
/

There are almost certainly far better ways than this, but I've been using this one for so long it's hard to change.

Ross Leishman
Previous Topic: Collecting Stats taking long to execute in PL/SQL code
Next Topic: Problem with output file using TKPROF - Oracle 9i Release 2
Goto Forum:
  


Current Time: Sun Dec 11 08:02:02 CST 2016

Total time taken to generate the page: 0.05707 seconds