High-speed cache technology for Oracle databases

The first time I came into contact with Oracle databases, I felt that he had a lot of superiority over other databases. Especially in terms of running speed, I tried Oracle10g operations in Linux, the speed is more than 4 times faster than in windows. How can Oracle achieve this? Two days ago, I read a copy of the document "displaying the secrets of Oracle database providing high-performance computing", and I will share it with you:

As the preferred database for complex operations, Oracle databases use the so-called high-speed data cache to achieve high-speed data operations and operations.

The data cache is similar to the operating system cache. It stores data blocks recently read from data files, and the data can be accessed by all users. For example, when we use the SELECT statement to query employee information from the database, we first do not query this data from the data file, but from the data cache, there is no need to query the data files in the disk. The database queries data files only when the data is not cached. Why is Oracle database designed like this? This is because when the database reads data, the READ memory speed is much faster than the read disk speed, so this mechanism can improve the overall data access efficiency. Although other databases also have this design, Oracle databases are relatively superior to other databases in this respect. It is no wonder that Oracle databases have higher memory requirements than other databases. If you sacrifice some memory at a slight cost, you can get a higher data access performance. I think it is worthwhile. Let's take a look at the special performance of Oracle databases in data cache.

1. Idle cache blocks.

After we restart the database, the system will allocate some idle cache blocks to the database. There is no data in the idle cache block, and he waits for nothing to write records. When the Oracle database reads data from the data file, the database will find whether there are idle cache blocks to write data into it. Generally, when the database is started, these cache blocks are pre-allocated in the memory. Therefore, when the Oracle database is started, it will occupy a large amount of memory. However, this removes the need to apply for time from the memory. Therefore, sometimes, although the Oracle database has been started, the memory usage is very high, but it can still run normally in the future. Although the memory usage of other databases is not very high at startup, when the system memory reaches more than 80%, data processing will be significantly affected. Therefore, when we use the SELECT statement to read files from database files, the database first looks for free cache.

Ii. Hit cache blocks.

When the SELECT statement reads data from the database file, it puts the obtained data into the hit cache block. That is to say, when we use the query statement to query employee information from the database, this information will be saved in the cache. This space will be released only after the cache consumption is completed. In this case, the next time you query employee information, you do not need to query the relevant information from the database file again, but directly extract data from the data cache to improve database access efficiency. In addition, data in the hit cache block is not written to the data file. Indeed, the data in the hit cache block has not been changed, and of course it will not be written into the database file.

3. Dirty cache blocks.

When we use the SELECT query statement to query employee information data, the database will mark the empty cache block stored in the data, indicating that the cache block already contains data, cache block in mission. At this time, if we use the data update statement update to update a record, we need to change the name of Michael Jacob to James. After running the update statement, the database first checks the cache for this record. If this record exists, it is changed directly, and mark the cache block as a dirty cache block. In this way, Data Consistency can be maintained. That is to say, the dirty cache block storage has been modified but has not been written to the database file. When a data update statement such as SQL update changes the data in a cache block, the hit cache block will be marked as a dirty cache block by the database. When certain conditions are met, the data content in these dirty cache blocks will be written into the database file, so that the database modification records can be permanently retained. When there is no idle cache block in the system and the user needs to query data, the database queries all the currently dirty cache blocks, write the content in the first modified dirty cache block to the database file to release the dirty cache block. The database marks the dirty cache block as an idle cache block to facilitate data storage next time.

What measures does the Oracle database use to control the conversion between idle cache blocks, hit cache blocks, and dirty cache blocks? You may not believe that the Oracle database manages such complex functions through two tables. The two tables are dirty list and LRU list respectively. The LRU list stores all idle cache blocks and hit cache blocks that have not been moved into the dirty list. Oracle database users may encounter the following situations when querying data:

1. When you search for employee information, the database first queries whether there are idle cache blocks in the LRU list. The queried data starts from the end. When an idle cache block is found, the database writes the retrieved data to the idle cache.

2. If the database first finds a dirty Cache during query, it will move the dirty cache to the dirty list and continue the query, until appropriate idle cache blocks are found.

3. If the database is in the LRU list, it is checked from the end to the header and no idle cache block is found, or the capacity of the idle cache block does not meet the requirements, the database will temporarily end this query. Then, the system will trigger the database write process and write the dirty cache blocks in the dirty list to the database. The dirty cache blocks that have been written to the database file will be marked as idle cache blocks by the database and inserted into the LRU list. After the database completes this operation, the database will search for the LRU list and find the appropriate data in the idle cache. Then, the read data will be written to the idle cache.

Therefore, when using the database, we will find that sometimes reading a large amount of data slows down. There are also some other reasons because the database does not find a large enough idle cache to store the data, so it has to write for read/write operations to release more dirty caches, then perform the query operation.

What should our database administrator do after knowing the working principles of these databases to optimize the Oracle database. To this end, I have the following suggestions:

1. Configure as much memory as possible for the Oracle database. The latest version of Oracle Database. According to official recommendations, the memory size is 1 GB. Although the database can still run when the number of memory is lower than this, the running of the database is moderately compromised. It is more difficult to query a large amount of data. The database server I use now uses 4 GB of memory. I used to use 2 GB. After the memory upgrade, the database performance has been greatly improved.

2. When querying data, try to use restrictions. If you want to query the employee information of the sales department, you do not need to query all employee information. Instead, you can use the WHERE clause to set the query conditions in the SELECT statement. In this way, the idle cache blocks in the dirty list can be fully utilized, instead of frequently performing database write operations due to insufficient free cache block capacity. This will significantly reduce the operation of the database. At the same time, it is best to clearly query the information. If you only need the employee's name and employment date, you do not need to query the employee's birth date and ID card number. Therefore, sometimes rational design of views can also improve the operational efficiency of databases.

3. It is best not to run other services on the database server. In the database server, if other servers are still running, the competition for hardware resources will not only affect the efficiency of the server, but also cause a problem. It will make the data cache block of the database discontinuous. This directly affects the efficiency of querying idle cache blocks in the database. Database write operations on dirty cache blocks and conversions between database tags will also be affected. Therefore, based on my experience, it is best to be independent of the database server. You can only deploy the application server on the same server. For example, Oracle database is a background database of the ERP system. It is best to deploy the database separately from the ERP server. However, the ERP application server and database server can be deployed on one server due to the limited funds of the server. However, it cannot be placed together with application servers such as the email server. This will affect the management efficiency of data cache, and ultimately affect the operational efficiency of the database. As server prices gradually decline, the cost of servers is no longer the key to affecting enterprise database applications. Therefore, for the sake of database performance, the author believes that enterprises should make generous investments in this regard. There is no need to influence the database performance for such a small amount of money.