DB_BLOCK_SIZE and DB_CACHE_SIZE in Oracle

season0891發表於2015-02-27
DB_BLOCK_SIZE Parameter 

The
DB_BLOCK_SIZE is used to specify the standard block size which is used for the system and temporary tablespaces. 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.

DB_CACHE_SIZE Parameter 


The DB_CACHE_SIZE  parameter, rather than DB_BLOCK_BUFFERS is used to define the size of the buffer cache for the standard block size. This can be set to any size, but it will be rounded to the nearest whole granule. 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.


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


DB_2K_CACHE_SIZE  = 2k
DB_4K_CACHE_SIZE  = 4k
DB_8K_CACHE_SIZE  = 8k
DB_16K_CACHE_SIZE = 16k
DB_32K_CACHE_SIZE = 32k


Database buffer cache size = DB_BLOCK_SIZE x DB_BLOCK_BUFFERS


Example:
We have 200M set for DB_CACHE_SIZE and db_keep_cache_size and db_recycle_cache_size is not set

SQL> Show parameters db_cache_size

SQL> Show parameter db_keep_cache_size

SQL> Show parameter db_recycle_cache_size

SQL> Show sga


SQL> Create tablespace test_tbs2k datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\test2'
size 100M blocksize 4k;


Output:

CACHE1.png


We will use block size 4k in these queries and we will change database buffer size.

SQL> Show parameters db_4k_cache_size


SQL> Alter system set db_4k_cache_size = 100M;


SQL> Show parameter db_4k_cache_size;


SQL> Show parameter db_cache_size


SQL> Alter system set db_cache_size= 500M;


SQL> Show sga


Output:
 
CACHE2.png

We can get the information about our database buffer cache from a view v$buffer_pool.

SQL> Select name, block_size, current_size from v$buffer_pool;

Output:

CACHE3.png

I hope this article help you.
http://www.dbtalks.com/uploadfile/anjudidi/db_block_size-and-db_cache_size-in-oracle/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/90618/viewspace-1443064/,如需轉載,請註明出處,否則將追究法律責任。

相關文章