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).
