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
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 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

764 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