Re: Event 'db file seq. read' stuck for hours on the same file#/block# ?

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Mon, 28 Feb 2005 16:03:52 GMT
Message-ID: <Xns960B5209522C5SunnySD@68.6.19.6>

spendius_at_muchomail.com (Spendius) wrote in news:aba30b75.0502280753.1eeb7aaa_at_posting.google.com:

> Hi,
> We have a procedure that makes the sessions that call it hang for
> hours, and I see in V$SESSION_WAIT the event 'db file sequential
> read' showing the same file number and block number ALL THE TIME
> it keeps hanging (in fact I never waited until it ended but always
> killed the sessions).
>
> Is it a normal behaviour ? It's all the more peculiar as the file and
> block id's point to an index block, created on a empty table !
> Nothing moves, everything seems to be stalled to death. The fields
> p1/p2/p3text and p1/p2/p3 keep showing me the same file#, block# and
> blocks values: 12, 37173, 1...
>
> Any idea about what's going on please ?
> (Oracle 8.1.7.4 64-bit on Solaris 8)
>
> Thanks.
>

It might be sitting on an enqueue wait

 SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

          id1, id2, lmode, request, type
     FROM V$LOCK
    WHERE (id1, id2, type) IN
          (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

    ORDER BY id1, request Received on Mon Feb 28 2005 - 10:03:52 CST