How to adjust the performance through the internal mechanism of the Oracle Buffer

How to adjust the performance through the internal mechanism of the Oracle Buffer

In this advanced Oracle tutorial, I will discuss the internal mechanism of Oracle Data Buffer-Oracle uses this memory to prevent unnecessary data blocks from being rereded from the disk. Understanding how Oracle data buffers are operated is the key to successfully using them to adjust database performance.

In versions earlier than Oracle 8 I, when a data block is sent from a disk to a data buffer, the data block is automatically placed in the front of the recently used data list. However, this behavior has changed since Oracle 8i: the new data buffer is placed in the middle of the buffer chain. When you adjust the database, your goal is to allocate as much memory as possible for the data buffer, without causing the database server to be paged in the memory. Once the hourly hit rate of the data buffer is lower than 90%, you should add a buffer for the data block buffer.

Survival time of data blocks

After the data block is imported, Oracle keeps track of the usage count of the data block (touch count, that is, the number of times the data block is accessed by the user thread ). If a data block is used multiple times, it is moved to the very beginning of the recently used data list, so that it can be saved in memory for a long time. This new Point Insertion Technology ensures that the most commonly used data blocks are retained at the very beginning of the recently used data list, because new data blocks will be moved to the front of the buffer chain only when they are reused.

All in all, Oracle 8i data buffer pool management is more effective than previous versions. By inserting a new data block into the middle of the buffer zone and adjusting the buffer Chain Based on access activity (frequency), each data buffer zone is divided into two parts: hot section ), indicates the last half of the data buffer zone. The cold section indicates the First Half of the data buffer zone. Only data blocks that have been repeatedly requested will be moved into the hot zone of each buffer pool, which makes every data buffer zone more efficient when buffering common data blocks.

The following hidden parameters are used to configure the heat zone size:

_ Db_percent_hot_default
_ Db_percent_hot_keep
_ Db_percent_hot_recycle

Oracle does not officially recommend changing these hidden parameters. These parameters should be used only by experienced people who understand internal mechanisms and want to regulate their data buffer behavior.

Find hot data blocks

Oracle 8i retains an X $ BH internal view table to display the relative performance of the data buffer pool. X $ BH:

Tim: the time difference between the two uses, which is related to the _ db_aging_touch_time parameter.

Tch: count. It is directly related to the transfer from the cold zone to the hot zone after _ db_aging_hot_criteria is used.

Because the Tch data column is used to track the usage of specific data blocks, you can write a dictionary query to display hot data blocks in the buffer zone-use data blocks with a count greater than 10, as shown below:

SELECT
Obj object,
Dbarfil file #,
Dbablk block #,
Tch touches
FROM
X $ bh
WHERE
Tch> 10
ORDER
Tch desc;

This advanced query technology is particularly useful when used to track objects in the DEFAULT buffer pool. Once hot data blocks are located, you can move them from the DEFAULT buffer pool to the KEEP buffer pool.

Fully buffered Database

From Oracle 8i, with the emergence of 64-bit addressing, you can create a database completely buffered in the data buffer. Today, no database can be fully buffered with less than 20 GB of memory, while a larger database still needs some data buffers.

To take advantage of full data buffering, remember the time difference between retrieving data blocks from memory and retrieving data blocks from disk. The access time to the disk is calculated in milliseconds or 1‰ seconds, and the memory speed is calculated in nanoseconds or one thousandth of a second. Therefore, the access speed of the memory is three orders of magnitude faster, that is, it is usually nearly 14,000 times faster than the access speed of the disk.

When fully buffering the Oracle database, you need to carefully plan the buffer and add the value of db_block _ buffers when necessary. When buffering the entire database, multiple data buffer pools are no longer needed, so you can buffer all data blocks in the DEFAULT data pool.

To calculate the number of allocated data blocks, run the following command:

Select
Sum (blocks)
From
Dba_data_files;

SUM (BLOCKS)
-----------
243260

Select
Sum (blocks)
From
Dba_extents;

SUM (BLOCKS)
-----------
321723

The actual number of data blocks used must be determined by viewing DBMS_ROWID to obtain the actual database address, as shown below:

Select
Sum (blocks)
From
Dba_data_files;

 

As the database expands, you must not forget to add parameter values. In the database startup phase, you need to call a script to load the buffer. This step is very simple, just like executing a command such as select count (*) from xxx, because all tables in the database work. This ensures that all data blocks are buffered, greatly improving the performance of read operations. However, disk I/O is still required for write operations. As the cost of memory stick keeps falling, a small database can be completely buffered without any hardware investment, thus significantly improving performance.