Oracle-L: RE: 100% CPU utilization, urgent

From: Koivu, Lisa <Lisa.Koivu_at_efairfield.com>
Date: Mon, 20 Jan 2003 08:49:35 -0800
Message-ID: <F001.00534BEC.20030120084935@fatcity.com>

Thanks Rick. I knew there was something missing here. And if I'm not mistaken, it's my brain. I'll dig into it and see what I can learn with the brain cells I have left.

Have a great afternoon.

Lisa

-----Original Message-----

Sent: Monday, January 20, 2003 11:42 AM
To: ORACLE-L_at_fatcity.com; Lisa.Koivu_at_efairfield.com

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_at_efair       To:     Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>  
                    field.com>              cc:

                    Sent by:                Subject:     RE: 100% CPU
utilization, urgent                        
                    root_at_fatcity.com

 

 

                    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 <ORACLE-L_at_fatcity.com>

                      @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: tday6_at_csc.com

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: ListGuru_at_fatcity.com (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: Koivu, Lisa
INET: Lisa.Koivu_at_efairfield.com

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