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.


Who is Participating?
baonguyen1Connect With a Mentor Commented:
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

Mark GeerlingsDatabase AdministratorCommented:
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;

expsaleemAuthor Commented:
Thank you very much baonguyen1 and markgeer. I am exploring your precious solutions
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

expsaleemAuthor Commented:

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


Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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.
expsaleemAuthor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.