How to set buffer cache in Oracle

The buffer cache settings are evolving as the Oracle version is upgraded. The Oracle 8i is set using Db_block_buffers, which represents the number of memory blocks that can be contained in the buffer cache, and the Oracle 9i is set using Db_cache_size later, which represents the buffer The total capacity of the cache can be set in bytes, KB, MB. It is simpler to Oracle 10g, even without having to set the size of the buffer cache alone. Because Oracle 10g introduces ASMM (Automatic Shared Memory Management), a self tuning component that automatically adjusts components in the SGA, such as Shared pool size, db cache size. You only need to set the Sga_target parameter, and then set Statistics_level to typical or all, the other components will be able to automatically adjust the size of each part based on the system's load and historical information.

Since Oracle 8.0, Oracle has provided three types of buffer cache, namely, default, Keep, and recycle. Keep and recycle are optional, and default must exist. Oracle 8i uses db_cache_size to set the default pool, Db_keep_cache_size set keep pool, Db_recycle_cache_size set recycle pool.

Frequently accessed objects are usually placed into the keep type of buffer cache, and the large table infrequently accessed into the Recycle type buffer cache. Other objects that do not specify the buffer cache type will enter the default type buffer cache. The method for specifying the buffer cache type for an object is as follows:

Sql> CREATE TABLE Test (n number) storage (Buffer_pool keep);

sql> ALTER TABLE test storage (Buffer_pool recycle);

If no Buffer_pool phrase is specified, the object enters the default type of buffer cache.

The point here is that, from the name, it's easy to assume that the three buffer cache provides three different mechanisms for managing memory blocks. But in fact, there is no difference between them in management and internal mechanisms. They are just a choice for DBAs to be able to divide database objects into "very hot", "hotter" and "not hot" three types. Because there are always some "very hot" objects in the database, they are frequently accessed. And if the system occasionally makes a full table scan of a large table, it is possible to clear the objects out of memory. To prevent this from happening, we can set the keep type buffer cache and move this very hot object into the keep buffer cache. Similarly, there will always be a large number of tables in the database, which may only need to be accessed once a month in order to generate a report. But it is possible that such a visit, will most of the memory data block cleared out of the buffer cache. To avoid this, you can set the recycle type of buffer cache and move this occasional large table into the recycle buffer cache.

There is no doubt that if we want to set these three types of buffer cache, we need to study and classify them according to the objects in the database, then calculate the size of these objects so that they can be put into different buffer cache correctly. However, in any case, setting these three types of buffer cache can only be considered the lowest level of optimization, that is to say, if we do not have any way to consider setting them. But if we can tune an SQL statement that reads too many chunks of data, reducing its buffer gets by 50%, it's much better than setting multiple buffer cache.

Oracle 9i also provides buffer cache with multiple block sizes (2, 4, 8, 16, or 32) that can be set to hold objects in table spaces of different block sizes. Use the initialization parameter db_nk_cache_size to specify the buffer cache for different block sizes, where n is 2, 4, 8, 16, or 32. When creating a database, the standard block size is used for the system tablespace, using initialization parameters db_block_size the specified default standard block size. You can then specify a table space of up to 4 different, nonstandard block sizes. The table space for each block size must correspond to a different size buffer cache, or you cannot create table spaces of different block sizes.

sql> Create Tablespace tbs_test_16k

2 datafile ' C:\oracle\oradata\ora92\tbs_test_16k.dbf ' size 10M

3 BlockSize 16k;

Create Tablespace tbs_test_16k

*

ERROR is on line 1th:

ORA-29339: Table space block size 16384 does not match the configured block size

Sql> Show Parameter Db_16k_cache_size

NAME TYPE VALUE

-------------------   -----------   ----------

Db_16k_cache_size Big Integer 0

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/