17.10 - PermDBAllocUnit - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Specifies the size of the storage allocation unit used for data blocks in permanent tables.

Field Group

File System

Valid Range

1 to 127 sectors

A sector is 512 bytes.

On cliques with 4 KB aligned devices, the setting is rounded up to the next 8-sector multiple.

Default

1 sector

Changes Take Effect

After the DBS Control Record has been written.

PermDBAllocUnit and System Performance

As tables are modified, rows are added, deleted, and changed. Data blocks grow and shrink dynamically to accommodate their current contents. However, data block sizes can change only in units of PermDBAllocUnit. This means there will nearly always be some unused space left at the end of the data block. If table modifications are relatively even, such incremental changes in data block size result in an average of approximately half an allocation unit of space wasted for every data block. (This is a rough approximation, and will depend on many factors that differ from database to database.)

In environments where new rows are added frequently to tables, or where tables with variable length rows are frequently growing, system performance might be improved slightly by increasing the allocation unit size. With a larger allocation unit, data blocks will not need to be enlarged as frequently, because there will already be room for additional changes. However, in environments where new rows are not added frequently, the additional space in each block can degrade performance by increasing the average I/O size.

Make only small changes to this setting at a time, and carefully evaluate the results before committing the change on a production system. Set the allocation unit to a multiple of the average row size of tables that change frequently, rounded up to the nearest sector.

Because the benefit of larger allocation units is often offset by the consequent increase in average wasted space, Teradata recommends that PermDBAllocUnit be left at the default setting.

Maximum Multirow Data Block Size

The PermDBAllocUnit and PermDBSize fields together determine the maximum size of multirow data blocks. Because data blocks can grow only in steps (allocation units) defined by PermDBAllocUnit, the size of a data block at any time will always be an integer multiple of PermDBAllocUnit, regardless of the PermDBSize setting.

Consequently, if the PermDBAllocUnit setting is not an integer factor of PermDBSize, then the largest multirow data blocks will be smaller than PermDBSize. For example, if PermDBAllocUnit is set to 4 sectors, even if PermDBSize is set to 255, the largest multirow data blocks can be only 252 sectors (the greatest multiple of 4 that is less than or equal to 255). Similarly, if PermDBAllocUnit is set to 16, the largest multirow data blocks can be only 240 sectors.