Recent site activity

Oracle: Buffer Busy Wait 7 - 8.1.5


Thought sure my original doc on Buffer Busy Waits was out there on the internet but can't find it. In the original doc I talk about the meaning of P3 starting in 8.1.6. P3 has 3 positions   XXX and each position represented something. The main distinction was 2XX verse 1XX. The 2XX values are users modifying a block, where as the 1XX values are users reading a block (a read into a new block modifies it thus the buffer busy wait potential)

The following  comes from Steve's site http://www.ixora.com.au/q+a/0104/27230622.htm
but I believe the original doc was from me (since I'm the one that had the codes changed for P3 in 8.1.6 )

P3 - id
The buffer busy wait event is called from different places in
the Oracle kernel. Each place in the kernel uses a different 'id'.
NB:   7.1 - 8.0.6 use one set of ID codes (mostly >1000)
      8.1.5       8.1.5 does not include an 'id' when waiting
      8.1.6+      use a new set of ID codes (100-300)


Buffer Busy Waits ID's
~~~~~~~~~~~~~~~~~~~~~~
     Id    Reason
     ~~    ~~~~~~
      0    A block is being read

   1003    We want to NEW the block but the block is currently
 or 100    being read by another session (most likely for undo).

   1007    We want to NEW the block but someone else has is
 or 200    using the current copy so we have to wait for them
           to finish.

   1010    Trying to get a buffer in CR/CRX mode , but a
 or 230    modification has started on the buffer that has not
           yet been completed.

   1012    A modification is happening on a SCUR or XCUR buffer,
           but has not yet completed

   1012 (dup.)
 or 231    CR/CRX scan found the CURRENT block, but a modification
           has started on the buffer that has not yet been
           completed.

   1013    Block is being read by another session and no other
 or 130    suitable block image was found, so we wait until the read
           is completed. This may also occur after a buffer cache
           assumed deadlock. The kernel can't get a buffer in a
           certain amount of time and assumes a deadlock. Therefore it
           will read the CR version of the block.

   1014    We want the CURRENT block either shared or exclusive
 or 110    but the Block is being read into cache by another session,
           so we have to wait until their read() is completed.

   1014 (duplicate)
 or 120    We want to get the block in current mode but someone else
           is currently reading it into the cache. Wait for them
           to complete the read. This occurs during buffer lookup.

   1016    The session wants the block in SCUR or XCUR mode.
 or 210    If this is a buffer exchange or the session is in discrete
           TX mode, the session waits for the first time and the second
           time escalates the block as a deadlock and so does not show
           up as waiting very long.  In this case the
           <Statistic:exchange_deadlocks> is incremented and we yield the
           CPU for the <WaitEvent:buffer_deadlock>.

   1016 (duplicate)
 or 220    During buffer lookup for a CURRENT copy of a buffer we have
           found the buffer but someone holds it in an incompatible mode
           so we have to wait.
There is a subtle distinction between reason codes 1012 and 1016, but the tuning advice for both is to avoid contention for exclusive locks on the buffers. Reason code 0 means that a process had to wait for a buffer that was busy being read into cache by another process.

Incidentally, these reason codes have changed in 8i. The new reason codes are 3-digit numbers. So far as I can tell, the first digit is 1 for consistent gets, and 2 for current mode gets. The second digit is 2 when requesting an exclusive lock, and 3 when requesting a shared lock on the buffer. The third digit is normally 0, but can be 1 if the buffer is needed for a (consistent read) rollback. If anyone reading this has source code access, I would appreciate some confirmation of this. Anyway, the two reason codes that you need to know about for 8i are 130 (block being read into cache by another process) and 220 (waiting for exclusive access to the block).

Nicely laid out in a table from http://cyzhang1983.itpub.net/post/40572/495934
VersionsValues used
7.1 - 8.0.6Uses one set of ID codes (mostly >1000)
8.1.58.1.5 does not include a value for P3 when waiting
8.1.6 - 9.2Uses a different set of ID codes (100-300)
10.1+Uses the block class
Buffer Busy Waits ID's and Meanings
Reason Code (Id)Reason
<=8.0.68.1.6-9.2>=10.1
00n/aA block is being read
1003100n/aWe want to NEW the block but the block is currently being read by another session (most likely for undo).
1007200n/aWe want to NEW the block but someone else has is using the current copy so we have to wait for them to finish.
1010230n/aTrying to get a buffer in CR/CRX mode , but a modification has started on the buffer that has not yet been completed.
1012-n/aA modification is happening on a SCUR or XCUR buffer, but has not yet completed
1012 (dup.)231n/aCR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.
1013130n/aBlock is being read by another session and no other suitable block image was found e.g. CR version, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block. This should not have a negative impact on performance, and basically replaces a read from disk with a wait for another process to read it from disk, as the block needs to be read one way or another.
1014110n/aWe want the CURRENT block either shared or exclusive but the Block is being read into cache by another session, so we have to wait until their read() is completed.
1014 (duplicate)120n/aWe want to get the block in current mode but someone else is currently reading it into the cache. Wait for them to complete the read. This occurs during buffer lookup.
1016210n/aThe session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock and so does not show up as waiting very long. In this case the statistic: "exchange deadlocks" is incremented and we yield the CPU for the "buffer deadlock" wait event.
1016 (duplicate)220n/aDuring buffer lookup for a CURRENT copy of a buffer we have found the buffer but someone holds it in an incompatible mode so we have to wait.


Comments