Process wise CPU Utilization

Posted on 2004-04-12
Last Modified: 2012-08-14
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.


Question by:expsaleem
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Accepted Solution

baonguyen1 earned 63 total points
ID: 10804657
This can be done with the NT performance monitor.
The NT performance monitor can be started on the server by following
Start --> Programs --> Administrative Tools --> Performance Monitor.
Once the Performance Monitor has been started then the following
steps should be taken.
1) click on the plus "+" sign button on the button menu bar -- the
Add to Chart dialog box should appear
2) in the Object: list box select Thread
3) in the Counter: list box select % Processor Time
4) in the Instance: list box do a multiple select on all of the ORACLE
threads  (The multiple select can be accomplished by clicking on  
the first instance and then shift clicking the last.)
5) Click the Add button in the dialog box and then the Done button
At this point you will see a list of instance numbers/thread/% Processor  
Times at the bottom of the screen.  The list shows each thread associated  
with the Oracle process (assuming that only one Oracle instance is running).  
If multiple Oracle instances are running, then you will find that some of  
the instance numbers (thread numbers) are duplicated.
Each thread has a specific function within the Oracle process.  The first
few are Oracle processes such as PMON, SMON and so forth.  

Each Oracle background "process" (e.g. LGWR, DBWR, ARCH, etc.), and each
dedicated server "process" is a thread of the master ORACLE process on
Windows NT.
You can use this query to find out the thread ID of the background process:  
  select 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;  
SQL> select NOME, vp.program PROCESSNAME, vp.spid THREADID, vs.sid SID  
  2    from v$session vs, v$process vp, v$bgprocess vb  
  3    where vb.paddr <> '00' and  
  4          vb.paddr = vp.addr and  
  5          vp.addr = vs.paddr ;
NOME  PROCESSNAME                                                      THREADID        SID
----- ---------------------------------------------------------------- --------- ---------
PMON  ORACLE.EXE                                                       260               1
DBW0  ORACLE.EXE                                                       261               2
LGWR  ORACLE.EXE                                                       202               3
CKPT  ORACLE.EXE                                                       220               4
SMON  ORACLE.EXE                                                       218               5
RECO  ORACLE.EXE                                                       131               6
SNP0  ORACLE.EXE                                                       271               7
SNP1  ORACLE.EXE                                                       288               8
8 rows selected.

To tune a query you may need to look into the db and you perform many actions , not just look after the proccess. Anyway I hope this helps you

LVL 35

Expert Comment

by:Mark Geerlings
ID: 10807165
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;


Author Comment

ID: 10811325
Thank you very much baonguyen1 and markgeer. I am exploring your precious solutions
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


Author Comment

ID: 10821727

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


LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 62 total points
ID: 10827852
The dynamic performance views (v$...) that the query I gave you is based on are cumulative from the time of the last Oracle startup.  They do not indicate exactly when things happened.  One option you could use is to create a reporting table with a structure that could hold the results of the query, then run an "insert into ... select ..." using your reporting table as the destination and the query I gave you from the v$... views as the source.  

Also, you should be aware that if/when a user logs out of Oracle, their history in these v$... views is cleared out.

Author Comment

ID: 10830142

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

Dear  baonguyen1
When i run your qury
  select 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

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question