RE: I/O waits hurting anyone?
- From: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Feb 2014 15:25:57 +0000
Jonathan,
Thank you for your response. I'm pretty sure I could have phrased my question
better, but you understood my question exactly right: it was a philosophical
question only. I always viewed metrics like "I/Os", "gets", "physical reads",
"CPU", etc as the best metrics for identifying "expensive" SQLs. Tuning a
query for those metrics will leave more capacity for other processes. Elapsed
time was just a natural result of the amount of work required... sort of a
second-tier metric, I guess. I wanted to know if I was missing some important
concept by thinking that way.
Regarding everyone's offers to look at the query, I haven't even looked at
tuning the query in question, yet. I may not be in the league of most of the
experts here, but I'm no slouch. I won't waste anyone's time looking at it
until I've looked at it myself.
Thanks to you and everyone for their responses!
Matt
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Wednesday, February 19, 2014 3:38 AM
To: ORACLE-L
Subject: Re: I/O waits hurting anyone?
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
