Tuesday, 24 March 2009

Oracle buffer busy waits...let's debug it

This post can be longer indeed, but let's from scratch. First, identify from v$session_waits if there're buffer busy waits with this query:

select
p1 "File ID",
p2 "Block ID",
p3 "Reason ID"
from
v$session_wait
where
event = 'buffer busy waits';

Now, you can find file id and block id of buffer busy. Using the following statement you can find the object belonging to waits:
select 
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = &P1
and
&P2 between block_id and block_id + blocks -1;


Now you have to find which session is using the object incriminated.

No comments:

Post a Comment