JL Computer Consultancy

Corrected 30/July/1999: Thanks to Tom Pall of Kansas City for pointing out the defect described below.

With the appearance of Oracle 8, the db block buffer has become more sophisticated. The buffer can now be split up into 3 sections known as the default pool, the keep pool, and the recycle pool; effectively each pool runs an independent LRU algorithm so that you can explicitly rig the buffer to make some objects more persistent whilst other objects are discarded more rapidly.

On top of this, each of the three pools can have more than one lru latch, which splits the pools into a number of 'working data sets'. Although it is not yet documented, the internal X$ tables describing the working data sets actually contain interesting information for each set about the amount of reading, writing, etc. that has happened for that set.

The purpose of this report is to give you a rough idea of whether (a) the various pools are about the right size, and (b) if there are any objects which are having a serious impact on buffering which need to be reviewed.

This script, which has to be run by the SYS account because is makes use of X$ internals, checks the buffer headers (X$BH) to find out which data set they are in, acquires the related objects information from sys.obj$, then allocates the results across the buffer pools by reference to the working data sets.

Be a little cautious with this query - even with the benefit of inline views the CPU cost of execution will be quite high if you have a very large db_block_buffers values

Correction to previous script: If you are not using a buffer pool, the numbering of the low and high set ids used in the view v$buffer_pool is a little surprising, and results in blocks being reported in the correct pool AND in the unused pools. This can be fixed simply by eliminating the unused pools from the output with the predicate: and bp.buffers != 0

.

rem

rem     Script:        buff_obj.sql

rem     Author:        J.P.Lewis

rem     Dated:         25-Oct-1998

rem     Purpose:       List blocks per object in buffer, by buffer pool

rem

rem     Notes:

rem     This has to be run by SYS because the 'working data set' is 

rem     only present as an X$ internal, and the column of the buffer

rem     header that we need is not exposed in the v$bh view

rem

rem     Objects are only reported if they have a signficant number of

rem     blocks in the buffer.  The code here is set to show object

rem     which have 5 times the number of latches active in the

rem     working set with most latches.

rem

rem     There is one oddity - the obj number stored in the x$bh is

rem     the dataobj#, not the obj$# - so some objects (e.g. tables in

rem     clusters) will generate spurious figures where the count is

rem     multiplied up by the number of objects in the data object.

rem

rem     Objects owned by SYS have been omitted (owner# > 0)

rem

rem     The various X$ tables and columns are undocumented, so the code

rem     is written on a best-guess basis, but the results seems to be 

rem     as expected.

rem

clear breaks

clear columns

break on pool_name skip 1 on report 

compute sum of blocks on report

compute sum of blocks on pool_name

column pool_name format a9

column object format a24

column sub_name format a24

column blocks format 999,999

set pagesize 60

set newpage 0

spool buff_obj

select

        /*+ ordered */

        bp.name                        pool_name,

        ob.name                        object, 

        ob.subname                     sub_name, 

        sum(ct)                        blocks

from

        (

        select

               set_ds,

               obj,

               count(*) ct

        from

               x$bh

        group by

               set_ds, 

               obj

        having count(*)/5 > (

                       select max(set_count) 

                       from v$buffer_pool

                       )

        )                      bh,

        obj$                   ob,

        x$kcbwds               ws,

        v$buffer_pool          bp

where

        ob.dataobj# = bh.obj

and     ob.owner# > 0

and     bh.set_ds = ws.addr

and     ws.set_id between bp.lo_setid and bp.hi_setid

and     bp.buffers != 0        --  Eliminate any pools not in use

group by

        bp.name,

        ob.name,

        ob.subname

order by

        bp.name,

        ob.name,

        ob.subname

;

spool off