Configure Multiple Block Sizes tablespace in Oracle

1. parameter description

Generally, the default value of db_block_size is 8 KB. From this perspective, the block size of the database is the same. However, Oracle can configure Multiple Block Sizes.

 

SYS @ anqing1 (rac1)> show parameter cache_size

NAME TYPE VALUE

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

Db_16k_cache_size big integer 0

Db_2k_cache_size big integer 0

Db_32k_cache_size big integer 0

Db_4k_cache_size big integer 0

Db_8k_cache_size big integer 0

Db_cache_size big integer 0

Db_keep_cache_size big integer 0

Db_recycle_cache_size big integer 0

SYS @ anqing1 (rac1)> show parameter db_block_size

 

NAME TYPE VALUE

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

Db_block_size integer 8192

 

If you want to configure Multiple Block Sizes, You need to configure the db_nK_cache_size parameters. These parameters are set to 0 by default. After these parameters are configured, you can specify the corresponding block when creating the relevant tablespace. This completes the Multiple Block Sizes. However, the Multiple Block Sizes provides a certain degree of complexity for the management proxy, so there is not much practical use.

 

Note:

When creating a non-db_block_size standard tablespace, you must first execute the corresponding DB_nK_CACHE_SIZE parameter to create the nK buffer. Otherwise, the following message will be reported:

ORA-29339: tablespace blocks size 4096 does not matchconfigured block sizes

 

1.1 DB_BLOCK_SIZE

Property

Description

Parameter type

Integer

Default value

8192

Modifiable

No

Range of values

2048 (2 k) to 32768 (32 k), but your operating system may have a narrower range

Basic

Yes

Oracle RAC

You must set this parameter for every instance, and multiple instances must have the same value.

 

Caution:

Set this parameter at the time of database creation. Do notalter it afterward.

DB_BLOCK_SIZEspecifies (in bytes) the size of Oracle database blocks. Typical values are4096 and 8192. The value of this parameter must be a multiple of the physicalblock size at the device level.

Thevalue for DB_BLOCK_SIZE in effect at the time you create the databasedetermines the size of the blocks. The value must remain set to its initialvalue.

ForReal Application Clusters, this parameter affects the maximum value of theFREELISTS storage parameter for tables and indexes. oracle uses one databaseblock for each freelist group. demo-support system (DSS) and data warehousedatabase environments tend to benefit from larger block size values.

 

Note:

(1) 32-bit operating systems support a maximum DB_BLOCK_SIZE value of16384 (16 k) -- 32-bit system, db_block_size up to 16 k

(2) 64-bit operating systems support a maximum DB_BLOCK_SIZE value of32768 (32 k) -- 64-bit system, db_block_size up to 32 k

 

1.2 DB_nK_CACHE_SIZE Parameter

The official website is described as follows:

From: http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams044.htm#REFRN10027

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 granule size * number of processor groups, 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 thesize of the cache for the nK buffers. you can set this parameter onlywhen DB_BLOCK_SIZE has a value other than nK. for example, ifDB_BLOCK_SIZE = 4096, then it is illegal to specify the parameterDB_4K_CACHE_SIZE (because the size for the 4 KB block cache is alreadyspecified by DB_CACHE_SIZE ).

The -- DB_nK_CACHE_SIZE parameter specifies the nK buffer. When specifying the cache, make sure there is sufficient space. It cannot conflict with the DB_BLOCK_SIZE parameter. n can be 2, 4, 8, 16, and 32.

Donot set this parameter to zero if there are any online tablespaces with an nKblock size.

Operating system-specific block size restrictions apply. for example, you cannot set DB_32K_CACHE_SIZE if the operating system's maximumblock size is less than 32 KB. also, you cannot set DB_2K_CACHE_SIZE if theminimum block size is greater than 2 KB.

-- DB_nK_CACHE_SIZE must be supported by the operating system. If n = 32, but the operating system does not support it.

 

See Also:

Youroperating system-specific Oracle documentation for more information on blocksize restrictions

 

 

Ii. Test

-- View db_block_size

SYS @ anqing2 (rac2)> show parameterdb_block_size

 

NAME TYPE VALUE

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

Db_block_size integer 8192

 

 

-- View the number of operating system digits

[Root @ rac2 ~] # Cat/proc/cpuinfo | grepflags | grep 'lm '| wc-l

1

If the result is greater than 0, 64bit computing is supported. lm indicates long mode, and lm indicates 64bit.

 

For more information about CPU, refer:

View Linux CPU Information

Http://blog.csdn.net/tianlesoftware/article/details/5970500

 

-- View the value of db_32k_cache_size

SYS @ anqing2 (rac2)> show parameterdb_32k_cache_size

 

NAME TYPE VALUE

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

Db_32k_cache_size big integer 0

 

-- If 32 KB is specified, an error is returned, indicating that the system is not supported. It seems that only 16 KB can be used.

SYS @ anqing1 (rac1)> alter system setdb_32k_cache_size = 1 M scope = both sid = 'anqing1 ';

Alter system set db_32k_cache_size = 1 Mscope = both sid = 'anqing1'

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00382: 32768 not a valid block size, valid range [..]

 

-- Specify 16 k cachebuffer

SYS @ anqing1 (rac1)> alter system set db_16k_cache_size = 1 M scope = bothsid = 'anqing1 ';

System altered.

SYS @ anqing2 (rac2)> alter system set db_16k_cache_size = 1 M scope = bothsid = 'anqing2 ';

System altered.

-- Because my test environment is RAC, parameters are specified on both nodes.

 

-- Check the cache. Although 1 M is specified, the system uses the default minimum value.

SYS @ anqing1 (rac1)> show parameter db_16k_cache_size

NAME TYPE VALUE

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

Db_16k_cache_size big integer 4 M

SYS @ anqing2 (rac2)> show parameter db_16k_cache_size

NAME TYPE VALUE

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

Db_16k_cache_size big integer 4 M

-- The minimum default value is explained on the previous official website:

-- Values greater thanzero are automatically modified to be either the granule size * number ofprocessor groups, or 4 MB * number of CPUs, whichever is greater

 

-- If 16 kcache is specified, we can create a tablespace with a block of 16 K.

 

SYS @ anqing1 (rac1)> CREATE TABLESPACETest DATAFILE

2 '+ DATA/anqing/datafile/test01.dbf' SIZE 50 m autoextend on next 10 MMAXSIZE 100 m blocksize 16 K;

Tablespace created.

-- Note that the block size is 16 k, which corresponds to the Cache size we created earlier.

 

-- To verify that an error is returned when no cache is specified, we create a 4 K tablespace.

SYS @ anqing1 (rac1)> create tablespace ttdatafile '+ DATA/anqing/datafile/tt01.dbf' SIZE 50 m autoextend on next 10 MMAXSIZE 100 m blocksize 4 K;

Create tablespace tt datafile '+ DATA/anqing/datafile/tt01.dbf' SIZE 50 m autoextend on next 10 m maxsize 100 MBLOCKSIZE 4 K

*

ERROR at line 1:

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

-- Here we are prompted: ORA-29339 error, so you must specify the corresponding Cache buffer before creating Multiple Block Sizes.

 

-- Create a test table and put it in the 16 K test tablespace

SYS @ anqing1 (rac1)> create table tt16k tablespace test as select * from dba_objects;

Table created.

 

-- Verify the size of the tt16k block in the table

SYS @ anqing1 (rac1)> selectsegment_name, blocks from dba_segments where segment_name = 'tt16k ';

SEGMENT_NAME BLOCKS

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

Tt16 K 384

 

-- When each block in a table is larger, it can store more blocks.

-- We use an 8 K block to create an identical table and view it using blocks for comparison.

 

SYS @ anqing1 (rac1)> create table tt8k as select * from dba_objects;

Table created.

SYS @ anqing1 (rac1)> select segment_name, blocks from dba_segments wheresegment_name = 'tt8k ';

SEGMENT_NAME BLOCKS

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

768 TT8K

-- We can see that 16 K is 384 blocks and 8 K is 768 blocks.

 

-- Move tt8kto test (16 k)

SYS @ anqing1 (rac1)> alter table tt8k move tablespace test;

Table altered.

SYS @ anqing1 (rac1)> select segment_name, blocks from dba_segments wheresegment_name = 'tt8k ';

 

SEGMENT_NAME BLOCKS

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

384 TT8K

 

The Blocks used is reduced.