Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 10 June 2012

Monitor CPU Usage of Oracle processes on Windows Env.

Check if CPUs are running at full capacity or are they being underutilized.


You can also monitor CPU usage for individual thread using QSlice on windows Env.
Download the Qslice and run the setup. You can download Qslice from:
 In the below Example you can see the CPU usage of Toad. As similar by clicking on the oracle.exe in Qslice it displays all threads and CPU usage for each thread.




















The below script is used to monitor CPU and DISK I/O for Oracle processes by thread

select p.spid thread, s.username,
    decode(nvl(p.background,0),1,bg.description,
         s.program ) program, ss.value/100 CPU,physical_reads disk_io
 from v$process p, v$session s, v$sesstat ss, v$sess_io si, v$bgprocess bg
 where s.paddr=p.addr and ss.sid=s.sid and ss.statistic#=12  and si.sid=s.sid and bg.paddr(+)=p.addr
 order by ss.value desc;
Output from Toad:
THREAD
USERNAME
PROGRAM
CPU
DISK_IO
4864
SYS
Quest.BRT.exe
6
1
5880
SYS
Toad.exe
1
481
5668
SYS
sqlplusw.exe
0
0
3668

db writer process 0
0
13
4588

System Monitor Process
0
65,530
5292

Archival Process 0
0
0
3936

distributed recovery
0
1
6064

process cleanup
0
0
2092

Redo etc.
0
13
3764

Archival Process 1
0
0
2492

checkpoint
0
0


Note: Maintain a margin of available disk space. Get notified when the disk space falls below the margin. You can also run your own programs/scripts to clear disk clutter when thresholds are crossed. If you have job process this query would show you which are they:
 select p.spid thread, s.username,
     case
     when p.background is null and s.program is null then bg.description
     when p.background is null then s.program
     when p.background = 1 then bg.description
     end program,
     ss.value/100 CPU,physical_reads disk_io
from  v$process p, v$session s, v$sesstat ss, v$sess_io si, v$bgprocess bg
where  s.paddr=p.addr and  ss.sid=s.sid and  ss.statistic#=12 and  si.sid=s.sid and  bg.paddr(+)=p.addr
order  by ss.value desc;

0 comments:

Post a Comment