||Oracle Tips by Burleson
Viewing the Oracle Data Buffer
The Oracle v$bh view shows
the contents of the data buffers, as well as the number of blocks for
each type of segment in the buffer. This view is primarily useful for
indicating the amount of table and index caching in multiple data
blocks. Combining the v$bh view with dba_objects and
dba_segments provides a block-by-block listing of the data buffer
contents and indicates how well the buffers are caching tables and
indexes. Of course, this is very important in Oracle, since the data
buffer sizes can be altered dynamically.
There are several data dictionary
tricks when writing a script for mapping data objects to RAM buffers:
Duplicate object names - When joining
dba_objects to dba_segments, the name, type, and owner
are all required to distinguish the object sufficiently.
Multiple blocksizes – To show objects in the
separate instantiated buffers (db_2k_cache_size, etc.), we need to
display the block size for the object. We do this by computing the
block size from dba_segments, dividing bytes by blocks.
Partitions - With a standard equi-join, every
object partition joins to every segment partition for a particular
object. Hence, the following qualification is required to handle
nvl(t1.subobject_name,'*') = nvl(s.partition_name,'*')
Multiple caches - There are
situations where a particular block may be cached more than once in
the buffer cache. This is a mystifying concept, but it is easily
overcome by creating the following in-line view:
code depot for full scripts
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Burleson Consulting shares their personal arsenal of
Oracle data dictionary scripts in this comprehensive download. With
decades of experience using Oracle monitoring scripts and Oracle tuning
scripts, BC experts share their secrets for navigating the Oracle data
Packed with 681 ready-to-use Oracle scripts, this is
the definitive collection for every senior Oracle DBA.
It would take man-years to develop these scripts
from scratch, making this download the best value in the Oracle
priced this collection of 681 scripts at $79.95.
You can download them immediately at this link: