DWHPro Insights

Teradata Skewing: Causes, Detection, and Solutions

By Roland Wenzlofsky ·

Teradata Skewing: Causes, Detection, and Solutions

Introduction

As a tuner, most slow queries can be traced back to two primary causes. The first cause is the transfer of substantial amounts of data between AMPs. This piece will focus on the second cause: Teradata skewing.

Teradata refers to skew as the uneven distribution of a table’s rows across the AMPs. This results in suboptimal utilization of the system’s parallelism and affects queries and data modifications (UPDATE, INSERT, DELETE). Skew causes uneven CPU and IO utilization, with AMPs having more rows experiencing longer processing times and becoming a bottleneck. The query completion time depends on the slowest AMP to finish its work.

Skew may result in numerous duplicate row checks when inserting rows with the same primary index values into a NUPI table defined as a SET table. This could also increase CPU usage for the affected AMP(s) in identifying duplicate rows.

Multiple factors can cause skewness, which we will address comprehensively in this article.

Poorly modeled physical data is a frequent cause of skew. A crucial aspect of performance tuning is the appropriate selection of primary indexes. The physical data model is fundamental to all workloads, and significant performance improvements can often be attained by correctly designing it. The data model is advantageous because it changes only gradually over time once it has been correctly created.

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

Here is a sample query to compute the skewness of a table:

SELECT
TABLENAME,
SUM(CURRENTPERM) CURRENTPERM,
CAST((100-(AVG(CURRENTPERM)/NULLIFZERO(MAX(CURRENTPERM))*100)) AS DECIMAL(5,2)) AS SKEWFACTOR_PERCENT
FROM DBC.TABLESIZE
WHERE DATABASENAME = 'the_database'
GROUP BY 1
ORDER BY 1
;

This query measures space skew, not row-count skew. Compression, row width, empty AMPs and table maps affect interpretation. There is no universal acceptable percentage; assess the bottleneck and workload, especially for small tables.

Detect Teradata Skew by analyzing the Joins

The skew of tables is commonly known as static skew, which can be identified by simply examining the table distribution. On the other hand, dynamic skew is harder to detect as it occurs during the execution of the execution plan and mostly impacts spool tables. The primary culprits for dynamic skew are joins and aggregations.

Rows participating in an AMP-local join must meet on the same AMP, but the base tables need not have identical primary indexes. Redistribution or duplication can make rows local for a join. Redistributing a large table on a low-cardinality join key can create skew; accurate statistics help the optimizer choose alternatives.

SELECT * FROM
CUSTOMER t01
INNER JOIN
GENDER_LOOKUP t02
ON
t01.GENDER = t02.GENDER;

If CUSTOMER is redistributed by low-cardinality GENDER, many customer rows concentrate on a few AMPs. Duplicating a small GENDER_LOOKUP to the participating AMPs can instead preserve CUSTOMER’s existing distribution. Select between these plans using row counts, value frequencies and EXPLAIN.

To identify an issue promptly, observing the query through Viewpoint is best. A Plan Step that experiences prolonged execution time and high skewness indicates an issue. Although one can identify dynamic skew by studying the execution plan, monitoring through Viewpoint is a quicker approach.

Detect Teradata Skewing by analyzing Column Values

The hidden skew is a remarkable instance of skew that is difficult to detect as it is typically not visible in Viewpoint.

Hidden skew is a problem wherein a table is mostly evenly distributed, but certain values are clustered together, making the skew difficult to detect. For instance, consider a transaction table where one customer has numerous transactions while all others are walk-in customers. A query can be used to uncover such hidden skew:

SELECT the_snaky_column,COUNT(*) FROM the_table GROUP BY 1 ORDER BY 2 DESC;

A hidden skew is present when the Result Set’s top values correspond to the primary index’s most common values.

‘A’,1000.000
‘B’,500
‘C’,480
‘D’,’480′
…

‘ZZZZZ’,1

Real-time monitoring through Viewpoint can detect plan steps displaying skewness. Skew problems arise when a step remains “stuck” for an extended period with a high skew factor. Short steps exhibiting skewness can be disregarded as Viewpoint provides snapshots. At the query’s completion, releasing a spool triggers a snapshot revealing any skewness.

Repeated single-column NULL keys may concentrate on one AMP, but that AMP is not universally the highest numbered. Composite keys and the table map change placement. Inspect the actual join-key distribution and map before attributing an AMP hotspot to NULL.

Prior to Teradata Release 14, splitting the query into two was the sole method to avoid skew, segregating the skewed value from the well-distributed values. However, with the new features provided by Teradata, this is no longer necessary, as the system can handle it internally.

A UNION ALL rewrite needs the same explicit column projection in every branch and a declared policy for unmatched and NULL-key rows. The former SELECT * example had incompatible column counts and changed join semantics; it is removed here. Retain the original join until a semantically equivalent rewrite is defined and verified.

Define the required output columns and outer-join semantics before constructing separate NULL and non-NULL branches.

Teradata employs Partial Redistribution/Partial Duplication (PRPD) as its join feature, which divides joins into several segments. Recognizing that these new features require precise and current statistics is crucial.

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