Re: I/O waits hurting anyone?
Date: Wed, 19 Feb 2014 09:52:23 +1100
Message-ID: <CAOEQsPJObcnoj+nkJWi8x_b11yHkd9KjNDuxwa0w5Ze8ykxBnQ_at_mail.gmail.com>
>>So I am wondering if there is something else about "elapsed time" that
makes it a good metric for identifying tuning targets.
"Elapsed time" includes the time waiting on idle event, ex sql*net event. Sometime it can indicate client app thinks too long, direct development to focus on client rather database.
Regards,
Jinwen Zou
On Wed, Feb 19, 2014 at 9:14 AM, Frits Hoogland <frits.hoogland_at_gmail.com>wrote:
> You can also use Egor Starostin's orasrp (http://oracledba.ru/orasrp/),
> which too will give you the ability to look how the elapsed time is
> composed by cpu time and IO.
>
> Frits Hoogland
>
> http://fritshoogland.wordpress.com
> frits.hoogland_at_gmail.com
> Phone: +31 20 8946342
>
> On 18 Feb 2014, at 22:18, David Fitzjarrell <oratune_at_yahoo.com> 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_at_hotsos.com> 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_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *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_at_rsiz.com <mwf_at_rsiz.com>]
> *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_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 18 2014 - 23:52:23 CET