Re: 100% CPU utilization, urgent

[Tim Gorman]

Title: RE: 100% CPU utilization, urgent

We seem to have switched email threads, but the value from the VALUE column in the query on V$SESSTAT is centiseconds of CPU consumed by the session.  Although V$SESSTAT is not updated continuously (but rather in "spurts"), you should see a clear trend in which session is consuming the most CPU.   Whichever session is doing it, I would suggest enabling SQL Trace (a.k.a. "the big hammer") on the session and running TKPROF SORT=PRSELA,EXEELA,FCHELA against the raw ".trc" file produced...   ...or use STATSPACK......or both... ----- Original Message ----- From: Hussain Ahmed Qadri To: Multiple recipients of list ORACLE-L Sent: Monday, January 20, 2003 8:24 AM Subject: RE: 100% CPU utilization, urgent 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----- From: Thomas Day [mailto:[EMAIL PROTECTED]] Sent: Monday, January 20, 2003 7:00 PM To: Multiple recipients of list ORACLE-L Subject: Re: 100% CPU utilization, urgent 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).