Re: Important: Oracle processes taking lots of CPU
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: new_dba_on_the_block@xxxxxxxxx
- Date: Tue, 23 Nov 2004 06:16:55 -0800 (PST)
Hi New DBA,
Oracle is not accounting everything in CPU used by
this session.
There are some other OS CPU time waits you can observe
from the v$sysstat and v$sesstat, but only if you have
set timed_os_statistics like:
OS User level CPU time
OS System call CPU time
OS Other system trap CPU time
For example OS User level CPU time is always greater
(or it should be) then CPU used by this session.
Also be aware of CPU used when call started if you
have some crap Oracle version.
With very high CPU usage overall on the server you
will have much more unaccounted CPU time in Oracle CPU
used by this session mainly because of time measuring
Oracle problems.
I think things like queueing on the CPU run queue and
some other OS related waits/CPU actions are not
perfectly accounted.
Also there are a lots of other time measurement
errors.
I know that Oracle Internals published one great
article about all possible errros and mistakes about
measuring in Oracle.
Regards,
Zoran
--- New DBA <new_dba_on_the_block@xxxxxxxxx> wrote:
> 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 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
> --
> //www.freelists.org/webpage/oracle-l
>
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
