How Oracle's data buffers work

This paper explores the internal mechanism of Oracle data buffers--oracle Use this memory to prevent unnecessary data blocks from being reread from disk. Understanding how Oracle Data buffers operate is key to successfully using them to tune database performance.

In previous versions of Oracle 8i, blocks of data were automatically placed in the front of the list of recently used data when the blocks were sent from disk to the data buffer. However, this behavior has been changed from Oracle 8i: The new data buffer is placed in the middle of the buffer chain. When you are tuning the database, your goal is to allocate as much memory as possible to the data buffer without causing the database server to be paged in memory. Once the hit rate for the data buffer is less than 90% per hour, you should add a buffer to the block buffer.

Data block survival time

After the data block is transferred, Oracle keeps track of the data Block usage count (touch count, that is, the number of times the data block is accessed by the user thread). If a block of data is used more than once, it is moved to the front of the list of recently used data, ensuring that it is kept in memory for a longer period of time. This new midpoint insertion technique ensures that the most commonly used blocks of data are kept at the top of the list of recently used data, because new blocks of data are moved to the front of the buffer chain only when they are reused.

In summary, Oracle 8i data buffer pools are managed more efficiently than previous versions. By inserting a new block of data into the middle of the buffer and adjusting the buffer chain based on the access activity (frequency), each data buffer is split into two parts: the hot section, which represents half of the most recent use of the data buffer; Cold section, Represents half of the earliest use of a data buffer. Only those blocks that are repeatedly requested are moved into the hot zone of each buffer pool, which makes each data buffer more efficient at buffering common chunks of data.

The size of the hot zone is configured with the following hidden parameters:

_db_percent_hot_default 
_db_percent_hot_keep 
_db_percent_hot_recycle

Oracle Company has not recommended changing these hidden parameters as an official. These parameters should be used only by experienced personnel who understand the internal mechanism and want to adjust their data buffer behavior.

find hot blocks of data

Oracle 8i retains a X$BH internal view table to show the relative performance of the data buffer pool. The X$BH view table has the following columns of data:

Tim: The time difference between two uses and the _db_aging_touch_time parameter is correlated.

Tch: Using count, it is directly related to moving from cold zone to hot zone after being used _db_aging_hot_criteria time.

Because the TCH data column is used to track the number of times a particular block of data is used, you can write a dictionary query to display the hot blocks in the buffer-using blocks of data larger than 10, as follows:

SELECT
   obj      object,
   dbarfil  file#,
   dbablk   block#,
   tch
   touches from x$ BH
WHERE
   tch >
   tch desc;

This advanced query technique is especially useful for tracking objects in the default buffer pool. Once the hot blocks are positioned, you can move them from the default buffer pool to the keep buffer pool.