Re: Debugging a buffer busy waits

From: <johnbhurley_at_sbcglobal.net>
Date: Sat, 28 Mar 2009 12:41:10 -0700 (PDT)
Message-ID: <9a534396-7d42-41d8-b782-ba8241fea6c0_at_h28g2000yqd.googlegroups.com>



On Mar 28, 1:30 pm, Ste <s..._at_ste.it> wrote:
> Hi all,
>
> Last day I've encountered a problem with a procedure waiting due to a
> buffer busy waits. In v$session_wait I've found FILE# and BLOCK#.
> Looking in DBA_SEGMENTS I've discovered incriminated blocks is belonging
> to a table called 'DUMMYTBL'.
>
> Ok, now I can discover which other SIDs are using this table?
>
> P3 code is 130 meaning no suitable images are found of this block.
> Deadlock are not signalled in alert.log.
>
> Thanks
> Ste

Here's something that might be useful for finding SQL accessing that table ...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1455285314563

Maybe a query like this could be useful to you ...

select *
  from (
select address, hash_value,

       lag(sql_text) over (partition by address, hash_value order by piece) ||

       sql_text ||
       lead(sql_text) over (partition by address, hash_value order by
piece)
          sql_text
  from v$sqltext_with_newlines
       )

 where upper(sql_text) like '%DUMMYTBL%'

Once you find SQL going after this table ... ask the developers responsible for it what is going on? Received on Sat Mar 28 2009 - 14:41:10 CDT

Original text of this message