Re: Important: Oracle processes taking lots of CPU

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: Tony.Adolph@xxxxxx, ORACLE-L@xxxxxxxxxxxxx, New DBA <new_dba_on_the_block@xxxxxxxxx>
  • Date: Tue, 23 Nov 2004 15:01:29 +0100

 I know that it may seem confusing on oracle-l, but 'select' doesn't ONLY
refer to the SQL language - in that case, it has to do with I/O multiplexing
- try 'man select'. Identifying what your file descriptors are pointing to
might help. In any case, you are more likely to see things with sar or
iostatthat the V$ views, as you pointed.
Regards, 

Stephane Faroult 

RoughSea Ltd 
http://www.roughsea.com 


On Tue, 23 Nov 2004 05:25 , New DBA <new_dba_on_the_block@xxxxxxxxx> sent:

Tony,

Yes if Oracle is not waiting but working no wait will
be registered. But it should atleast be reflected in
"CPU used by this session" stat. It doesn't.

I traced a few processes, but the trace files show no
SQL which takes lots of CPU. Moreover, the CPU
utlization in the trace file, or in v$sesstat don't
match with the actual CPU taken by the process as seen
from the OS commands like "top"

Thats why I believe its some kind of O/S issue.

So I did a truss on the process. And I saw the
following line repeating infinitely.

select(2048, 0x800003fffdffb3d0, 0x800003fffdffb4d0,
0x800003fffdffb5d0, 0x800003fffdffb6d0) =
0

I'm not sure how to interpret the output of truss, so
I posted it in this forum since there are many experts
out here, who might be able to interpret it!

Is there any further information I can gather at the
O/S level which throws some light on the problem?

As far as statspack is concerned, we haven't
implemented statspack, but I did run utlbstat/utlestat
and uploaded the output to oraperf.com. It didn't
suggested or detect excess CPU/LIOs, since those stats
are pretty acceptable in the trace files.

Regards
New DBA

--- Tony.Adolph@xxxxxx[1] wrote:

>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



__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com[2] 
--
//www.freelists.org/webpage/oracle-l[3]



--- Links ---
   1 javascript:parent.opencompose('Tony.Adolph@xxxxxx','','','')
   2 modules/refer.pl?redirect=http%3A%2F%2Fmail.yahoo.com
   3 
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
//www.freelists.org/webpage/oracle-l

Other related posts: