Oracle-L: RE: Which process is taking up so much CPU???

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Thu, 27 Mar 2003 07:58:50 -0800
Message-ID: <F001.005744D8.20030327075850@fatcity.com>

Prompt
Prompt Show total CPU and statement CPU for current session (requires TIMED STATISTICS = TRUE)
Prompt

set verify off

col UNAM format a20  word  heading 'User'
col STMT format a56  word  heading 'Statement'
col RUNT format a08  word  heading 'CPU Time'
col ltim format a20 word heading 'Logon Time'
col etim format a20 word heading 'Connect Time'

col PROG format a30 word heading 'Program|Client Terminal Details' col SID format a10 word heading 'SID/|Serial#'

col DR   format 999999999 heading 'Disk Reads'
col BG   format 999999999 heading 'Buffer Gets'
col EX   format 999999999 heading 'Executions'

col rsecs format 999,999,999.00 heading "CPU time|(seconds)"

Prompt Overall Top CPU for all connected session (used by this session) Prompt
select nvl(username,'ORACLE PROC')||'
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','') UNAM,

ss.program||'-'||ss.terminal||'('||ss.machine||') as '||ss.osuser PROG,

          ltrim(to_char(floor(se1.value*.01/3600), '09')) || ':'


       || ltrim(to_char(floor(mod(se1.value*.01, 360000)/60), '09')) || ':'

       || ltrim(to_char(mod(se1.value*.01, 60), '09'))    RUNT,se1.value*.01

rsecs
from v$session ss, v$sesstat se1, v$statname sn1

where se1.statistic# = sn1.statistic#

and sn1.name like '%CPU used by this session%' and se1.sid = ss.sid
-- and ss.username is not null

and se1.value !=0
ORDER BY 3 DESC,1
/
Prompt Overall Top CPU for all connected session (when call started) Prompt
select nvl(username,'ORACLE PROC')||'
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','') UNAM,

ss.program||'-'||ss.terminal||'('||ss.machine||') as '||ss.osuser PROG,

          ltrim(to_char(floor(se1.value*.01/3600), '09')) || ':'


       || ltrim(to_char(floor(mod(se1.value*.01, 3600)/60), '09')) || ':'

       || ltrim(to_char(mod(se1.value*.01, 60), '09'))    RUNT,se1.value*.01

rsecs
from v$session ss, v$sesstat se1, v$statname sn1

where se1.statistic# = sn1.statistic#

and sn1.name like '%CPU used when call started%' and se1.sid = ss.sid
-- and ss.username is not null

and se1.value !=0
ORDER BY 3 DESC,1
/

Prompt Top CPU for currently active statements Prompt
select nvl(username,'ORACLE PROC')||'
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','') UNAM, -- ss.program||'-'||ss.terminal||'('||ss.machine||') as '||ss.osuser PROG,
ltrim(to_char(floor((se1.value*.01-se2.value*.01)/3600), '09')) || ':'

|| ltrim(to_char(floor(mod((se1.value*.01-se2.value*.01), 3600)/60),
'09')) || ':'

|| ltrim(to_char(mod((se1.value*.01-se2.value*.01), 60), '09'))
RUNT,

	se1.value*.01-se2.value*.01 rsecs,
       NVL(SQL.SQL_TEXT,'***NO known SQL***')      STMT

from v$session ss, v$sesstat se1, v$sesstat se2, v$statname sn1, v$statname sn2

, V$SQL SQL
where se1.statistic# = sn1.statistic#

   and  se2.statistic# = sn2.statistic#
   and  sn1.name  like '%CPU used by this session%'
   and  sn2.name  like '%CPU used when call started%'
   and  se1.sid = ss.sid
   and  se2.sid = ss.sid

   and ss.username is not null
   and se1.value !=0
   and se2.value !=0
   and SS.SQL_ADDRESS = SQL.ADDRESS --(+)    and SS.SQL_HASH_VALUE = SQL.HASH_VALUE --(+) ORDER BY 3 DESC,1
/

clear columns

I'm not sure where this is from (so can't point to an author)

Mark

-----Original Message-----
Bernaus Berraondo
Sent: 27 March 2003 15:14
To: Multiple recipients of list ORACLE-L

I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance.

I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him.

Thank you for your answers!

Fermin.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fermin Bernaus Berraondo
  INET: fbernaus_at_sammic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Mar 27 2003 - 09:58:50 CST