Re: I/O waits

From: Chuck <chuckh_at_softhome.net>
Date: 14 Feb 2003 18:50:33 GMT
Message-ID: <Xns93228CD0E76F2chuckhsofthomenet@130.133.1.4>

groups_at_karsten-schmidt.com (Karsten Schmidt) wrote in news:c6711ac4.0302140804.47415feb_at_posting.google.com:

> 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.
>

Thanks to you and everyone who pointed out this event. I'm usually very reluctant to do anything that isn't officially documented but in this case I'm making an exception. Received on Fri Feb 14 2003 - 12:50:33 CST