Data Skew Check

This is a good practice to do when new applications are being loaded on the system. It is also good practice to run fairly regularly if there are a lot of data changes.

1) First check to see if the primary index is ok for tables system-wide:

SEL (MAX(CurrentPerm) - MIN(CurrentPerm)) * 100
/(NULLIF(MIN(currentperm),0))
(NAMED variance)
(FORMAT 'zzzzz9.99%')
,MAX(CurrentPerm)
(TITLE 'Max')
(FORMAT 'zzz,zzz,zzz,999')
,MIN(currentperm)
(TITLE 'Min')
(FORMAT 'zzz,zzz,zzz,999')
,TRIM(DatabaseName)
||'.'
||TableName (NAMED Tables)
FROM DBC.tablesize
GROUP BY DatabaseName, TableName
HAVING SUM(CurrentPerm) > 1000000
AND variance > 1000
WHERE DatabaseName
NOT IN ('CrashDumps','DBC')
ORDER BY Tables;


2) If this is not the case, try to identify the job that might be responsible for the concentration of IO and cpu by checking spool usage by vproc:

/**************************************************************
//* DBC.DISKSPACE - Get AMP Number with skew for data & Peak Spool */
/* - Include % distribution efficiency */
/**************************************************************/
.export file diskskew.out
.set defaults
.set separator 1
.set width 110

SELECT A.databasename (Format 'x(20)') (Title 'DiskSpace//DB Name')
, A.accountName (Format 'x(20)') (Title 'Acct Name')
, A.vproc (Format 'zzzz9') (Title 'AMP //Number')
, A.currentperm / (1024**2) (Format 'zz,zz9.999') (Title '//CurrPerm')
, DT.avgCurperm / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//CurrPerm')
, A.currentperm / DT.avgCurperm (Format 'zzz9.99') (Title 'CurrPerm//Skew')
, A.peakspool / (1024**2) (Format 'zz,zz9.999') (Title '//PeakSpool')
, DT.avgPeakspool / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//PeakSpool')
, A.peakspool / DT.avgPeakspool (Format 'zzz9.99') (Title 'PeakSpool//Skew')FROM DBC.Diskspace A,

(SELECT databasename (Format 'x(12)') (Title 'DiskSpace//DB Name') , accountName (Format 'x(12)') (Title 'Acct Name')
, AVG(currentperm) (Format 'z,zzz,zz9') (Title 'CurrPerm//MBytes')
, AVG(peakspool) (Format 'z,zzz,zz9') (Title 'PeakSpool//MBytes') FROM DBC.Diskspace

GROUP BY 1, 2 ) DT (databasename, accountName, avgCurperm, avgPeakspool)
WHERE A.databasename = DT.databasename
AND A.accountName = DT.accountName
AND (A.currentperm / DT.avgCurperm >= 1.30 OR A.peakspool / DT.avgPeakspool >= 1.30)
ORDER BY 3, 1, 2
;
.set defaults




/************************************************************************/
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by Users*/
/* with more than 100,000 cpu seconds */
/************************************************************************/
.export file ampusage.out
.set defaults
.set width 110

SELECT A.accountName (Format 'x(18)') (Title 'AMPUsage//Acct Name')
, A.username (Format 'x(22)') (Title 'User Name')
, SUM(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'CPUtime')
, SUM(A.DiskIO) (Format 'zzz,zzz,zzz,zz9') (Title 'DiskIO')
, AVG(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'AvgCPUtime')
, MAX(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'MAXCPUtime')
, MAX(A.CPUTime)/nullifzero(AVG(A.CPUTime) )(Format 'zz9.99') (Title 'CPU//Skew//Effect')
FROM DBC.AMPUsage A
GROUP BY 1, 2
HAVING SUM(CPUTime) > 1e5
ORDER BY 3 desc, 1, 2;

.set defaults
.set width 80


/**************************************************************/
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by User */
/* with skewing greater than 30% over average*/
/**************************************************************/

.export file ampusageskew.out
.set defaults
.set width 94
SELECT A.accountName (Format 'x(18)') (Title 'AMPusage//Acct Name')
, A.username (Format 'x(12)') (Title 'User Name')
, A.vproc (Format '99999') (Title 'Vproc')
, A.CPUTime (Format 'zz,zzz,zz9') (Title 'CPUtime')
, DT.AvgCPUTime (Format 'zz,zzz,zz9') (Title 'AvgCPUtime')
, A.CPUTime/NULLIFZERO(DT.AvgCPUTime)(Format 'zz9.99')(Title 'Ratio//to Avg')(Named CpuRatio), A.DiskIO (Format 'zzz,zzz,zzz,zz9') (Title 'AvgDiskIO')
, A.DiskIO /NULLIFZERO(DT.avgDiskIO) (Format 'z9.99') (Title 'Ratio//to Avg')FROM DBC.AMPUsage A,

(SELECT accountName
, username
, AVG(CPUTime)
, SUM(CPUTime)
, AVG(DiskIO)
, SUM(DiskIO) FROM DBC.AMPUsageGROUP BY 1, 2
HAVING SUM(CPUTime) > 1e4) DT (accountName, username, avgCPUtime, sumCPUtime, avgDiskIO, sumDiskIO)
WHERE A.accountname = DT.accountName
AND A.username = DT.username
AND CpuRatio > 1.30
ORDER BY 5, 1, 2, 3;

3) Once you have identified the application, check the tables involved to see if secondary indexes are skewed or if the access to the tables is causing a concentration on the clique...

/***********************************************/
/* The following query will provide the distribution by amp*/
/* for a given index or column. */
/***********************************************/


sel hashamp(hashbucket(hashrow(index or column)))
,count(*)
from database.table
group by 1
order by 2 desc;


/* */
/* The following query will provide the number of collisions */
/* for row hash. */
/* */

sel hashrow(index or column), count(*)
from database.table
group by 1
order by 1
having count(*) > 10;