Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Important: Oracle processes taking lots of CPU
I'm no expert here, but here *may be* a few things to think about:
When Oracle is actually doing something it isn't recorded as a wait event,
e.g. getting a datablock that is in cache doesn't generate a wait event.
If your query is "horrible" you could be using loads of CPU without
generating many waitevents.
A little more dodgy info: "db file sequential read" is normally accociated with datafile access by rowid, ie. after an index lookup.
I think I'd try to find out which queries are running during the performance problem times and explaining the queries.
Also, have you run spreport for this time period?
Told you I wasn't an expert, but I hope that prompts other readers to fill in the gaps and give you better hints,
Good luck
Tony
New DBA <new_dba_on_the_block_at_yahoo.com>
Sent by: oracle-l-bounce_at_freelists.org
11/23/2004 11:57 AM
Please respond to
new_dba_on_the_block_at_yahoo.com
To
ORACLE-L_at_freelists.org
cc
Subject
Important: Oracle processes taking lots of CPU
Hi all,
Please forgive my ignorance if the details provided by
me are not sufficient, or I'm not doing proper
analysis.
11.5.8 on 9.2.0.5 O/s HP-UX Risc 64 B.11.11
We are facing a unique problem. Lots of DB processes
take close to 100% CPU and an overall CPU time of more
than 500 mins. This happens with lots of different
applications/forms. The problem doesn't happen all the
time i.e. if the same form is run 10 times, only 1-2
faces that kind of problem.
With my limited knowledge I've been able to get the
following details about the problem.
The DB processes take a huge amount of CPU time. This
fact is not reflected in v$sesstat or the 10046 trace
files. There are processes which take more than 500
mins. of CPU time, but the trace file doesn't account
for even a tiny fraction of that.
When monitoring the process at the OS, the process
takes close to 100% CPU all the time, but the CPU
stats in v$sesstat doesn't increase.
I did a truss on the processes consuming 100% CPU and
it shows the following line repeating infinitely:
select(2048, 0x800003fffdffb3d0, 0x800003fffdffb4d0,
0x800003fffdffb5d0, 0x800003fffdffb6d0) = 0
v$session_wait shows some wait event like "latch
free", "db file sequential read" but with STATE =
waited known time, but the seconds_in_wait keeps
increasing while the process keeps taking 100% CPU all
the while.
Please let me know if anyone has faced a similar
issue. How do I find out what is the process doing
which is eating up all the CPU?
I think that the problem is somehow at the O/S level,
since trace files don't indicate anything wrong, same
forms when run again, run without taking much CPU.
Regards
New DBA
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 23 2004 - 06:50:44 CST