Re: SQL> select class,count from v$waitstat; show data block contention

From: kyle hailey <oraperf_at_yahoo.com>
Date: Wed, 14 Mar 2001 07:11:31 -0800
Message-ID: <1h2vatc08g5mfhtegvn132vc8031pgro93@4ax.com>

You can look at the wait event

"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

Best
Kyle Hailey Received on Wed Mar 14 2001 - 09:11:31 CST