Shulou(Shulou.com)06/01 Report--

Test of db_16k_cache_size setting in Oracle

Reference: blog.chinaunix.net/uid-23622436-id-3234873.html

Official Document: docs.oracle.com/cd/E11882_01/server.112/e40402/initparams046.htm#REFRN10027

To create a non-standard block table space, you need to set the db_nk_cache_size value before creating a table space.

The default db block size is 8k, but if you want to use block sizes of 2k,4k, 16k, 32k, you need to set db_nk_cache_size, where n is 2, 4, 16, or 32.

1. Take creating a 16K table space block as an example, db_16k_cache_size is initially 0, and creating a table space fails.

SYS@PROD3> show parameter block

NAME TYPE VALUE

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

db_block_buffers integer 0

db_block_checking string FALSE

db_block_checksum string TYPICAL

db_block_size integer 8192

db_file_multiblock_read_count integer 128

SYS@PROD3> show parameter 16k

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

SYS@PROD3> show parameter db_create_file_dest

NAME TYPE VALUE

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

db_create_file_dest string /u01/app/oracle/oradata/PROD3

SYS@PROD3> create tablespace tb16 datafile size 10m blocksize 16k;

create tablespace tb16 datafile size 10m blocksize 16k

*

ERROR at line 1:

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

2. After modifying the db_16k_cache_size parameter value, the table space is successfully created;

SYS@PROD3> alter system set db_16k_cache_size=20m;

SYS@PROD3> create tablespace tb16 datafile size 10m blocksize 16k;

SYS@PROD3> create table table16 tablespace tb16 as select * from dba_objects ;

3. Set the db_16k_cache_size to 0 again, and the data insertion fails.

SYS@PROD3> alter system set db_16k_cache_size=0;

SYS@PROD3> show parameter 16

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

SYS@PROD3> insert into table16 select * from dba_objects ;

insert into table16 select * from dba_objects

*

ERROR at line 1:

ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K

4. The default value of db_16k_cache_size parameter is 0. After setting, the minimum value is the number of system CPUs *4M, and the maximum value depends on the db_buffer value.

SYS@PROD3> alter system set db_16k_cache_size=1m;

SYS@PROD3> show parameter 16k

NAME TYPE VALUE

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

db_16k_cache_size big integer 8M

Description of this parameter in official documentation;

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams046.htm#REFRN10027

DB_nK_CACHE_SIZE

Property Description

Parameter type Big integer

Syntax DB_[2 | 4 | 8 | 16 | 32]K_CACHE_SIZE = integer [K | M | G]

Default value 0 (additional block size caches are not configured by default)

Modifiable ALTER SYSTEM

Range of values Minimum: 0 (values greater than zero are automatically modified to be either the user-specified size rounded up to the granule size or 4 MB * number of CPUs, whichever is greater)

Maximum: operating system-dependent

Basic No

DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, then it is illegal to specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE).

Do not set this parameter to zero if there are any online tablespaces with an nK block size.

Operating system-specific block size restrictions apply. For example, you cannot set DB_32K_CACHE_SIZE if the operating system's maximum block size is less than 32 KB. Also, you cannot set DB_2K_CACHE_SIZE if the minimum block size is greater than 2 KB.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.