Re: q: buffer busy wait(data block wait) when P3=130

From: Kyle Hailey <oraperf_at_yahoo.com>
Date: Thu, 31 Jan 2002 01:07:38 GMT
Message-ID: <3c5894f3.61573988@newsfeeds-goliath.1usenet.net>

No, it just means two readers are reading the same block and its not in the buffer cache yet

130 want cr -

 	ex, two people do same select
	being read by another user      first one reads block off disk
	second waits on buffer busy

when two users want to read the same block and it is not in the buffer cache, then only one will actually do the disk io, doing a db file wait and the other will do a buffer busy wait until the block is in the cache.

The only way to avoid this wait is either to have the buffer cached or sequence users so that they don't read the same buffer at the same time. If you set off a bunch of users at the same time doing a full table scan of a large table not in the cache you will often see alot of buffer busy waits with p3 = 130

This is very easy to reproduce. For example have 4 or so sessions simultaneously do

alter session set events='10046 trace name context forever, level 8';

select count(*) from sys.source$;
select count(*) from sys.source$;
select count(*) from sys.source$;

etc

and check the trace files. There should be buffer busy waits almost exclusively wity p3=130

Best Wishes
Kyle Hailey

oraperf.sourceforge.net

From a previous post on the news group

"buffer busy waits"

p1 is the file
p2 is the block

This is already pretty good for seeing where the problem might have come from. In 8.1.6 I changed the

values. They were taken out in 8.1.5 ie the p3 value was always 0 in 8.1.5. For 8.1.6 I had them put back in. The old number were a bit unintuitive so we made new P3 values with some logic. The new values we came up with were base on a 3 digit number.

This first digit

1.. - waiting on a reader, ie someone else is reading the block into the buffer cache
2.. - waiting on another session that is modifying the block

The second digit:

.0. - want to new the block
.1. - want the current block (xcur or scur)
.2. - want current in buffer lookup
.3. - want cr

The 3rd digit

..0 - normal
..1 - cr gets the current

So you get (its been two years since I've looked at this so take the following with a grain of salt and better yet chip in if you have some good data), the resulting codes are:

readers "blocking" others:

100 want to new

being read by another user

110 want current - ( scur or xcur)

being read by another user

120 want current - buffer lookup

being read by another user

130  want cr                       - ex, two people do same select
     being read by another user      first one reads block off disk
                                     second waits on buffer busy

writer blocking others:

200 want to new

being modified by another user

blocking a writer 210 want current - (scur or xcur) - ex, change index block mod started on current by another user by two users
blocking a writer 220 want current - buffer lookup - ex, mods on RBS tx table mod started on current by another user - ex, mods on free list

230  want cr                                - blocking a reader
     another mod on block started already   - ex, rc read of block    
                                              while some one else is 
                                              applying rbs
                                              to that block

231  want cr, found current                 - blocking a reader
     other owner of current:mod started     - often tx tab or tab head

--------
CR   - shared access of a coherent version of the block
XCUR - exclusive access of current block  for write

SCUR - shared access of the current block for read New - exlcusive access to create a new block

On 7 Jan 2002 12:15:50 -0800, tedchyn_at_yahoo.com (Ted Chyn) wrote

Best Wishes
Kyle Hailey

http://oraperf.sourceforge.net

Posted Via Binaries.net = SPEED+RETENTION+COMPLETION = http://www.binaries.net Received on Wed Jan 30 2002 - 19:07:38 CST