|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 modeIt 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 72You see I do not have FREE blocks. Is it a problem?