RE: I/O waits hurting anyone?
- From: Jeff Smith <jeff.d.smith@xxxxxxxxxx>
- To: carlos.sierra.usa@xxxxxxxxx, oratune@xxxxxxxxx
- Date: Tue, 18 Feb 2014 15:50:10 -0800 (PST)
If I were you, I'd take Carlos up on his offer!
-----Original Message-----
From: Carlos Sierra [mailto:carlos.sierra.usa@xxxxxxxxx]
Sent: Tuesday, February 18, 2014 4:47 PM
To: oratune@xxxxxxxxx
Cc: ric.van.dyke@xxxxxxxxxx; vxsmimmcp@xxxxxxxxxx; Mark W. Farnham; ORACLE-L
Subject: Re: I/O waits hurting anyone?
Matt,
Or you may want to use SQLT XTRACT on this SQL (MOS 215187.1). If you do, I
volunteer to review the output and provide some insight.
Cheers,
Carlos Sierra
blog: carlos-sierra.net
twitter: @csierra_usa
Life's Good!
On Feb 18, 2014, at 4:18 PM, David Fitzjarrell <oratune@xxxxxxxxx> wrote:
> I second the call to generate a 10046 trace file; even if you don't have the
> Hotsos Profiler you can use tkprof to get a pretty good idea where that time
> is being spent. Mark brings up good points; even if you can prove that the
> I/O waits aren't burning CPU they could very well be blocking another session
> trying to access the same data. Yes, the waits are small, comparatively
> speaking, but they do add up and can do so quickly.
>
> Generate a 10046 trace file (at least at level 8 so you can capture the
> waits) then see how that 0.7 seconds of wait time breaks down.
>
>
> David Fitzjarrell
> Primary author, "Oracle Exadata Survival Guide"
>
>
> On Tuesday, February 18, 2014 11:09 AM, Ric Van Dyke
> <ric.van.dyke@xxxxxxxxxx> wrote:
> A question to ask is why is it doing the IO? Can that be "eliminated"? As
> in, is it doing IO that is unnecessary? Like scanning a table or index it
> shouldn't, doing a full scan where an index would be better (or the other way
> around)? IO has to happen at some point, the key is to do it as little as
> possible.
>
> In the end it's all about elapsed time. All those things add up as you well
> know of course. So what is taking up the most of the total elapsed time?
> Once you know that, try to get rid of it, or if you have to do it, how can
> you do it faster and/or less often.
>
> Know where your elapsed time is going. This is commonly called a PROFILE.
>
> And yes we at Hotsos have a tool called the Hotsos Profiler to do just that.
> All you need is a 10046 trace file of the thing running and it will tell you
> where your time is going.
>
> +--+--+--+--+--+--+--+--+--+--+--+--+--+--+
> Ric Van Dyke
> Education Director
> Hotsos Ltd.
>
> Hotsos Symposium March 2-6 2014
> Make your plans to be there now!
>
>
>
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of McPeak, Matt
> Sent: Tuesday, February 18, 2014 12:47 PM
> To: Mark W. Farnham; 'ORACLE-L'
> Subject: RE: I/O waits hurting anyone?
>
> Yes.. maybe I didn't ask the right question.
>
> The reason this came up was because the DBAs had a report generated showing
> this SQL as the #1 in the database over the past week. But it's only #1 in
> terms of elapsed time.
>
> When I look at these things, I usually look for actual work: gets, physical
> reads/writes, cpu time, etc and ignore elapsed time.
>
> The rationale being: if it is not doing a physical read/write and it is not
> using CPU, who cares?
>
> So I am wondering if there is something else about "elapsed time" that makes
> it a good metric for identifying tuning targets.
>
> Thanks,
> Matt
>
>
> From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
> Sent: Tuesday, February 18, 2014 12:31 PM
> To: McPeak, Matt; 'ORACLE-L'
> Subject: RE: I/O waits hurting anyone?
>
> That depends largely on two factors:
> 1) How much of your i/o "wait" is actually cpu/data movement, burning cpu.
> 2) Whether your i/o is obstructing some other job's need for data access
>
> mwf
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of McPeak, Matt
> Sent: Tuesday, February 18, 2014 12:24 PM
> To: ORACLE-L
> Subject: I/O waits hurting anyone?
>
> I have a process that executes a lot. Over 6 days it's executed 1.3 million
> times. The elapsed time per call averages 0.8 seconds, and the I/O wait time
> per call averages 0.7 seconds.
>
> In other words, it spends most of its time waiting.
>
> I'll look into all that. my question is more general: am I right in saying
> that the I/O waits don't load the system in any way and don't hurt any
> processes besides the one that is waiting?
>
> Thanks in advance!
>
> Matt
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
