In this example, the SELECT request looks for poorly distributed tables by displaying the average, minimum, and maximum of the CurrentPerm figures allocated on each AMP to every table in the USER database. Each table is reported separately, ordered by name.
SELECT TableName (FORMAT 'X(20)'), MIN(CurrentPerm) AS "AMP Minimum", AVG(CurrentPerm) AS "AMP Average", MAX(CurrentPerm) AS "AMP Maximum" FROM DBC.TableSizeV WHERE DatabaseName = 'USER' GROUP BY TableName ORDER BY TableName;
Result:
TableName AMP Minimum AMP Average AMP Maximum -------------------- ----------- ----------- ----------- employee_nupi_ondept 4,096 15,488 30,208 employee_upi_onempid 18,944 19,200 19,968
The result displays two tables. Notice the results show that:
- The table Employee_upi_onempid is evenly distributed. CurrentPerm is similar across all vprocs (the minimum and maximum are close to the average). Permanent space is relatively evenly distributed across all AMPs in the system.
- The table Employee_nupi_ondept is poorly distributed. The CurrentPerm figures range from a minimum of 4,096 bytes to a maximum of 30,208 bytes on different AMPs, indicating a wide variance from the average.