JL Computer Consultancy

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.

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

;