RE: Important: Oracle processes taking lots of CPU
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <zoran_martic@xxxxxxxxx>, <cary.millsap@xxxxxxxxxx>, <ORACLE-L@xxxxxxxxxxxxx>
- Date: Wed, 24 Nov 2004 16:23:15 -0500
That's all interesting commentary, but it seems to ignore that his truss
showed a system select call with values for the file descriptors filled in
with non-zero values for read, write, exception simultaneously and an
apparently absurd value for timeout. Did someone explain that and I missed
the message?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Martic Zoran
Sent: Wednesday, November 24, 2004 3:30 PM
To: cary.millsap@xxxxxxxxxx; ORACLE-L@xxxxxxxxxxxxx
Subject: RE: Important: Oracle processes taking lots of CPU
I agree with Cary fully.
As we all know we can use tracing with event 10046
level 8, but you need to know how to interpret the
result.
If you are not using online Internet tools like hotsos
or ubtools then you are stuck with tkprof or forgot
that new name utility from Oracle.
Because you need to pay for excellence from these
sites, good for less knowledgable DBA's, you can even
use stupid tkprof and see some imperfections.
In this case the imperfection can be very big (If the
author of the question can answer what is the diff
between CPU used and CPU from top).
This imperfection apart from 10046 tracing can be
spotted by stupid manual method for long running SQL's
by looking time in ps, top or prstat between two
points in time, while getting all wait stats from
x$/v$ Oracle views.
At the end who cares about maybe 10% imperfect result
in this method.
You will probably find what are the top problems.
But for non experienced DBA's and experienced while
the company is paying small money hostos or ubtools
are probably great.
I personally used ubtools when the site was for free.
Probably was for free until did not perfect the method
as the master hotsos.
Cary, even the x$/v$ views with timed_os_statistics
turned on can be rock solid if used in conjuction with
top/prstat/ps commands with OS timing data.
Cary, what do you think?
Regards,
Zoran
--- Cary Millsap <cary.millsap@xxxxxxxxxx> wrote:
> Both the time spent in user mode (%usr) spinning for
> a latch and the =
> time
> spent in kernel mode (%sys) contribute to the c
> statistic shown in =
> extended
> SQL trace files.
>
> If your system is enduring a lot of preemptions
> (context switching), you =
> can
> also detect that in your extended SQL trace data by
> noticing that a
> significant proportion of your total response time
> is unaccounted for. =
> You
> can detect unaccounted-for time in your extended SQL
> trace data, but I =
> don't
> think there's any way to detect it Oracle's V$/X$
> data.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 1/4 Calgary
> - SQL Optimization 101: 11/8 Dallas, 12/13 Atlanta
> - Hotsos Symposium 2005: March 6-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx =
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of John Kanagaraj
> Sent: Wednesday, November 24, 2004 1:13 PM
> To: 'New DBA'; ORACLE-L@xxxxxxxxxxxxx
> Subject: RE: Important: Oracle processes taking lots
> of CPU
>
> Hi New DBA,
>
> >As I understand, the CPU is used while performing
> >LIOs, doing calculation, sorts or while spinning
> for
> >the latches. My understanding might be wrong! Since
> >there aren't enough LIOs or complex calculations
> >involved I thought that spinning for latches might
> be
> >causing excessive CPU.=20
>
> Spinning does cause CPU issues. I believe that if
> you have many =
> processes
> and comparatively lesser number of processors, then
> you might have a lot =
> of
> CPU consumption that is unaccounted for as cycles
> used for process =
> context
> switching is not accounted in oracle. You will have
> to then look at 'sar =
> -u'
> ( look for high %sys), 'sar -q' (look for high run
> queue size and =
> occupancy)
> and 'sar -w' (look for excessive process switches).
>
> Let us know if this helps,
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> http://tahiti.oracle.com - Manuals for DBAs
> (English only)
> http://www.bibleserver.com - Manual for Life (in
> English, Deutsch, =
> French,
> Italian, Spanish, Portugese, Turkish,...)
>
> ** The opinions and facts contained in this message
> are entirely mine =
> and do
> not reflect those of my employer or customers **
> --
> //www.freelists.org/webpage/oracle-l
>
> --
> //www.freelists.org/webpage/oracle-l
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
