8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multiple Block Sizes
Oracle 9i supports tablespaces with different block sizes. This allows large tables and indexes to have a larger block size than smaller objects. This is especially useful in hybrid databases where DSS transactions benefit from large block sizes, whilst OLTP operations are best suited to smaller block sizes. 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. These are set up with the following parameters which default to a size of 0M.
DB_2K_CACHE_SIZE = 0M DB_4K_CACHE_SIZE = 0M DB_8K_CACHE_SIZE = 0M DB_16K_CACHE_SIZE = 0M DB_32K_CACHE_SIZE = 0M
Each cache that is used must have at least 1 granule assigned to it. The instance must be restarted
before changes to these parameters take effect. The DB_nK_CACHE_SIZE
parameters can only be set to
zero if there are no online tablespace with a nK block size.
Once the caches are defined the tablespaces can be defined.
CREATE TABLESPACE <tablespace-name> BLOCKSIZE 2048;
The following example shows whole the process of setting up a 2K buffer cache and defining a tablespace to use it.
SQL> CONN sys/password AS SYSDBA Connected. SQL> ALTER SYSTEM SET DB_2K_CACHE_SIZE=16M SCOPE=SPFILE; System altered. 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. SQL> CREATE TABLESPACE test_2k_ts DATAFILE '/u01/oradata/TSH1/test_2k_ts01.dbf' SIZE 1M BLOCKSIZE 2048; Tablespace created. SQL> CREATE TABLE scott.test_2k_tab ( 2 id NUMBER 3 ) 4 TABLESPACE test_2k_ts; Table created. SQL>
The KEEP and RECYCLE buffer caches can only be defined for the standard block size. In 8i these were
defined using the BUFFER_POOL_KEEP
and BUFFER_POOL_RECYCLE
parameters, with their memory
was taken from the total DB_BLOCK_BUFFERS
. In 9i the parameters have been changed to DB_KEEP_CACHE_SIZE
and DB_RECYCLE_CACHE_SIZE
, with their memory allocations being totally separate to the main buffer cache.
A number of rules apply to the use of variable block sizes:
- All partitions of a partitioned object must reside in tablespaces with the same block size.
- All temporary tablespaces must be of the standard block size.
- Index Organized Table Overflow and out-of-line LOB segments can be stored in a tablespace with a different block size that that of the base table.
Hope this helps. Regards Tim...