15.00 - DEFRAGMENT - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

DEFRAGMENT

Purpose  

Note: Because of the difficulty in determining how severely a cylinder is fragmented, only trained personnel should use this command.

The DEFRAGMENT command causes free sectors on each qualifying logical cylinder contained within the current scope to be combined together into a single contiguous block of free space on that cylinder. The scope used is designated by the SCOPE command or the default if no SCOPE command was issued.

Syntax  

 

Syntax element …

Specifies …

FORCE

to defragment every cylinder whether a cylinder meets the criteria specified in Usage Notes below.

/Y

to bypass the confirmation prompt that asks if you are sure you want to run this command.

The Ferret ABORT command can be used to halt this operation during command execution. After it is initiated, ABORT stops the defragmenting and reports the current status of the vprocs.

Note: Up to 30 seconds can elapse before the ABORT operation begins.

Usage Notes

A cylinder becomes fragmented as a result of modifications to table data. As data blocks (DBs) are created, deleted, or change size as a result of these modifications, what started out as a small number of large free spaces on the newly allocated cylinder becomes a large number of small free spaces. Although there may still be a large total number of free sectors on the cylinder, with time there are fewer ranges of contiguous free sectors that are large enough to store new DBs. When new DBs cannot be stored on the cylinder, new free cylinders must be found and allocated.

The defragmentation process moves the data blocks closer together, coalescing the free space into areas that are large enough that new DBs can be allocated on the cylinder. This can reduce the need to allocate free cylinders for new data storage. The defragmentation process actually moves DBs from the fragmented cylinder to a new cylinder, where the DBs can be packed together, leaving a single, large set of consecutive free sectors. The cylinder from which the DBs were moved is freed, and made available for new allocations.

Consequently, defragmentation is a costly operation, so should be performed only when system resources are plentiful.

Unless the FORCE option is used, the DEFRAGMENT command performs defragmentation of a logical cylinder within the scope only if all of the following criteria are met.

  • More than one set of free sectors is on the cylinder, so there is something that could be combined.
  • 25% or more free sectors are on the cylinder.
  • The average number of sectors comprising a set of free sectors is less than the average size of a data block on that cylinder.
  • If the FORCE option is used, the DEFRAGMENT command performs defragment of a logical cylinder within the scope if it has more than one block of free space. In that case, it will defragment a cylinder regardless of the percentage of free sectors or average block sizes.

    Note that the DEFRAGMENT operation does not change the size or content of any data blocks. Existing blocks may be moved, but they are otherwise unchanged.

    For each AMP, the Ferret indicates how many cylinders it will attempt to defragment. For example:

    vproc  2  response
    Requested Cylinder Range had 197 cylinders placed on the defrag list

    Upon completion, for each amp, the actual number of cylinders defragmented will be logged into the system's message log. For example:

    002507 14:47:07 043100d6 ... 44 8 
    340516600|appl|1|S|I|U|0|0|M|0|0|PDE|0|0|0|1#PDElogd: Event number 34-05166-00
    (severity 10, category 10)
    5166: Defragment of cylinder[s] occurred.

    On Wed Jun 20 14:47:07 2007 on NODE 001-01, VPROC 2, partition 9, task
    fsubackgrnd
    197 cylinder[s] defragmented

    Defragmentation proceeds as a background task, and can continue to run even after the Ferret prompt returns. To determine when defragmentation has completed, check DBC.SW_Event_Log for event_tag=34-05166-00.

    Teradata Database can isolate some file system errors to a specific data or index subtable, or to a contiguous range of rows (“region”) in a data or index subtable. In these cases, Teradata Database marks only the affected subtable or region down. This improves system performance and availability by allowing transactions that do not require access to the down subtable or rows to proceed, without causing a database crash that would require a system restart.

    If DEFRAGMENT encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.

    Example  

    The following example shows the output that DEFRAGMENT generates:

    Ferret ==>
    defrag /y
    Defrag has been sent to all AMP vprocs in the SCOPE.
    Type ‘ABORT’ to stop them before completion
     
    vproc 0 response
    Requested Cylinder Range had 5 cylinders placed on the defrag list
     
    vproc 1 response
    Requested Cylinder Range had 9 cylinders placed on the defrag list