Re: Oracle Process Problem

100% CPU for processes could be either of a. They are "runaway" processes -- the client has abnormally exited. Get the client process and machine name and program from v$session and verify if the client is still alive. (to go from the Unix PID to the Oracle SID in V$session , use
select s.sid, s.serial#, s.machine, s.program, s.process from v$session s, v$process p
where s.paddr=p.addr and p.spid='&unix_pid'; )

   b. They are doing a lot of logical I/O. Query v$sessstat for "consistent gets"
and "CPU used by this session" at quick intervals and see if these are consistently
incrementing. In that case, you need to validate the SQL that these sessions are
running -- wrongly used indexes / nested loops etc can manifest as 100% cpu if all the blocks are already in the buffer cache, in which case v$session_wait will not show any active wait for these sessions.

   c. They are repeatedly spinning on latches . However, you seem to have checked this
out (use v$session_wait at quick intervals or v$session_event to see if these sessions
are incrementing latch wait counts and wait times).

Hemant
At 06:20 PM Thursday, Anju Bala wrote:
>hi list,
>
>We are running Oracle 9iR2 on Linux ES4. there are certain oracle
>process which is consuming almost 100% of the cpu. I tried finding
>if there is any latch issue but could not find any specific problem
>though I am not very good DBA but I tried all the possibilities I
>could. Could anyone please let me how to figure this problem out and
>resolve it??
>
>System config : 4 CPU with 1GB RAM
>
>TIA
>
>Bala

Hemant K Chitale
http://hemantoracledba.blogspot.com

"There is more to life than increasing its speed." Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l