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

From: Thomas Day <tday6_at_csc.com>
Date: Mon, 20 Jan 2003 08:54:37 -0800
Message-ID: <F001.00534C12.20030120085437@fatcity.com>

I don't know off hand. Why does it matter? Aren't you interested in finding out which thread is responsible for most of your CPU utilization? This will give you relative values. What their absolute meaning is is unimportant, isn't it?

                                                                                                                                       
                      Hussain Ahmed                                                                                                    
                      Qadri <hussain           To:      Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      @skm.org.pk>             cc:                                                                                     
                      Sent by: root            Subject: RE: 100% CPU utilization, urgent                                               
                                                                                                                                       
                                                                                                                                       
                      01/20/2003 10:24                                                                                                 
                      AM                                                                                                               
                      Please respond                                                                                                   
                      to ORACLE-L

Thanks for the script, I would like to know how would I interpret the VALUES column, I mean what does it stand for. If the value of CPU used for a particular SID is 2000, what does that mean? Is it the time, in 1/100 th of seconds of the total CPU time? Can you please help me understand this?

Thanks and regards

Hussain

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

Sent: Monday, January 20, 2003 7:00 PM
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: 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