Re: I/O waits hurting anyone?
Date: Wed, 19 Feb 2014 00:08:16 +0100
Message-ID: <-252807219105257617_at_unknownmsgid>
Orasrp makes the distinction between idle time/wait and waiting as part of processing.
Overall the first step in performance investigation should be to get proof of what is executed (to see if it's necessary and expected) and the second step is to get a time profile of that execution, which both can be provided by a SQL trace at at least level 8.
Frits Hoogland
http://fritshoogland.wordpress.com
frits.hoogland_at_gmail.com
Phone: +31 20 8946342
Op 18 feb. 2014 om 23:52 heeft Jinwen Zou <zjworacle_at_gmail.com> het volgende geschreven:
>>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 Wed Feb 19 2014 - 00:08:16 CET
- application/pkcs7-signature attachment: smime.p7s