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>
No comments:
Post a Comment