What's in the buffer pool (Oracle 8.0)
Corrected 30/July/1999: Thanks to Tom Pall of
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 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 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.
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
/*+ ordered */
having count(*)/5 > (
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