Multiple Database Block Sizes and the Buffer Cache-白红宇

Multiple Database Block Sizes and the Buffer Cache

阅读量：5249 次

发布时间：2019-06-14

本文共 6269 字，大约阅读时间需要 20 分钟。

In oracle 10g we can have multiple block sizes at the same time. When a tablespace is created we can assign a block size for the objects that will be created in that tablespace.

The DB_BLOCK_SIZE parameter in your initialization parameter file determines the size of your standard block size in the database and frequently is the only block size for the entire database.

The DB_CACHE_SIZE parameter in your initialization parameter file specifies the size (in bytes) of the cache of the standard block sized buffers. Notice that you don’t set the number of database buffers; rather, you specify the size of the buffer cache itself in the DB_CACHE_SIZE parameter.

You can have up to five different database block sizes in your databases. That is, you can create your tablespaces with any one of the five allowable database block sizes.

But before you use non standard block size, you have to define the cache size for these non standard block size. We have a paramter called DB_nK_CACHE_SIZE for setting the cache size for non standard block size.

The new init.ora parameters that allow you to use non-default block sizes are:

DB_2K_CACHE_SIZE

DB_4K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_16K_CACHE_SIZE

DB_32K_CACHE_SIZE

Another classification for buffer cache is depending on the algorithm used to keep the contents into the cache. We have basically 3 types in this catagory.

1) DB_KEEP_CACHE_SIZE

2) DB_RECYCLE_CACHE_SIZE

3) DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE is where the object are always present when they are loaded. The objects which qualifies for this cache are those which are very frquently accessed and which has to be retained in memory. For example, frquently used small lookup tables. This cache is a subset of default cache defined by parameter DB_CACHE_SIZE. For any database we need to have DB_CACHE_SIZE set.

DB_RECYCLE_CACHE_SIZE is where you dont want to store the object. You want to clear off the object from cache as soon as it is used. You have to be careful while using this, since this may incure performance hit in case you allocated a frequently used object to this cache.

DB_CACHE_SIZE is the size for default cache.

it is important to note that the init.ora parameters and functionality regarding the keep and recycle buffer pools has changed between Oracle8i and Oracle9i. Those changes are

summarized in the table below:

Version Of Oracle Init.ora parameters Functionality

Oracle8i BUFFER_POOL_KEEP = <buffers> Subsets of the data

BUFFER_POOL_RECYCLE = <buffers> buffer cache

Oracle9i and 10g DB_KEEP_CACHE_SIZE = <size> Independent of the

DB_RECYCLE_CACHE_SIZE = <size> data buffer cache

To specify the use of the keep, recycle or default buffer pools, you can use the storage clause of the alter table statement:

alter table <table_name> storage (buffer pool keep);

alter table <table_name> storage (buffer pool recycle);

alter table <table_name> storage (buffer pool default);

Note: The keep and recycle buffer pools are only available for the standard

block size. Non-standard block-size caches have a single default pool.

So again back to non standard cache size. Lets say the default block size is 8K and you want to create 1 more block size for you future tablespaces. In that case you have to

assign the buffer cache for those block size in the memory. Remember, when

you create a non standard block sizes, the memory (cache size) allocation for these block

size will be taken again from physical memory RAM and hence the RAM consumption with go up. This memory wont be allocated from existing db_cache_size.

Example

SQL> show parameters db_cache_size

NAME TYPE VALUE

———————————— ———– ——————————

db_cache_size big integer 200M

Now we have 200M set for DB_CACHE_SIZE and db_keep_cache_size and db_recycle_cache_size is not set.

SQL> show parameter db_keep_cache_size

NAME TYPE VALUE

———————————— ———– ——————————

db_keep_cache_size big integer 0

SQL> show parameter db_recycle_cache_size

NAME TYPE VALUE

———————————— ———– ——————————

db_recycle_cache_size big integer 0

Also we can see the size of buffer cache using show sga

SQL> show sga

Total System Global Area 1073741824 bytes

Fixed Size 1984184 bytes

Variable Size 750786888 bytes

Database Buffers 209715200 bytes

Redo Buffers 6397952 bytes

Lets now try to create a tablespace with 4K block size.

SQL> create tablespace test_tbs4k datafile ‘/dy/oracle/product/db10g/dbf/test_tbs4k_1.dbf’ size 100M blocksize 4K;

create tablespace test_tbs4k datafile ‘/dy/oracle/product/db10g/dbf/test_tbs4k_1.dbf’ size 100M blocksize 4K

ERROR at line 1:

ORA-29339: tablespace block size 4096 does not match configured block sizes

Oracle in not intelligent enough to translate the block size of 4k into 8k buffer size. Because it cannot load the content of this datafile created for this tablespace having block size of 4k into buffer buffer of 8k.

So we need to create a buffer of 4K block size, after that only we can create a tablespace for 4k block size.

If we see the parameter db_4k_cache_size is not set.

SQL> show parameters db_4k_cache_size

NAME TYPE VALUE

———————————— ———– ——————————

db_4k_cache_size big integer 0

SQL> alter system set db_4k_cache_size = 100M;

System altered.

SQL> show parameter db_4k_cache_size;

NAME TYPE VALUE

———————————— ———– ——————————

db_4k_cache_size big integer 100M