This note is more for entertainment value than for practical application. It describes a part of one of the internal structures ($$ 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 is my current understanding of what the various bits in the integer mean for Oracle versions 7.3.4, 8.0.4 and 8.1.5. Changes from earlier versions are highlighted.
By the way - the size of x$bh has grown over the years and is currently about 250 bytes per block - or 12% overhead on a 2K block. Another good reason for using larger block sizes if memory resources are a bit limited.
| Bit | Version 7.3.4 | Version 8.0.4 | Version 8.1.5 |
| 0 | buffer_dirty | buffer_dirty | buffer_dirty |
| 1 | about_to_modify | about_to_modify | notify_after_change |
| 2 | mod_started | mod_started | mod_started |
| 3 | block_has_been_logged | block_has_been_logged | block_has_been_logged |
| 4 | temp_data | temp_data | temp_data |
| 5 | being_written | being_written | being_written |
| 6 | waiting_for_write | waiting_for_write | waiting_for_write |
| 7 | checkpoint_wanted | checkpoint_wanted | multiple_waiters |
| 8 | recovery_reading | recovery_reading | recovery_reading |
| 9 | unlink_from_lock | unlink_from_lock | unlink_from_lock |
| 10 | down_grade_lock | down_grade_lock | down_grade_lock |
| 11 | cross_instance_write | cross_instance_write | 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 |
| 16 | direct_access | direct_access | direct_access |
| 17 | moved_to_lru_tail | moved_to_lru_tail | hash_chain_dump |
| 18 | ignore_redo | ignore_redo | ignore_redo |
| 19 | only_sequential_access | only_sequential_access | only_sequential_access |
| 20 | ??? | ??? | prefetched_block |
| 21 | re_write_needed | re_write_needed | block_written_once |
| 22 | flushed | flushed | logically_flushed |
| 23 | ??? | resilvered_already | resilvered_already |
| 24 | ??? | ckpt_writing | ??? |
| 25 | ??? | redo_since_read | redo_since_read |
| 26 | ??? | ??? | ??? |
| 27 | ??? | ??? | ??? |
| 28 | ??? | ??? | ??? |
| 29 | ??? | ??? | plugged_from_foreign_db |
| 30 | ??? | ??? | flush_after_writing |
| 31 | ??? | ??? | ??? |
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 - even though all other occurences 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:
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
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 ;