Re: How do you relate high I/Os waits to a specific file/process ?

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0309300517.73845674_at_posting.google.com>...
> Hi,
> I'm often called and told "once again the server
> suffers from high io waits activity, what the hell
> is going on with Oracle again..." etc etc., which
> I can verify with 'top', 'sar' or certain options of
> the 'ps' command as well (I'm talking about io waits
> reaching 40% and more in the '%wio' column of sar for
> example).
> I'd like to know whether it's possible to link this
> IO activity and slowliness with
> -precise files, and/or
> -precise processes
> (in order to see, of course, if they're Oracle related).
>
> It's very easy to link high CPU consumption with a process,
> but not so with io activity, so maybe you can help me please ?

Hi, Spendius,

To identify the heavy I/O Oracle sessions or processes, v$sess_io in Oracle can tell you physical reads. You need to correlate the delta (difference) between two consecutive runs of select physical_reads from v$sess_io for each session. But this view does not record physical writes. So you may want to look at v$sesstat for "physical reads" and "physical writes" statistics, again watching the delta between two runs for the same sessions.

If you just need a quick view of what OS processes consume the most I/O and you happen to use Solaris, try my topio command: topio -d. See http://www.stormloader.com/yonghuang/freeware/pio.html for details.

To identify the Oracle files experiencing the most I/O, look at v$filestat. To identify both processes and files, simply look at v$session_wait where event like 'db file%' or 'control file%' or 'log file%'. If the files are not part of Oracle database or if you don't want to login Oracle, tell me what OS you're using and I may be able to tell you how to do this.

Yong Huang Received on Tue Sep 30 2003 - 23:12:29 CDT