JL Computer Consultancy

What's in the buffer pool (Oracle 8.0)

October 1998

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

Back to Main Index of Topics.

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     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     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     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     Objects owned by SYS have been omitted (owner# > 0)
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.
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
        /*+ ordered */
        bp.name                        pool_name,
        ob.name                        object, 
        ob.subname                     sub_name, 
        sum(ct)                        blocks
               count(*) ct
        group by
        having count(*)/5 > (
                       select max(set_count) 
                       from v$buffer_pool
        )                      bh,
        obj$                   ob,
        x$kcbwds               ws,
        v$buffer_pool          bp
        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
order by
spool off

Back to Main Index of Topics