Finding The Thread ID of a Process on MS Windows

The following outlines how to find the Thread ID of an Oracle process
taking up large amounts of processor time.

1) Go into Performance Monitor (perfmon) in Administrative tools.

2) Click on the '+' icon
In the window that pops up:
In Object - select Thread
In Counter - select %ProcessorTime
In Instance - select all the Oracle sessions

Click ADD

3) Use 'Ctrl H'. This will highlight the first thread
In the bottom window select each thread one at a time until the thread
using all the processor time is highlighted.
Note down the instance number of the thread.

4) Click on the '+' button again
In the window that pops up:
In Object - select Thread
In Counter - select ID Thread
In Instance - select the oracle instance number above

Click ADD

5) In the bottom window, highlight the thread ID added in step 4 and note
the number that appears in the Last/Average/Min and Max boxes. This number will be the same in all 4 boxes and is the process ID.

Then run the following script in SQLPLUS and enter the process ID when
prompted.

SELECT A.USERNAME,
A.OSUSER,
A.PROGRAM,
SPID,
SID,
A.SERIAL#
FROM V$SESSION A, V$PROCESS B
WHERE A.PADDR = B.ADDR AND SPID = '&pid';