Re: I/O waits hurting anyone?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Feb 2014 08:38:00 -0000
Since this question looks like "philosophy" rather than "please solve this
problem", I'll offer a different path from the other responses.
Preamble - this statement my be number one in elapsed time without being
responsible for the largest volume of physical I/O because other statements
are doing so much I/O that the (relatively small) number of I/O requests
made by this statement are spending a lot of time queueing for their I/Os.
If you reduce the I/O required by other statements, this statement may go
much faster as a side effect.
To answer your question, then, there is no metric which tells you the best
target point for tuning "the system" - there are several metrics you use to
point you in useful directions. That's probably why the AWR and Statspack
reports show you "SQL order by...." several different metrics - they give
you several ways of seeing things that are "expensive" in some absolute
way, and let you choose which one(s) are likely to be most relevant to the
business process that's suffering.
Elapsed time tends to be a good metric for a single business process - but
following THAT bit of elapsed time may (as in your example) lead you to
say: "THIS process is slow because THAT process is hammering the disk
drives to death - but no-one happens to think that THAT process has to give
a rapid result."
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
| >
| > *From:* oracle-l-bounce@xxxxxxxxxxxxx [
| > mailto:oracle-l-bounce@xxxxxxxxxxxxx <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 <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 <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
| >
| >
| >
|
|
|
| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2014.0.4335 / Virus Database: 3705/7091 - Release Date: 02/13/14
--
//www.freelists.org/webpage/oracle-l
Other related posts:
