Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: High CPU usage by Oracle

RE: High CPU usage by Oracle

From: Uma Mohoni <umohoni_at_ikimbo.com>
Date: Tue, 22 Jan 2002 11:54:54 -0800
Message-ID: <F001.003F7A04.20020122113700@fatcity.com> 






Thanks, Michael and Jared for the scripts. I really appreciate the help.
Kirti thanks for the metalink references for figuring out the reason the
database shutdown was hanging. I still have not finished processing all the
information I have received. However I will do so and let you all know what
happens. 



Thanks for all the help. Its kind of overwhelming to know there are so many
people out there who want to help you to get your job done. Thanks guys. I
really appreciate it!


-Uma



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


Sent: Tuesday, January 22, 2002 2:10 PM


To: Multiple recipients of list ORACLE-L




Here's a Korn Shell script that I use to identify all of the top CPU hogs in
Solaris.  It is better than "top" because it gives you the actual command
that is running:



#!/bin/ksh


# Show the PS version of the top 37 processes (by CPU usage)



print "UID\tPID\tPPID\tSTIME\t\tTIME\tCMD"



top -n 37 2>/dev/null| tail -38 | head -37 | awk '{print $1}' | while read
PID



do


  PS=`ps -fp $PID 2>/dev/null| tail -1`



  if [[ `echo $PS | grep -c "UID PID PPID C STIME TTY TIME CMD"` -ne 1 ]]
  then


    echo $PS | awk '{for (i = 8; i <= NF; i++) O = O " " $i; print $1 "\t"
$2 "\t" $3 "\t" $5 "\t" $7 "\t


" substr(O,2,59)}'


  fi


done



--Michael



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


Sent: Tuesday, January 22, 2002 1:26 PM


To: Multiple recipients of list ORACLE-L




This query will show current sessions CPU usage.  It may
indicate a particular session is hogging the CPU.



select

   sess.username,
   stat.sid,
   name.name name,




   sum(stat.value)/100 valuesum_seconds


from v$sesstat stat, v$statname name, v$session sess
where


   stat.sid = sess.sid


   and stat.statistic# = name.statistic#
   and name.name like '%CPU%'


group by sess.username, stat.sid, name.name;



You should also use 'top' or some similar tool to 
identify the process.



At times a session may have a 'runaway' process that
is hogging the CPU.  If a single dedicated server process 
is using a very high percentage of the CPU, it's would 
probably be a good idea to kill it.  Be sure to identify
the user or batch program first to make sure it can be
killed.



The output of 'ps -fuoracle' will show a dedicated session
as similar to this: 


     oracleVDRPROD (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))



The following query will match an


process to an Oracle session:



select

   s.username,
   s.sid,
   s.serial#,
   p.pid ppid,
   s.status,
   s.osuser,




   substr(s.program,1,20) client_program,
   s.process client_process,


   substr(p.program,1,20) server_program,
   p.spid server_pid


from v$session s, v$process p


where s.username is not null




Jared









Uma Mohoni <umohoni_at_ikimbo.com>


Sent by: root_at_fatcity.com


01/22/02 07:20 AM


Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        High CPU usage by Oracle






Hi,


I would like help with this problem I am grappling with since yesterday. 
One


of the test boxes with Sun Solaris 2.7 OS and Oracle 8.1.7.2  on it has
started showing 97% and above CPU usage mostly by Oracle . It has slowed
down the application to a point where the testers can not test. Does any 
one


have any idea why Oracle would suddenly hog CPU so much? The normal CPU
usage on these boxes is 2%-3%.  All help is greatly appreciated. Thanks in
advance.



Thanks, 


Uma Mohoni


Consultant, CDI Corporation


@ iKimbo Inc.


500-A Huntmar Park Drive


Herndon, VA 20170


(703) 904-4150 Ext:237


umohoni_at_ikimbo.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Uma Mohoni
  INET: umohoni_at_ikimbo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Jenkins, Michael
  INET: Michael.Jenkins_at_Nextel.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Uma Mohoni
  INET: umohoni_at_ikimbo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).


Received on Tue Jan 22 2002 - 13:54:54 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US