RE: I/O waits hurting anyone?

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Tue, 18 Feb 2014 12:07:56 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0226E34A_at_WIN02.hotsos.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_rsiz.com] 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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  

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 18 2014 - 19:07:56 CET