RE: How do I find top CPU consumers on a windows machine running Oracle 9i (9.2.0.4 Enterprise Edition)?
- From: "Juan Miranda" <j.miranda@xxxxxxxxxxxx>
- To: <WLJohnson@xxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Jul 2007 08:41:49 +0200
Try this:
set wrap on
set tab off
set trimspool on
set linesize 6000
set pagesize 60
set heading on
set serveroutput on
column SQL_TEXT format A80 wrapped
set tab off
set pages 50
column usern format a6 heading 'User'
column substr(s.status,1,3) format a3 heading 'Stat'
column program format a20 wrap heading 'Program'
column event format a15 wrap heading 'Evento|espera'
column SID format 999 heading 'SID'
column waitt format A8 heading 'waits'
column txt format a200 wrapped heading 'Current Statment'
column cpu format 9999
column phread format 99999 heading 'Lecturas|Fisicas'
column consistent_gets format 9999999 heading 'Consistent|gets'
column spid format A6 heading 'OSpid'
column puser format a8 heading 'O/S|ID' noprint
column BLOCK_GETS format 999999 heading 'Block|gets'
column osu format a6 heading 'OsUser'
column sser format 9999 heading 'Serial'
select s.username usern, p.username puser, s.osuser osu, a.sid, trim(p.spid)
spid, s.serial# sser,substr(s.status,1,3), s.PROGRAM, a.value cpu,
c.BLOCK_GETS, c.CONSISTENT_GETS, c.physical_reads phread,
trim(d.seconds_in_wait) waitt, d.event, sa.sql_text txt
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d,
v$session s, v$sqlarea sa, v$process p
where b.name = 'CPU used by this session' and
p.addr=s.paddr
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid
and a.sid=s.sid
AND s.sql_address=sa.address(+)
AND s.sql_hash_value=sa.hash_value(+)
order by cpu DESC
/
_____
De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] En
nombre de Johnson, William L (TEIS)
Enviado el: martes, 03 de julio de 2007 15:09
Para: oracle-l@xxxxxxxxxxxxx
Asunto: How do I find top CPU consumers on a windows machine running Oracle
9i (9.2.0.4 Enterprise Edition)?
HP Unix is simple - use a top command.
Solaris is not as friendly - but you can use prstat.
When I use task manager in Windows XP, I can only see CPU being consumed by
the Oracle.exe process. I can then perform a "tlist" on the process number
to find out which instance of Oracle is consuming the cpu.
I tried a utility called procexp.exe, but it doesn't tie back to any
sessions in the database.
Does anyone have a utility to find the top CPU consumers on a windows
machine?
I am desperate and just about ready to stop both production instances since
I can not find the offenders and a 4-way, dual core machine has been running
100% cpu for a while.
Thanks!
Other related posts:
