8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

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:

Hope this helps. Regards Tim...

Back to the Top.