RE: I/O waits hurting anyone?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <vxsmimmcp@xxxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Feb 2014 12:07:56 -0600
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:
