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
复制