| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Important: Oracle processes taking lots of CPU
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Martic Zoran
Sent: Wednesday, November 24, 2004 3:30 PM
To: cary.millsap_at_hotsos.com; ORACLE-L_at_freelists.org
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
> 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_at_freelists.org =
> [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of John Kanagaraj
> Sent: Wednesday, November 24, 2004 1:13 PM
> To: 'New DBA'; ORACLE-L_at_freelists.org
> 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 **
> --
> http://www.freelists.org/webpage/oracle-l
>
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 24 2004 - 15:20:04 CST
 |
 |