Tip

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

    Requires Free Membership to View

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.


This was first published in October 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

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.