Monitoring Oracle processes in Windows NT

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.