||Oracle Tips by Burleson
Finding Hot Blocks inside the
Oracle Data Buffers
The relative performance of the
data buffer pools is shown in Oracle8i by the internal x$bh
view. This view shows the following columns:
tim – The tim column governs the amount of time
between touches and is related to the new db_aging_touch_time
tch – The tch column gives the number of times
a buffer is touched by user accesses. This is the count that
directly relates to the promotion of buffers from the cold region
into the hot, based on having been touched the number of times
specified by the db_againg_hot_criteria parameter.
Since the tch column gives the
number of touches for a specific data block, the hot blocks within the
buffer can be displayed with a simple dictionary query:
code depot for full scripts
. . .
. . .
This advanced query is especially
useful for tracking objects in the DEFAULT pool. It was pointed out
earlier that there should be enough data blocks in the KEEP pool to
fully cache the table or index. If we find hot blocks in the DEFAULT
pool, they should be moved into the KEEP pool.
We are now ready to learn a
technique for viewing the actual objects inside the data buffers and
the scripts that will show their contents.
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: