asked on

Process wise CPU Utilization

Dear Club members

I want to see CPU utilization of various Oracle processes/sessions separatelly. We use window 2000 server. Task Manager shows Oracle.exe CPU utilization, I want to see CPU utilization of oracle processes, this will help to identify the query makes CPU busy.

Could any body help me in this regard.

Thanks

ASKER CERTIFIED SOLUTION

baonguyen1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

Usually the query/ies that cause Oracle the most work can be found by running a select from v$sess_io and looking for the session with the most physical I/O like the following query.
Note that I have this limited to sessions with more than 100 physical I/Os or 1000 logical I/Os (consistent_gets). Usually a session with high CPU utilization is also high in one or both of these I/O measures also, and that is usually the real problem.

column sid format 999;
column "Bl gets" format 99999999;
column "Phys rds" format 9999999;
column "Bl chgs" format 99999999;
column "Cons chgs" format 9999999;
column "Cons gets" format 99,999,999;
column time format a8;
undefine user_nm;
prompt Enter a specific Oracle user name to see sessions for that user only, or leave blank to see all
prompt ...
select i.SID, substr(s.username,1,7) "OraUser", s.osuser "OS user",
i.BLOCK_GETS "Bl gets", i.CONSISTENT_GETS "Cons gets",
i.PHYSICAL_READS "Phys rds", i.BLOCK_CHANGES "Bl chgs",
i.CONSISTENT_CHANGES "Cons chgs", to_char(s.logon_time, 'HH24:MI:SS') "Logon",
to_char(sysdate, 'HH24:MI:SS') "Current"
from v$session s, v$sess_io i
where s.sid = i.sid and (s.username like upper('&&user_nm')
or nvl('&&user_nm','x') = 'x' and (i.physical_reads > 100 or i.CONSISTENT_GETS > 10000))
order by (i.physical_reads * 100 + i.CONSISTENT_GETS) desc, i.sid;
undefine user_nm;

expsaleem

ASKER

Thank you very much baonguyen1 and markgeer. I am exploring your precious solutions

expsaleem

ASKER

markgeer

Thank u very much. Your solution provides statistics from starting time of the oracle(When Oracle was started).
If i want to see these information at given time, how do i that.

For example
At 10:00 am SID 12's physical read 3000000
after 10:00 am sid 12's phy read rate is very slow.
At 11:00am sid 20 starts phy. read and it makes system busy but its phy read are 1000000. Then how i can juge that sid 20 makes system busy.
*****************************
Dear baonguyen1
i am still working on ur solution

Thanks

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

expsaleem

ASKER

markgeer

I think you are right. we can submit a job which does this at the specfied intervals.

I shall award points after having some discussion with Baonguyen1

Thanks
**************************
Dear baonguyen1

When i run your qury
select vb.name NOME, vp.program PROCESSNAME, vp.spid THREADID, vs.sid SID
from v$session vs, v$process vp, v$bgprocess vb
where vb.paddr <> '00' and
vb.paddr = vp.addr and
vp.addr = vs.paddr;

Following are the results
PMON ORACLE.EXE 1840 1
DBW0 ORACLE.EXE 2016 2
LGWR ORACLE.EXE 1848 3
CKPT ORACLE.EXE 1352 4
SMON ORACLE.EXE 1920 5
RECO ORACLE.EXE 2004 6
SNP0 ORACLE.EXE 2000 7
----
We run one oracle istance

from Windows performance monitor, Oracle threads are like

oracle /0
oracle /1
oracle /101
oracle 2
oracle /20
oracle/21
.........................

How can i find relation between results from select statement and Windows' performance system threads. Because Threadid(from select statement) does not match with Windows Oracles' threads id