From:Steve Adams
Date:22-Mar-2001 15:13
Subject:   Free buffers in X$BH

The possible values of X$BH.STATE are:

        0, FREE, no valid block image
        1, XCUR, a current mode block, exclusive to this instance
        2, SCUR, a current mode block, shared with other instances
        3, CR,   a consistent read (stale) block image
        4, READ, buffer is reserved for a block being read from disk
        5, MREC, a block in media recovery mode
        6, IREC, a block in instance (crash) recovery mode
It is normative to have no FREE buffers. FREE buffers are always moved immediately to the tail of the auxiliary replacement list from where they will be reused before any other buffer is aged out. You may see some free buffers shortly after instance startup, or shortly dropping or truncating a segment or putting a tablespace offline that had some buffers in cache, but those free buffer should be re-used very quickly unless the instance is idle.

The query that you have used is misleading. The LRBA_SEQ (low redo block address sequence number) is normally only non-zero for dirty blocks. So the query is effectively counting dirty blocks and CR blocks together as being used, and clean current mode blocks as being available. However, clean blocks may nevertheless be pinned and CR blocks will in general not be. You could fix the query by using the low-order bit of the FLAG field to determine which blocks are dirty and the MODE_HELD column to determine which blocks are pinned, but the information you get will be of very little utility.

When I query the X$BH table's state field, I get 0, 1, or 3. What do these values mean? And in the query
select
  decode(state,
  0, 'FREE',
  1, decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),
  3, 'BEING USED',
  state) "BLOCK STATUS",
  count(*)
from x$bh
group by
  decode(state,
  0, 'FREE',
  1, decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),
  3, 'BEING USED',
  state);

BLOCK STATUS                              COUNT(*)
---------------------------------------- ---------
AVAILABLE                                     3928
BEING USED                                      72
You see I do not have FREE blocks. Is it a problem?