Donald K. Burleson Oracle Tips

Show the percentage of a table in the data buffer

In a previous tip we talked about how we can use of the v$bh view to display counts all the number of data blocks per object that have been accumulated inside the Oracle data buffers.  For those who are using multiple data buffer pools in Oracle8 and beyond the v$bh query can be enhanced in order to show us the percentage all of table data blocks which are in fact stored within the data buffers.  This can be extremely useful for those Oracle professionals who are trying to identify candidates for storage in the KEEP pool, or for the segmentation of specific tables into named Oracle9i data buffers.

As we may know, starting in Oracle9i we have a multiple blocks size feature, and separate independent data buffers can be created for all objects in the today, for 2k, 4k, 8k, 16k and 32k blocks sizes.

The following script will interrogate to the v$bh  view and give us counts all the number of data blocks in the buffer on a segment-by-segment basis.  Note that the script also then joins into the dba_objects view in order to count the number of data blocks in the segment and compare it to the buffer.  This script is a multi-step process, and rather than make the query complex with in-line views or subqueries, the script has been broken down into three separate queries using temporary cables to hold the intermediate results.

The following query is extremely useful for showing the percentage of data blocks for on each table within the data buffer caches.

set pages 999

set lines 80

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as

select

   o.object_name    object_name,

   o.object_type    object_type,

   count(1)         num_blocks

from

   dba_objects  o,

   v$bh         bh

where

   o.object_id  = bh.objd

and

   o.owner not in ('SYS','SYSTEM')

group by

   o.object_name,

   o.object_type

order by

   count(1) desc

;

column c1 heading "Object|Name"                                format a30

column c2 heading "Object|Type"                                format a12

column c3 heading "Number of|Blocks"                           format 999,999,999,999

column c4 heading "Percentage|of object|data blocks|in Buffer" format 999

select

   object_name       c1,

   object_type       c2,

   num_blocks        c3,

   (num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4

from

   t1,

   dba_segments s

where

   s.segment_name = t1.object_name

and

   num_blocks > 10

group by

   object_name,

   object_type,

   num_blocks

order by

   num_blocks desc

;

Wed Oct 23                                                     page    1

                            Contents of Data Buffers

                                                              Percentage       

                                                               of object       

Object                         Object              Number of data blocks       

Name                           Type                   Blocks   in Buffer       

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

MTL_DEMAND_INTERFACE           TABLE                  38,745         100       

FND_CONCURRENT_REQUESTS        TABLE                  16,636          88       

WIP_TRANSACTIONS               TABLE                  14,777         100       

WIP_TRANSACTION_ACCOUNTS       TABLE                  13,390          33       

CRP_RESOURCE_HOURS             TABLE                   7,806         100       

SO_LINES_ALL                   TABLE                   7,576         100       

ABC_EDI_LINES                  TABLE                   7,041         100       

BOM_INVENTORY_COMPONENTS       TABLE                   6,882          46       

MTL_SYSTEM_ITEMS               TABLE                   4,747          63       

WIP_TRANSACTION_ACCOUNTS_N1    INDEX                   3,996          38       

MTL_ITEM_CATEGORIES            TABLE                   3,390         100       

RA_CUSTOMER_TRX_LINES_ALL      TABLE                   3,264         100       

MRP_FORECAST_DATES             TABLE                   3,082          99       

RA_CUSTOMER_TRX_ALL            TABLE                   2,739          97       

WIP_OPERATIONS                 TABLE                   2,311          34       

SO_PICKING_LINES_ALL           TABLE                   2,006         100       

MTL_DEMAND_INTERFACE_N10       INDEX                   1,482          76       

BOM_OPERATION_RESOURCES        TABLE                   1,456          45       

ABC_EDI_ERRORS                 TABLE                   1,427         100       

ABC_EDI_HEADERS                TABLE                   1,188         100       

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: