Re: I/O waits hurting anyone?
Date: Wed, 19 Feb 2014 08:53:47 -0600
Message-ID: <CAP79kiSk3ad9-e570KXwcxVNODShdF4nt0HdFkpcC_Q-23s2wg_at_mail.gmail.com>
I want to add something to think about [in line with Jonathan & Rick's comments below] about something you asked:
You asked this: "So I am wondering if there is something else about “elapsed time” that makes it a good metric for identifying tuning targets."
One thing to keep in mind is that if no business user is waiting - imagine a nightly batch process that has an SLA of 4 hours and finishes in 3.5 hours - then this process is a "poor" target for tuning. You need to spend your time tuning those processes that users (and the business) is waiting on. Otherwise it's kind of a wasted effort.
That's one of the key metrics in identifying tuning targets - what is important to the business? Are users waiting on this process? Is the business being impacted negatively by the performance of this query (or set of queries).
Granted, there are some queries that negatively impact other queries and should be tuned even when no user is actively waiting on that specific query (such as queries that burn a lot of CPU time causing other business user queries to wait)
Finally, there is some personal benefit to tuning queries that no one is waiting on just for the experience, but those should be near the bottom of your "to do" list normally. (In my opinion anyway)
Regards,
Chris
On Wed, Feb 19, 2014 at 2:38 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:
>
> 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
>
>
On Tue, Feb 18, 2014 at 12:07 PM, 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 19 2014 - 15:53:47 CET