Re: I/O waits

From: Andrew Allen <andrew.allen_at_handleman.com>
Date: Tue, 11 Feb 2003 19:47:35 GMT
Message-ID: <3E494646.4020400@handleman.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.

Try something like this (you may have to reformat the sql because of posting limits.
-- -----------
set linesize 180

col sid for 9999
col serial# for 999999
col p1text for a30
col p2text for a15
col p3text for a10
col wait_time for 9999 head 'Wait|Time'
col seconds_in_wait for 9999 head 'Seconds|in Wait' col event for a25

col seq# noprint

select w.sid,

        w.seq#,
        w.EVENT,
        DECODE(w.P1TEXT,'file#'    , f.name,
                        'file number', f.name,
                        'name|mode', 'Lock:'||chr(bitand(p1, 
-16777216)/16777215)
                                            ||chr(bitand(p1, 
16711680)/65535) ||
                                     ' Mode:'||bitand(p1, 65536),
                        'address',   l.name,
                        w.P1TEXT)       as P1TEXT,
        w.P1,
        DECODE(w.P1TEXT,'file#'    , o.name,
                        'file number', o.name,
                                               DECODE(w.P2TEXT, 
'number', NVL(e.name, TO_CHAR(p2)),
                                         w.P2TEXT) )      as P2TEXT,
--       w.P2TEXT,
        w.P2,
        w.P3TEXT,
        w.P3,
        w.WAIT_TIME,
        w.SECONDS_IN_WAIT,
        w.STATE
   FROM V$SESSION_WAIT      w,
        V$DBFILE            f,
        sys.EXT_TO_OBJ_view      o,
        V$LATCH_children             l,
        V$LATCHNAME         e
--      sys.EXT_TO_OBJ_VIEW o
where 1=1
   and w.p1     = f.file# (+)
   and w.p1     = o.file# (+)
   and o.lowb   (+) <= w.p2
   and o.highb  (+) >= w.p2

   and l.addr (+) = w.p1raw
   and e.latch# (+) = l.child#

   and event not like 'SQL*Net%'
   and event       <> 'pipe get'
   and event       <> 'rdbms ipc message'
   and event not like '%time%'

;

--

aja

Received on Tue Feb 11 2003 - 13:47:35 CST