RE: I/O waits hurting anyone?

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Tue, 18 Feb 2014 17:54:06 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0226E396_at_WIN02.hotsos.com>



What, you think just because he wrote it he knows what it all means? :-)

-----Original Message-----
From: Jeff Smith [mailto:jeff.d.smith_at_oracle.com] Sent: Tuesday, February 18, 2014 6:50 PM To: carlos.sierra.usa_at_gmail.com; oratune_at_yahoo.com Cc: Ric Van Dyke; vxsmimmcp_at_subaru.com; Mark W. Farnham; ORACLE-L Subject: RE: I/O waits hurting anyone?

If I were you, I'd take Carlos up on his offer!

-----Original Message-----
From: Carlos Sierra [mailto:carlos.sierra.usa_at_gmail.com] Sent: Tuesday, February 18, 2014 4:47 PM To: oratune_at_yahoo.com
Cc: ric.van.dyke_at_hotsos.com; vxsmimmcp_at_subaru.com; 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: _at_csierra_usa

Life's Good!

On Feb 18, 2014, at 4:18 PM, 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] 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]
> 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] 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-l


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 19 2014 - 00:54:06 CET

Original text of this message