db_aging_touch_time db_aging_hot

Call for Oracle support & training (800) 766-1884

We are top for USA Oracle Training Clients

Custom Oracle Training

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

§ 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_aging_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 like the one shown below:

SELECT

obj object,

dbarfil file#,

dbablk block#,

tch touches

FROM

x$bh

WHERE

tch > 10

ORDER BY

tch desc;

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 the DBA finds hot blocks in the DEFAULT pool, they should be moved into the KEEP pool.

The next section presents a technique for viewing the actual objects inside the data buffers and the scripts that will show their contents.