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
compute sum of blocks on report
column pool_name format a9 column object format a24 column sub_name format a24 column blocks format 999,999
break on pool_name
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