Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Viewing the Oracle Data Buffer Contents

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 partitions:

and nvl(t1.subobject_name,'*') = nvl(s.partition_name,'*')

• Clusters – Clusters present a challenge when joining the v$bh row with its corresponding database object. Instead of joining the bh.objd to object_id, we need to join into data_object_id.

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:

See code depot for full scripts

The above 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 dictionary.

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 industry.

BC has priced this collection of 681 scripts at $79.95.  You can download them immediately at this link:

http://www.rampant-books.com/download_adv_mon_tuning.htm