DWHPro Insights

Maximizing Performance with Teradata Dynamic AMP Sampling: An Introduction

By Roland Wenzlofsky ·

Maximizing Performance with Teradata Dynamic AMP Sampling: An Introduction

Introduction to Teradata Dynamic AMP Sampling

Dynamic AMP sampling supplies selected demographic estimates when optimizing. Do not include USI distinctness as though it needs sampling: uniqueness supplies that information. These samples do not replace full column histograms.

A random AMP is selected for each table based on the row hash of its internal TABLE_ID, using a well-known hashing algorithm.

CALCULATE ROWHASH(TABLE_ID) -> HASHMAP(ROWHASH) -> AMP

The determination of key figures must be restricted to quickly create dynamic AMP samples during execution plan generation when they are not already present.

Dynamic AMP sampling is initially limited to one AMP, but it can be altered. The system settings allow for selecting 2, 5, or all AMPs within a node or all AMPs within all nodes for sampling.

The “RandomAmpSampling” field can be modified in the DBC control area. The available options include:

D…use the default 1 AMP sampling
L.. use 2 AMPs for sampling
M…use 5 AMPs for sampling
N…use all AMPs belonging to one node for sampling
A…use all available AMPs in the system for sampling

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

More sampling AMPs mean increased time for statistical creation, which can hinder the speed of tactical queries. However, this is generally not a concern for strategic workloads. When tables are skewed, multiple sampling AMPs should be utilized to give the Optimizer a more accurate representation of the data demographics.

All-AMP sampling can reduce sampling error on skewed data, but does not create full collected-column histograms. Choose sampling settings using documented release behavior and workload evidence.

Teradata always generates dynamic AMP samples for indexes to detect stale statistics, even if statistics have already been collected. The process of Dynamic AMP Sampling is as follows:

  • If the table header is unavailable in the FSG cache, a dynamic AMP sample of the index is taken and distributed to all AMPs.
  • The related DBC tables are checked to see if collected statistics are available
  • If no collected statistics are available, the dynamic AMP sample is used for estimations
  • The dynamic AMP sample is compared against the collected statistics if the collected statistics are available.
  • The optimizer may compare sampled demographics, collected statistics, history and usage counts. It does not universally discard stale histograms in favor of samples whenever a single threshold is crossed.
Find out what Object Use Counts are and how they support statistics

The Sampling Process for PI, USI, NUSI

These steps may be executed simultaneously by one or multiple AMPs based on their configurations.

  • Each sampling AMP reads 1 – 2 cylinders into the FSG cache from the disk.
    Usually, it’s two cylinders (the first and the last belonging to the table). But if the table fits entirely into one cylinder, only one must be read.
  • The average number of rows of the cylinder’s data blocks is calculated
  • The average number of rows is multiplied by the number of data blocks
  • The above result is multiplied by the number of cylinders belonging to the table
  • The above result is multiplied by the number of AMPs available in the entire system

The calculations above estimate the row count of the table.

Teradata employs a comparable method for conducting random AMP sampling for a NUSI.

  • Each sampling AMP reads 1 – 2 cylinders from the disk into the FSG cache.
  • The number of NUSI values in the cylinder(s) is counted.
  • The table row count is divided by the NUSI row number calculated in the previous step; the result is the rows per NUSI value.

The Teradata Optimizer can utilize a random AMP sample of a NUSI to determine whether to perform a full table scan on the base table or the NUSI.

Teradata does not use random AMP sampling for a Unique Secondary Index (USI). The Optimizer assumes that the number of distinct values in the USI equals the number of rows in the table.

Dynamic AMP Sampling cannot replace collected statistics.

Validation scope: Sampling defaults, cylinder algorithms and extrapolation across table maps are historical descriptions; no DBSControl changes or physical sampling inspection were performed.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam