Using Oracle9i x$ fixed tables to interrogate the data buffers   Donald K. Burleson

In another tip we discussed the new v$bh columns and suggested that they could be used to interrogate the buffer movement of specific database objects for inclusion in separate data buffers.

The v$bh view has a column labeled “status” that indicates the lock mode for each data block in the data buffer.  For non-OPS and non-RAC databases, you will see three lock modes:

• xcur – This is a RAM block that has an exclusive lock.

• cr – This mode indicates a downgraded RAM block, that was one in xcur mode.

• free – This is an un-used RAM block – This is extremely important because free blocks in the RAM data buffers can be re-allocated to other SGA regions

column c1 heading 'Status'                 format a10

column c2 heading 'Number|of Data|Buffers' format 999,999,999

select

   status    c1,

   count(1)  c2

from

   v$bh

group by

   status

order by

   count(1) desc

;

Here is the output from an Oracle8i database.

                 Number

                of Data

Status          Buffers

---------- ------------

xcur            311,967

free            270,731

cr               17,302

Note that the status column is extremely important in determining when the RAM data buffers are over-allocated.  So long as the RAM data buffers are smaller than the database, the number of free blocks will decline and disk I/O continues.

If you have defined multiple buffer pools in Oracle9i, you can now use the v$bh view to see how well the data buffers are caching the tables block, and make the most of your RAM resources within the SGA.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: