SOLUTION: Process Explorer

Sysinternals Process Explorer

  is a standalone executable downloadable from www.sysinternals.com, direct link: http://technet.microsoft.com/en-US/sysinternals/bb896653.

This utility uses a graphical interface to allow you to monitor the cpu usage of processes and, more importantly for Oracle, individual threads. Once you have the thread details you can use those to dump information from within oracle to determine what the process in question is doing, allowing you to debug its activity.

Usage

• Download and install Process Explorer from www.sysinternals.com
• Run Process Explorer on the system where you are interested in investigating Oracle's CPU usage

• Identify the executeable you are interested in (in most cases Oracle.exe). In this case we have used "sqldeveloper.exe" as an example: 

• Double click on the executable to open it's properties. If you get a warning about missing symbols, this can be ignored:
• Select the "Threads" Tab from the properties window, a list of threads within the process should be displayed. Select the one you are interested in investigating (likely the one using the most CPU):

Armed with the thread ID, you can now get the Oracle information you need about the process using various selects:

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
AND p.spid     = --TID decimal value-- ;

Thread ID                Backg User Name                      OS User                        STATUS   Session ID Serial No.
------------------------ ----- ------------------------------ ------------------------------ -------- ---------- ----------
OS Program
------------------------------------------------
16723                    VKRM  SYS                            scott                          ACTIVE          200      53883
sqlplus@myserver (TNS V1-V3) 

Additionally you can run the following query to get the SQL Text of the current SQL:

SELECT s2.sql_text
FROM v$process p,
  v$session s1,
  v$sqlarea s2
WHERE p.addr          = s1.paddr
AND s1.SQL_HASH_VALUE = s2.HASH_VALUE
AND p.spid            = --TID decimal value--; 

SQL_TEXT
--------------------------------------------------------------------------------
SELECT s2.sql_text FROM v$process p,   v$session s1,   v$sqlarea s2 WHERE p.addr
          = s1.paddr AND s1.SQL_HASH_VALUE = s2.HASH_VALUE AND p.spid
 = 16723

Collecting More Information

Now that the process has been identified and it's activity scrutinized, there are a number of different actions you may wish to perform:

• Trace the session: To trace the session, use the Thread ID to attach to the process using oradebug:

•	connect / as sysdba
•	ALTER SESSION SET tracefile_identifier = '10046';
•	oradebug setospid XXXX
•	oradebug unlimit
•	oradebug event 10046 trace name context forever, level 12 
•	oradebug tracefile_name

To turn off the trace:

 oradebug event 10046 trace name context off.

• Collect Errorstacks for Debugging: To collect errorstacks, use the Thread ID to attach to the process using oradebug:

•	connect / as sysdba
•	ALTER SESSION SET tracefile_identifier = 'STACK';
•	oradebug setospid XXXX
•	oradebug unlimit
•	oradebug dump errorstack 1
•	oradebug dump errorstack 1
•	oradebug dump errorstack 1
•	oradebug tracefile_name

• Kill the session using the sid and serial# based on the thread id:

•	SELECT p.spid "Thread ID",
•	  s.sid "Session ID",
•	  s.serial# "Serial No."
•	FROM v$process p,
•	  v$session s
•	WHERE s.paddr  = p.addr
•	AND p.spid     = --TID decimal value-- ;
•	
•	Thread ID                Session ID Serial No.
•	------------------------ ---------- ----------
•	16723                           200      53883
•	
•	ALTER system kill session 'sid, serial#';

Traces will be written to the normal trace destination for your version. On 11g you can find the trace file location using the following:

SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/dbadmin/v11203/trace/v11203_ora_16723.trc

 For more information on trace and errorstacks, see:

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning
Document 1364257.1 How to Collect Errorstacks for use in Diagnosing Performance Issues

 

Legacy Solution: Qslice

NOTE: The following is provided as legacy information for reference only. The tools mentioned may have been discontinued and the URLs mentioned no longer active.

Quick Slice is an standalone executable, qslice.exe, included with the NT 4.0 Resource Kit. Using this utility, you are able to monitor the cpu usage for the background threads within the Oracle process. 

QuickSlice shows the percentage of total CPU usage for each process in a system. This tool is similar to Pstat.exe, but it presents the information in a graphical format.

Download and install "Qslice.exe: CPU Usage by Processes".

Can downloaded from below link. Refer README.txt for detail steps.
 

https://technet.microsoft.com/en-us/library/bb625087.aspx

Steps:

-- Run Quick Slice on the server.

-- Once Quick Slice has been started, a graphical display showing all of the processes on the server will be displayed. 

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

For example, to see all of the threads of an Oracle 8.0.x database:
- find the process ORACLE.EXE
- double click on it

-- Another graphical display will appear showing all of the threads and the cpu usage of each. The red bar displays Kernel Time taken by that thread; the blue bar displays user time.

-- Identify the TID with high kernel and user time. 

-- To match the TID numbers with the Oracle Thread process ID you will need to convert the TID number from HEX to DEC using the scientific calculator.

In some version, the HEX number need to be 4 characters in length. 
For example, with a HEX of '39B' from qslice it should be '39B0'.