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; |