Monitoring Oracle processes on Windows

Monitoring Oracle processes on Windows NT:
Monitoring Oracle processes in Windows NT
Ravinder Bhalla
06 Oct 2004
Rating: -4.33- (out of 5)

It's much easier to monitor CPU usage in a Unix environment for each individual Oracle process (i.e., PMON, SMON, DBWR) as compared to NT. In NT, there is only a single process (i.e., oracle.exe). In order to get the status of individual threads of Oracle processs in a Windows environment, use Quick Slice, a GUI-based standalone application. It's a free download from Microsoft. It works with Oracle8i and 9i in all Windows environments. Here is how to use it:

1. First of all, you need to find the process id of all threads running in Oracle.

SQL> select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr;

Here is the output:

Thread ID Backg User Name OS User STATUS Session ID Serial No.
OS Program
------------ ----- ---------- ---------- -------- ---------- ----------

20229 PMON Oracle ACTIVE 1 1
oracle@db02.s0.gc.media.com (PMON)
20231 DBW0 Oracle ACTIVE 2 1
oracle@db02.s0.gc.media.com (DBW0)
20233 LGWR Oracle ACTIVE 3 1
oracle@db02.s0.gc.media.com (LGWR)
20235 CKPT Oracle ACTIVE 4 1
oracle@db02.s0.gc.media.com (CKPT)
20237 SMON Oracle ACTIVE 5 1
oracle@db02.s0.gc.media.com (SMON)
20239 RECO Oracle ACTIVE 6 1
oracle@db02.s0.gc.media.com (RECO)
20241 ARC0 Oracle ACTIVE 7 1
oracle@db02.s0.gc.media.com (ARC0)
20243 ARC1 Oracle ACTIVE 8 1
oracle@db02.s0.gc.media.com (ARC1)
20245 DMON Oracle ACTIVE 9 1
oracle@db02.s0.gc.media.com (DMON)
18460 SYS Oracle ACTIVE 17 22737
sqlplus@db02.s0.gc.media.com (TNS V1-V3)
15700 Oracle ACTIVE 12 7
oracle@db02.s0.gc.sj.ipixmedia.com (LNS0)

In this example, thread 20229 is PMON and 20231 is DBW0, etc.

2. Now doubleclick on the qslice.exe file under "c:Program FilesResource Kit."

3. To display the individual threads of a process, find the process -- oracle.exe and double click on it.

Another graphical window will pop up showing all of the threads and the cpu usage of each.

4. To identify the threads, match up the TID column to the thread ID column of the previous SQL statement. To match the TID numbers with the Oracle thread process ID you will need to convert the TID number from HEX to DEC using a scientific calculator.

The red bar displays kernel time taken by that thread; the blue bar displays user time.