Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: q: buffer busy wait(data block wait) when P3=130

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

From: Ted Chyn <tedchyn_at_yahoo.com>
Date: 4 Feb 2002 07:48:53 -0800
Message-ID: <44a19320.0202040748.410e1409@posting.google.com>


kyle, I tried your example
select count(*) from $source and the wait in trace file were 'db file sequential read' other than 'buffer block wait' ?

thnx
ted

oraperf_at_yahoo.com (Kyle Hailey) wrote in message news:<3c5894f3.61573988_at_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
>
> p3
>
> 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 Mon Feb 04 2002 - 09:48:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US