Re: I/O waits

Chuck <chuckh_at_softhome.net> wrote in message news:<Xns9320A1EEC3CB1chuckhsofthomenet_at_130.133.1.4>...
> Andrew Allen <andrew.allen_at_handleman.com> wrote in news:3E4AAC3E.3070401
> @handleman.com:
>
> > Chuck wrote:
> >> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in
> >> news:3E4A07F7.7ADB_at_yahoo.com:
> >>
> >>
> >>>Chuck wrote:
> >>>
> >>>>Platform: Oracle 8.1.7
> >>>>
> >>>>I have a script that runs every 10 minutes to check the response time
> >>>>of a query on one of my databases. At the end I am reporting on what
> >>>>events it waited on. I frequently see I/O waits much higher than I
> >>>>expect and would like to see which datafiles it's waiting on. Is
> >>>>there a way to report which datafiles the query accessed and how much
> >>>>time was spent waiting for each one? I know I can query
> >>>>v$session_wait to see what files are being accessed at any given
> >>>>moment, but that's not what I want. I want to see the total time
> >>>>waited on each datafile but just for the one session.
> >>>>
> >>>>Thanks in advance.
> >>>
> >>>v$sess_io
> >>>
> >>>hth
> >>>connor
> >>
> >>
> >> This tells me how many I/O's my session did, but is not broken down by
> >> datafiles. What I really need to see is the i/o waits per datafile. I
> >> strongly suspect I am seeing disk contention within a SAN but I have
> no
> >> access to what is placed where in the SAN. To the DBAs it's just a big
> >> black box of disk space. If I can see which files the waits are
> >> occurring on, I can tell the folks who manage the SAN where to look.
> >
> > Statspack is probably what you should be using.
>
> >
>
> That gives me stats for the entire instance over a period of time. I need
> it for one session.

You might want to look at a 10046 trace, level 8 or 12 there you get all the i/o waits with file#, block#

Not sure, whether this is documented somewhere, so Oracle support might refuse to help in case you break something by turning on that event.

on the other hand, this particular event/level is pretty much common wisdom. Received on Fri Feb 14 2003 - 10:04:14 CST