Oracle9i New Feature Series: Using Multiple Block Sizes in a Database

This is one of the most exciting new features for the DBA, especially now a day there are many databases where you can hardly distinguish between OLTP and DSS….

In the pre-9i releases, you define the BLOCK_SIZE when creating the database and it cannot be changed. In 9i also this is true. In addition to the standard block size of the database, you can create tablespaces with different block size. The block size of the tablespace is specified using the BLOCK SIZE clause of CREATE TABLESPACE.

For you to use this feature, you need to set the right buffer cache parameter. The DB_CACHE_SIZE specifies the buffer cache size for the objects in tablespaces created with the standard block size. DB_nK_CACHE_SIZE parameter sets the appropriate buffer cache for the non-standard block sized tablespace. ‘n’ could be 2, 4, 8, 16 or 32 but it should not be equal to your standard block size. The default values for DB_nK_CACHE_SIZE parameters are 0.

SQL> show parameter db%cache

 

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_advice                      string      OFF

db_cache_size                        big integer 0

db_keep_cache_size                   big integer 0

db_recycle_cache_size                big integer 0

SQL> show parameter db_block

 

NAME                                 TYPE        VALUE

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

db_block_buffers                     integer     1500

db_block_checking                    boolean     FALSE

db_block_checksum                    boolean     TRUE

db_block_size                        integer     8192

SQL>

Look at the above parameters; since the database was upgraded from 8i, it is still using the old style buffer cache sizing using DB_BLOCK_BUFFERS. Here we cannot set any of the DB_nK_CACHE_SIZE parameter because you have not used the DB_CACHE_SIZE parameter to start the database.

SQL> alter system set db_4k_cache_size=20;

alter system set db_4k_cache_size=20

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00381: cannot use both new and old parameters for buffer cache size

specification

 

SQL>

Let’s try another database:

SQL> show parameter db%cache

 

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_advice                      string      ON

db_cache_size                        big integer 83886080

db_keep_cache_size                   big integer 0

db_recycle_cache_size                big integer 0

SQL> show parameter sga

 

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 220163984

SQL>

SQL> alter system set db_cache_size=20m;

 

System altered.

 

SQL> alter system set db_4k_cache_size=2M;

 

System altered.

 

SQL> show parameter db%cache

 

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 16777216

db_8k_cache_size                     big integer 0

db_cache_advice                      string      ON

db_cache_size                        big integer 33554432

db_keep_cache_size                   big integer 0

db_recycle_cache_size                big integer 0

SQL>

SQL>

SQL> create tablespace test_biju datafile

  2  '/ora_backup/test_biju.dbf' size 50m

  3  extent management local

  4* blocksize 4k

SQL> /

 

Tablespace created.

 

SQL>

SQL> select block_size from dba_tablespaces

  2  where tablespace_name = 'TEST_BIJU';

 

BLOCK_SIZE

----------

      4096

 

1 row selected.

 

SQL> drop tablespace test_biju including contents and datafiles;

 

Tablespace dropped.

 

SQL>