Process wise CPU Utilization

Posted on 2004-04-12
Medium Priority
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
  • 3
  • 2

Accepted Solution

baonguyen1 earned 252 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 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;  
SQL> select vb.name 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database


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 248 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 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

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

862 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