Adjust the performance through the internal mechanism of the Oracle Buffer

Oracle PerformanceThe adjustment is the prerequisite for ensuring that the Oracle database works normally and is one of the most important tasks in Oracle Database Operations. This article will mainly introduceBuffer ZoneInternal MechanismPerformance Adjustment. 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. I hope the content involved in this article will be helpful to you.

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 will keep track of the count touch count of the data block, 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 the access activity frequency, each data buffer zone is divided into two parts: hot section ), represents the last half of the data buffer; the cold zone cold section) represents the first half of the data buffer. 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.

This article describes how to adjust the performance of Oracle databases by using the internal mechanism of the Oracle buffer zone, I hope the content mentioned above will be helpful to you.