Solved

Process wise CPU Utilization

Posted on 2004-04-12
8
1,501 Views
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.

Thanks


0
Comment
Question by:expsaleem
  • 3
  • 2
8 Comments
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 63 total points
Comment Utility
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;  
 
 
Result:
 
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

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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;

0
 

Author Comment

by:expsaleem
Comment Utility
Thank you very much baonguyen1 and markgeer. I am exploring your precious solutions
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:expsaleem
Comment Utility
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

0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 62 total points
Comment Utility
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.
0
 

Author Comment

by:expsaleem
Comment Utility
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 99
Cross Outer Join 4 50
Export BLOB data from Oracle 10g 4 23
Wrap Oraccle SQL*Plus executable Command 4 33
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now