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
-- use outer join to show sniped sessions in
-- v$session that don't have an OS process
and p.addr(+) = s.paddr
-- uncomment to see only your own session
--and userenv('SESSIONID') = s.audsid
order by username, sid
/
Jared
Uma Mohoni <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/22/02 07:20 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Uma Mohoni
INET: [EMAIL PROTECTED]
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: [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.com
--
Author:
INET: [EMAIL PROTECTED]
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: [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.com
--
Author: Jenkins, Michael
INET: [EMAIL PROTECTED]
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: [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.com
--
Author: Uma Mohoni
INET: [EMAIL PROTECTED]
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: [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).
