Lisa,
To get info at the thread level and determine what thread within a process
is consuming the most CPU you have to use something like
performance monitor or Process Viewer.
Using Performance monitor you want to choose THREAD from the performance
object drop-down and choose ID Thread from the counter list..
In the instance list box you will see a separate line for each oracle
thread showing the instance # of the thread. You can choose any/all of the
threads
and then choose ADD. The value now in the performance monitor will show you
the actual thread ID that you can match to the SPID column
in V$PROCESS.
For ex.
SELECT s.*
FROM v$session s, v$process p
where p.spid='spid_in_perf_mon'
and p.addr=s.paddr;
Rick
"Koivu, Lisa"
<Lisa.Koivu@efair To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
field.com> cc:
Sent by: Subject: RE: 100% CPU utilization,
urgent
[EMAIL PROTECTED]
01/20/2003 10:36
AM
Please respond to
ORACLE-L
Thomas, thanks for your post.
However I don't see where I can match the threads on NT to what I see in
Task Manager. Am I missing something?
To be more explicit, here's what I've got:
SQL> select * from dba_nt_threads;
ID_THREAD B NAME SID SERIAL# USERNAME
STATUS OSUSER
--------- - ----- --------- ---------- ------------------------------
-------- --------
3144 1 PMON 1 1
ACTIVE SYSTEM
2436 1 DBW0 2 1
ACTIVE SYSTEM
2972 1 LGWR 3 1
ACTIVE SYSTEM
3172 1 CKPT 4 1
ACTIVE SYSTEM
2976 1 SMON 5 1
ACTIVE SYSTEM
3380 1 RECO 6 1
ACTIVE SYSTEM
2840 11 1973 LISA
ACTIVE lkoivu2
900 12 2 DBSNMP
INACTIVE SYSTEM
8 rows selected.
I see no processes in task manager that correspond to any of the numbers
listed in ID_THREAD. In fact my sessions script used to reference spid,
but I took it out because I couldn't make sense of it on Windows.
Thanks for any insight.
Lisa Koivu
Oracle Dogbarf Cleanerupper
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
-----Original Message-----
Sent: Monday, January 20, 2003 9:00 AM
To: Multiple recipients of list ORACLE-L
Create the view dba_nt_threads and query it, then run the monitor CPU per
session. These are not my scripts --- I'm pretty sure that they were
posted here by others --- but I did not capture the information on who
originally wrote them. My apologies. HTH
--cr_dba_nt_threads.sql
-- run as sys
create or replace view
dba_NT_threads
as
select
p.spid "ID_THREAD",
p.background "BACKGROUND",
b.name "NAME",
s.sid "SID",
s.serial# "SERIAL#",
s.username "USERNAME",
s.status "STATUS",
s.osuser "OSUSER",
s.program "PROGRAM"
from
v$process p,
v$bgprocess b,
v$session s
where
s.paddr = p.addr
and
b.paddr(+) = p.addr;
create public synonym dba_nt_threads for dba_nt_threads;
create public synonym threads for dba_nt_threads;
-- monitor CPU per session
-- requires timed statistics on
col sid format 99999999
SELECT v.SID, SUBSTR(s.NAME,1,30) "Statistic", v.VALUE
FROM V$STATNAME s, V$SESSTAT v
WHERE s.NAME = 'CPU used by this session'
AND v.STATISTIC# = s.STATISTIC#
Hussain Ahmed
Qadri <hussain To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>
@skm.org.pk> cc:
Sent by: root urgent
01/20/2003 12:44
AM
Please respond
to ORACLE-L
HI all
We have a consistent problem of CPU utilization 100%. We have had this
problem since Saturday, but it automatically subsided, I mean went back to
normal after a few hours, and remained normal on Sunday as well. But its
back to 100% since morning, that is when the load on the server has gone up
again to 100% and over all work is non-existent.
Our machine is Compaq Proliant ML350, 900 MB ram, 933 single Processor,
Database size of roughly 5 GB. WINNT4.0, Oracle 8.1.7.
I have checked the temporary tablespaces, they are normal.
We have a 24x7 environment, a hospital, so please can you suggest the areas
to look in to, its really very urgent.
Regards,
Hussain
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
