Inside the Oracle9i data buffers   Donald K. Burleson

Many Oracle professionals are not aware that Oracle has a view inside the Oracle data buffers.  The view is called v$bh, and while v$bh was originally developed for Oracle Parallel Server (OPS), the v$bh view can be used to show the number of data blocks in the data buffer for every object type in the database.

The following query is especially exciting because you can now see what objects are consuming the data buffer caches.  In Oracle9i, you can use this information to segregate tables to separate RAM buffers with different blocksizes.

Here is a sample query that shows data buffer utilization for individual objects in the database.  Note that this script uses an Oracle9i scalar sub-query, and will not work in pre-Oracle9i systems unless you comment-out column c3.

column c0 heading 'Owner'                     format a15

column c1 heading 'Object|Name'               format a30

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

column c3 heading 'Percentage|of Data|Buffer' format 999,999,999

select

   owner                        c0,

   object_name                  c1,

   count(1)                     c2,

   (count(1)/(select count(*) from v$bh)) *100  c3

from

   dba_objects o,

   v$bh        bh

where

   o.object_id  = bh.objd

and

   o.owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')

group by

   owner,

   object_name

order by

   count(1) desc

;

Here is an actual listing from an Oracle Financials database.  Note that we can easily see the most popular tables in our data buffers, and the amount of RAM that they consume.  This is invaluable information when planning multiple data buffer pools in Oracle9i.

                                                 Number

                Object                               of

Owner           Name                            Buffers

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

INV             MTL_SYSTEM_ITEMS                  7,098

WIP             WIP_TRANSACTION_ACCOUNTS_N1       6,583

PERFSTAT        STATS$ERROR_LOG                   5,635

OE              SO_LINES_ALL                      5,091

OE              SO_LINES_ALL                      5,091

INV             MTL_DEMAND                        4,924

INV             MTL_ITEM_CATEGORIES               3,098

MRP             MRP_FORECAST_DATES                2,248

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:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.