Environmental Cost Factors | Optimizer Process | Teradata Vantage - 17.10 - Environmental Cost Factors - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

About Environmental Cost Factors

Vantage makes a substantial store of system configuration and performance data, referred to as environmental cost factors, available to the Optimizer so it can tune its plans appropriately for each individual system or cloud environment. This environmental cost data is what enables the Optimizer to operate fully and natively in parallel mode.

Types of Cost Factors

The basic types of environmental cost factors are defined in the following table:

Cost Factor Type Definition
External cost parameters External cost parameters are culled from various system tables and files at system startup. They consist of various hardware and configuration details about the system.
Performance constants Performance constants specify the data transfer rates for each type of storage medium and network interconnection supported by Teradata. The values of these constants can be statically modified to reflect new hardware configurations when necessary.

External Cost Parameters

External cost parameters are various families of weights and measures, including the parameters listed in the following table:

External Cost Parameter Group Definition
Optimizer weights and scales Weightings of CPU, disk, and network contributions to optimizing a request plus scaling factors to normalize disk array and instruction path length contributions.

Unitless decimal weighting factors.

CPU cost variables CPU costs for accessing, sorting, or building rows using various methods.
Disk delay definitions Elapsed times for various disk I/O operations.

Measured in milliseconds.

Disk array throughput Throughputs for disk array I/O operations.

Measured in I/Os per second.

Network cost variables Message distribution and duplication costs and overheads.

Measured in milliseconds.

Optimizer environment variables Hardware configuration information such as the number of CPUs and vprocs per node, maxima, minima, and average numbers of MIPS per CPU, nodes per clique, disk arrays per clique, and so on.

Unitless decimal values.

Miscellaneous cost parameters DBS Control information such as free space percentages, maximum number of parse tree segments, dictionary cache size, and so on.

Measured in various units, depending on the individual cost parameter.

Performance constants Various values used to assist the Optimizer to determine the best method of processing a given query.

Measured in various units, depending on the individual performance constant.

PDE system control files Lists various system control files used by PDE. Used to initialize the appropriate GDOs with the appropriate values at startup.
DBS control files Lists various database control files found in the DBS Control record. Used to initialize the appropriate GDOs with their respective appropriate values at startup.
TPA subsystem startup initialization Initializes or recalculates various Optimizer parameters, including the Optimizer target table GDO used by the target level emulation software. For more information, see Teradata Vantageā„¢ - Database Administration, B035-1093.