JL Computer Consultancy

Interpreting the Buffer Flag (X$BH)

July 2002

Updated Mar 2008


This note is more for entertainment value than for practical application. It describes a part of one of the internal structures (X$ tables) used by Oracle, and will hardly ever have any relevance to real-life. Still, it's fun to investigate such things, and one day it might help you somehow.

The object is the db_block buffer header object (X$BH) and the part of it I am going to describe is the FLAG column. This column is a 32-bit integer which can be interpreted using the bitand() function to discover the origin or state of blocks in the buffer.

The map has obviously changed over the versions of Oracle, and the following charts my understanding of what the various bits in the integer mean for Oracle versions 8.1.5, 9.2.0.1 and 10.2.0.3. Changes from earlier versions are highlighted – although I’ve now deleted the columns for 7.3.4 and 8.0.4.

By the way - the size of x$bh has varied over the years and, in 10.2.0.3, appears to be about 164 bytes (down from 188 in 9.2.0.8) per block - or 8% overhead on a 2Kb block, 2% on an 8Kb block – if you use x$kqfco to examine the structure.

It’s also worth pointing out that in earlier versions of Oracle the x$bh structure was stored independently of the memory used for the buffers. However, with the introduction of memory granules to allow dynamic resizing of the SGA, each granule used for the buffer cache contains both the buffers and the section of x$bh relating to those buffers. This is why you can allocate (for example) 64Mb to a cache that uses 8Kb blocks and find that you have 7,984 buffers (in 10.2.0.3) or 8,008 buffers (9.2.0.8) rather than 8,192 buffers. (Note – I don’t know why these figures aren’t consistent with the apparent size of x$bh given above).

Back to Main Index of Topics.


Bit

Version 8.1.5

Version 9.2.0.1

Version 10.2.0.3

0

buffer_dirty

buffer_dirty

buffer_dirty

1

notify_after_change

buffer_reused

buffer_reused

2

mod_started

mod_started

mod_started

3

block_has_been_logged

block_has_been_logged

private

4

temp_data

temp_data

temp_data

5

being_written

being_written

being_written

6

waiting_for_write

waiting_for_write

foreground_waiting

7

multiple_waiters

multiple_waiters

private_clone

8

recovery_reading

recovery_reading

pinned_for_imu

9

unlink_from_lock

unlink_from_lock

unlink_from_lock

10

down_grade_lock

down_grade_lock

down_grade_lock

11

clone_being_written

clone_being_written

clone_being_written

12

reading_as_CR

reading_as_CR

reading_as_cr

13

gotten_in_current_mode

gotten_in_current_mode

gotten_in_current_mode

14

stale

stale

stale

15

deferred_ping

deferred_ping

deferred_ping

16

direct_access

direct_access

direct_access

17

hash_chain_dump

being_evicted

being_evicted

18

ignore_redo

ignore_redo

prewarmed_buffer

19

only_sequential_access

only_sequential_access

only_sequential_access

20

prefetched_block

prefetched_block

prefetched_block

21

block_written_once

block_written_once

block_written_once

22

logically_flushed

logically_flushed

logically_flushed

23

resilvered_already

resilvered_already

??? unused

24

???

transfer_in_progress

transfer_in_progress

25

redo_since_read

redo_since_read

redo_since_read

26

???

waiting_for_bwr

waiting_for_bwr

27

???

fusion_write_queue

remote_transfer

28

???

ping_write_queue

???

29

plugged_from_foreign_db

plugged_from_foreign_db

plugged_from_foreign_db

30

flush_after_writing

flush_after_writing

flush_after_writing

31

???

waiting_for_evict

???

The purpose of the bit number in the first column is to tell you how to decode the flag, for example:

        bitand(flag,power(2,19))

is either 0 (bit not set) or power(2,19). If the latter then the block has been loaded by a sequential read (which in this case means a table scan (or index fast full scan) - even though all other occurrences of tablescans are referred to as scattered reads).

To date I have only ever used the above table to check for blocks which are (a) in current mode, (b) consistent read copies of each other, (c) scanned, or (d) dirty. The following script is an example of how I have recently used the above information against an Oracle 8.0.4 database:

Back to Main Index of Topics.


rem
rem     Script:        buffers.sql
rem     Author:        Jonathan Lewis
rem     Dated:         17th Sep 1998
rem
rem     Purpose:       Report multiple copies of scanned blocks in buffer.
rem     Note:          Has to be run by sys
rem                    Only applies to version 8.0.4
rem

select
        file#,
        dbablk,
        count(*) 
from
(
select
        decode(bitand(flag,power(2,00)),0,'No','Yes') buffer_dirty,
        decode(bitand(flag,power(2,01)),0,'No','Yes') about_to_modify,
        decode(bitand(flag,power(2,02)),0,'No','Yes') mod_started,
        decode(bitand(flag,power(2,03)),0,'No','Yes') block_has_been_logged,
        decode(bitand(flag,power(2,04)),0,'No','Yes') temp_data,
        decode(bitand(flag,power(2,05)),0,'No','Yes') being_written,
        decode(bitand(flag,power(2,06)),0,'No','Yes') waiting_for_write,
        decode(bitand(flag,power(2,07)),0,'No','Yes') checkpoint_wanted,
        decode(bitand(flag,power(2,08)),0,'No','Yes') recovery_reading,
        decode(bitand(flag,power(2,09)),0,'No','Yes') unlink_from_lock,
        decode(bitand(flag,power(2,10)),0,'No','Yes') down_grade_lock,
        decode(bitand(flag,power(2,11)),0,'No','Yes') cross_instance_write,
        decode(bitand(flag,power(2,12)),0,'No','Yes') reading_as_CR,
        decode(bitand(flag,power(2,13)),0,'No','Yes') gotten_in_current_mode,
        decode(bitand(flag,power(2,14)),0,'No','Yes') stale,
        decode(bitand(flag,power(2,15)),0,'No','Yes') deferred_ping,
        decode(bitand(flag,power(2,16)),0,'No','Yes') direct_access,
        decode(bitand(flag,power(2,17)),0,'No','Yes') moved_to_lru_tail,
        decode(bitand(flag,power(2,18)),0,'No','Yes') ignore_redo,
        decode(bitand(flag,power(2,19)),0,'No','Yes') only_sequential_access,
        decode(bitand(flag,power(2,20)),0,'No','Yes') unused_0x100000,
        decode(bitand(flag,power(2,21)),0,'No','Yes') re_write_needed,
        decode(bitand(flag,power(2,22)),0,'No','Yes') flushed,
        decode(bitand(flag,power(2,23)),0,'No','Yes') resilvered_already,
        decode(bitand(flag,power(2,24)),0,'No','Yes') ckpt_writing,
        decode(bitand(flag,power(2,25)),0,'No','Yes') redo_since_read,
        decode(bitand(flag,power(2,26)),0,'No','Yes') unused_0x4000000,
        decode(bitand(flag,power(2,27)),0,'No','Yes') unused_0x8000000,
        decode(bitand(flag,power(2,28)),0,'No','Yes') unused_0x10000000,
        decode(bitand(flag,power(2,29)),0,'No','Yes') unused_0x20000000,
        decode(bitand(flag,power(2,30)),0,'No','Yes') unused_0x40000000,
        decode(bitand(flag,power(2,31)),0,'No','Yes') unused_0x80000000,
        b.*
from
        x$bh b
)
where
        only_sequential_access = 'Yes'
group by
        file#,
        dbablk
having count(*) > 1
;       

Back to Main Index of Topics