Re: Important: Oracle processes taking lots of CPU

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Wed, 24 Nov 2004 09:01:35 -0800 (PST)
Message-ID: <20041124170135.41081.qmail@web52605.mail.yahoo.com>

Hi New DBA,

timed_os_statistics are just fun for me.

Not a crazy overhead for me. Bigger overheads are usually stupid application executing on top of Oracle :)

I had all my 9i/10g databases set with
timed_os_statistics=1 because they are not the production.

Some of these OS times should be added to the CPU used by this session to get the overall time. These stats are very undocumented. You can check the Metalink, I had one discussion with Oracle about it.

But the parameter is not that important, even setting it and looking all these times.

The most important thing is that these statistics may reveal you some of the discrepancies in Oracle CPU timing (or timing in the computer science globally).

What I mean is:
you can have CPU used by this session 100 s, but your top is showing you 200 s.

This can be because of many many reasons, one of the most suspicious is the ORacle bug related to a few Oracle versions.

Then I am asking you:

What is the version of Oracle you are using?
What is the time in CPU used in session and what in top (in seconds)?
Did you wait to the SQL/PL/SQL to finish before responding to the question 2. as somebody pointed to you?

The main thing is the difference, because there are cases when the Oracle timing can be very bad.

Also, why you are looking Oracle timing when you said you do not have the time to learn tuning?

Regards,
Zoran

New DBA <new_dba_on_the_block_at_yahoo.com> wrote:

> Hi Zoran,
>
> > 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.
> >
>
> We aren't gathering OS statistics as of now. Are
> there
> any drawbacks of using the same?
>
> In case I start gathering OS stats, how do I relate
> the OS System Call CPU time to a particular session?
>
> Does this value get added to the CPU values in 10046
> trace files? If not, then I won't gather much
> information about the process by gathering OS stats.
>
> Regards
> New DBA
>
>
>
>
> __________________________________
> Do you Yahoo!?
> The all-new My Yahoo! - What will yours do?
> http://my.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>

Do you Yahoo!?
All your favorites on one personal page – Try My Yahoo! http://my.yahoo.com

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

Received on Wed Nov 24 2004 - 10:57:22 CST