Different Block Size of ORACLE
Oracle 9i supports tablespaces with different block sizes. This allows large tables and indexes to have a larger block size than smaller objects. It also means that tablespaces can be transported between databases with differing block sizes. The database is created with a standard block size and up to 5 none-standard block sizes.
The DB_BLOCK_SIZE is used to specify the standard block size which is used for the SYSTEM and TEMPORARY tablespaces. All subsequent tablespaces will also be assigned this block size unless specified otherwise.
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.
If the total SGA is less then 128M the granule size is 4M, greater than 128M and it becomes 16M.
The DB_CACHE_SIZE must be at least 1 granule in size and defaults to 48M.
An individual buffer cache must be defined for each non-standard block size used.
The list of five block sizes in Oracle :
2k,4k,8k,16k,32k .
The parameters to set different block sizes :
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
To check parameter values we have show command :
|
1 2 3 4 5 6 7 8 9 |
SYS>>show parameter db_2k NAME TYPE VALUE ---------------- ----------- ------------------------------ db_2k_cache_size big integer 0 SYS>>show parameter db_4k NAME TYPE VALUE --------------- ---------- ------------------------------ db_4k_cache_size big integer 0 |
To check default block size value
|
1 2 3 4 5 6 7 |
SYS>> show parameter db_block_size NAME TYPE VALUE ------------------- ----------- ------------ db_block_buffers integer 0 db_block_checking string FALSE db_block_checksum string TYPICAL db_block_size integer 8192 |
Now the following example shows whole the process of setting up a 2K buffer cache
|
1 2 |
SQL> ALTER SYSTEM SET DB_2K_CACHE_SIZE=16M SCOPE=SPFILE; System altered. |
We need to bounce the database .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 787968 bytes Variable Size 78641664 bytes Database Buffers 88080384 bytes Redo Buffers 262144 bytes Database mounted. Database opened. |
Now check the parameter of 2k
|
1 2 3 4 |
SYS>>show parameter db_2k NAME TYPE VALUE ---------------- ----------- ------------------------------ db_2k_cache_size big integer 16M |
Now create tablespace with 2k block size tablespace.
|
1 2 |
SYS>>create tablespace ts_2k datafile '/u01/oradata/primary/ts_2k.dbf' size 10m blocksize 2k; Tablespace created. |
Use DBA_TABLESPACES dictionary view to check blocksize of tablespaces.
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS>>select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces; TS-Name Block-Size ---------- ---------- SYSTEM 8192 SYSAUX 8192 UNDOTBS1 8192 TEMP 8192 USERDATA 8192 TEST 8192 TS_2K 2048 |
In the same way we can change the parameters for 4K,16K,32K block sizes ..
Thank you …
Loading...
