buffer deadlock

Oracle does not really wait on this event; the foreground only yields the CPU. Thus, the chances of catching this event are very low. This is not an application induced deadlock, but an assumed deadlock by the cache layer. The cache layer cannot get a buffer in a certain mode within a certain amount of time. Wait Time:

0 seconds. The foreground process only yields the CPU and will usually be placed at the end of the CPU run queue.

Parameters:

• class The class of the block basically tells what the block contents are going to be used for. In the table below we list all of the different classes that are possible in Oracle.

  Class	Block Type
  1	Data Block
  2	Sort Block
  3	Save Undo
  4	Segment header block
  5	Save Undo Segment header block
  6	Free List block
  7 + (n * 2)	Undo segment header block ((System) Rollback Segment Header)
  8 + (n * 2)	Undo segment block

  If n is 0, the class is pointing at the system rollback segment. The following query will display the class of the block:

  select trunc(p2/10)
    from v$session_wait
   where event='buffer deadlock';

• mode The mode of the block tells the mode in which the block is owned.
• Block/Buffer modes

  Mode	Means
  0	NULL
  1	Current Share (SCUR)
  2	Current Exclusive (XCUR)
  3	Consistent Read (CR)
  4	Consistent Read Examination mode (CRX)
  5	Current, Exclusive new block (NEW)

  The following query will display the block mode:

  select mod(p2, 10)
    from v$session_wait
   where event='buffer deadlock';

• flag The flag points to the internal flags used by the Oracle kernel to get this block. They are for internal use only: Flags passed down with the buffer get

  Flag	Meaning
  0x1	Block is being used in sequential scan only
  0x2	Block not needed after release
  0x4	Release block after operation
  0x8	Get the block in exclusive mode
  0x10	Don't log changes (temporary tables)
  0x20	Called by sort which will get the block and modify it directly (Mode is New or Exclusive)
  0x40	Sort is finished with the block, free the buffer
  0x80	This is a block cleanout
  0x100	Return a NULL pointer on a corrupt block rather than blowing off
  0x200	Block will likely change soon.
  0x400	Not used.
  0x800	Internal flag
  0x1000	Internal flag
  0x2000	Flag is set when Oracle has to wait on block.
  0x4000	User supplied buffer instead of buffer that is in buffer cache.

• dba This is the dba of the block that we deadlock on:

  select name, kind
    from ext_to_obj_view
   where file#  = data_block_address_file(dba)
     and lowb  <= data_block_address_block(dba)
     and highb >= data_block_address_block(dba);

We should be more concerned with the number of deadlocks over a given time period than the characteristics of an individual wait. It is important to see on which objects buffer deadlocks are happening.