Monitoring Oracle processes in Windows NT

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

  1.         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.


This was first published in October 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top