The CACHE features of the KEEP pool and the CACHE features of the KEEP pool

The CACHE features of the KEEP pool and the CACHE features of the KEEP pool

The use of the KEEP pool is very simple. If the value of DB_KEEP_CACHE_SIZE is greater than 0, you can set the BUFFER_POOL parameter of other objects to KEEP.

Alter table t storage (BUFFER_POOL KEEP );

Select count (*) from t;

Select count (*) from t;

Read from the KEEP pool for the second time

When the KEEP pool is full, the new table will squeeze out the memory occupied by the previous table.

It is better to understand all the physical reads in the first query. This is because the space in the KEEP pool was occupied by the T table at that time. In this case, the T2 Query Needs to be read from the physical disk. After executing this query, we can find that table T2 is all in the cache, table T is replaced with a portion of the data, and more than 3000 blocks are stored in the KEEP pool. However, T queries are all composed of physical reads, but the cache in the KEEP pool does not work.

For common DEFAULT pools, Oracle uses the least recently used algorithm and has a structure similar to a linked list in the memory. When DB_CACHE is filled up, Oracle will swap out the least-used end of the linked list to store new data. In addition, based on the nature of the new data, you can choose whether to put the new data to the most-used or least-used end.

If an index scan is performed when DB_CACHE is full, Oracle considers that the data needs to be cached. Therefore, the minimum used space is cleared to store the cache data of the index scan. If a full table scan is performed on a large table, Oracle considers that the data seldom needs to be accessed. Therefore, after clearing the minimum used space and placing it in the cache data scanned by the table, still put back to the least user end.

The KEEP pool does not use this algorithm. The KEEP pool is actually a piece of available memory that uses a loop-like Algorithm for access. If there is still space available in the KEEP pool, the new data will first use the remaining space. If the KEEP pool is full, Oracle will reuse the KEEP pool from the beginning.

This is why all physical reads are caused by queries to table T. Because table T2 replaces the initial part of data in table T with KEEP, when querying table T, the data in the beginning cannot be found, after the physical read is generated, the data in the middle of the T table is replaced in the KEEP pool. In the same way, when reading the middle of the T table, the data at the end of the T table is replaced. Therefore, after the query is completed, it is found that all queries to the T table are physical reads, and the content in the buffer of the KEEP pool does not work.

In addition, because the size of the T table exceeds the size of the KEEP pool, the data at the end of the T table will replace the data at the beginning. Therefore, all queries to table T are still physical reads.

Through tests, we can find that the CACHE option does not work. In fact, this is not hard to understand. Since it is placed in a separate KEEP pool, it is necessary to CACHE this object, therefore, Oracle uses the default CACHE for all objects in the KEEP pool. Ignore the CACHE and NOCACHE options of the object.

Although the KEEP pool has many characteristics that differ greatly from the DEFAULT pool, one thing is that the two are similar: Recently, the most commonly used BLOCK is replaced with the memory at the latest.

Although the KEEP pool does not adopt the LRU linked list method, Oracle still considers the LRU algorithm. In the cache of the KEEP pool, the more frequently-used the BLOCK, the longer the retention time.