Chuck wrote:
> 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.
OK. Try joining v$mystat to v$statname. You may want to save a begin
copy of v$mystat and an end copy ov v$mystat and do a diff report to
give you stats only for your particular execution.
--
Andrew Allen
Received on Wed Feb 12 2003 - 15:59:20 CST
