Re: I/O waits

From: Candido Dessanti <candido.dessanti_at_blunet.it>
Date: 13 Feb 2003 01:11:08 -0800
Message-ID: <11c1e2d8.0302130111.7888d95a@posting.google.com>

Chuck <chuckh_at_softhome.net> wrote in message news:<Xns931F69D5E6798chuckhsofthomenet_at_130.133.1.4>...
> 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.

you need to query v$session_wait

SQL> desc v$session_wait
 Name Null? Type

• --------

  ---

  SID NUMBER SEQ# NUMBER EVENT VARCHAR2(64) P1TEXT VARCHAR2(64) P1 NUMBER P1RAW RAW(4) P2TEXT VARCHAR2(64) P2 NUMBER P2RAW RAW(4) P3TEXT VARCHAR2(64) P3 NUMBER P3RAW RAW(4) WAIT_TIME NUMBER SECONDS_IN_WAIT NUMBER STATE

The fields p1,p2,p3 give you different values for different events. Please refere to oracle's documentation for in depth description. Anyway if i remeber correctly you can get objectid,block# causing the wait. Received on Thu Feb 13 2003 - 03:11:08 CST