RE: How do I find top CPU consumers on a windows machine running Oracle 9i (9.2.0.4 Enterprise Edition)?

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Johnson, William L (TEIS)
Enviado el: martes, 03 de julio de 2007 15:09 Para: oracle-l_at_freelists.org
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!  

--
http://www.freelists.org/webpage/oracle-l