Re: I/O waits hurting anyone?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "ric.van.dyke@xxxxxxxxxx" <ric.van.dyke@xxxxxxxxxx>, "vxsmimmcp@xxxxxxxxxx" <vxsmimmcp@xxxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Feb 2014 13:18:35 -0800 (PST)
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
Other related posts:
