When Teradata tables are not distributed properly this can cause significant performance problems. Storage space is also not utilized properly. Therefore, identifying and monitoring tables that are not distributed properly across AMPs is very important when working with Teradata databases. The following query will reveal the skew factor of tables in Teradata
Check skew factor of all table in Database
SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))
AS SkewFactor FROM DBC.TableSize
WHERE databasename ='<DATABASE_NAME>'
GROUP BY 1,2
ORDER BY SkewFactor DESC;
Check skew factor of some specific tables in Databse
SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))
AS SkewFactor FROM DBC.TableSize
WHERE databasename ='<DATABASE_NAME>'
AND TableName IN ('<TABLE_NAME>','<TABLE_NAME>')
GROUP BY 1,2
ORDER BY SkewFactor DESC;
Example:
Check skew factor of all table in Database
SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))
AS SkewFactor FROM DBC.TableSize
WHERE databasename ='DBTgtT'
GROUP BY 1,2
ORDER BY SkewFactor DESC;
Check skew factor of some specific tables in Databse
SELECT DatabaseName,TableName,CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100))
AS SkewFactor FROM DBC.TableSize
WHERE databasename ='DBTgtT'
AND TableName IN ('SALES' , 'CUSTOMERS')
GROUP BY 1,2
ORDER BY SkewFactor DESC;