How do interpert the results of this query which is shoeing me more actual sapce than wasted sapce
SyntaxEditor Code Snippet
select TableName, sum(CurrentPerm) as ActualSpace,count(*)*(max(CurrentPerm)-avg(CurrentPerm)) as WastedSpace from dbc.TableSize where DatabaseName='abc_data' group by TableName order by WastedSpace desc;
SyntaxEditor Code Snippet
table actual sapce wasted sace VG30_RCPT_TBL 45,379,907,072.00 3,625,251,328.00 VG30_RCPT_BKP 42,970,040,832.00 3,536,107,008.00 VG62_MAKER_TBL 176,272,896.00 321,575,424.00 VV30_INSPON_TBL 6,136,215,040.00 241,441,280.00 V045_RLSE_TBL 11,438,800,896.00 206,352,384.00 VV10_VCHR_FJL 51,134,644,224.00 202,715,136.00 VF01_RMINI_TBL 4,735,264,768.00 169,121,792.00 V016_MRSREL_TBL 22,622,766,592.00 150,891,008.00 VG52__PLT_TBL 1,527,831,040.00 137,500,160.00 VMA4_UPP_CHILD_TBL 26,468,389,376.00 82,353,664.00
The WastedSpace calculation above is one way to look at the impact of data skew. It's particularly useful to prioritize tuning efforts, or identify the likely cause of an "out of space" condition on one AMP (or few AMPs).
So reducing the data skew (e.g. by changing PI) for V630_RCPT_TBL potentially could free up 3+ GB for other tables.
currentperm records the actual usage - thus any effect from BLC is included. (It is harder to get the non-BLC number because all the usual fields reord the actual as it is stored.)
I dislike the term "wasted space" in this context. The space is not "wasted" because it can be used for other things - perm storage for other tables, spool space or temp space. It is possible but rare that all tables are skewed the same way on the same AMPs. Therefore if one table is skewed on a few AMPs and another table is skewed on a few other AMPs, then the skew balances out. More tables usually means more balance. This can be seen if the aggregate is done at the database level rather than at the table level.
I agree with Fred however that this is a good calculation to use to prioritze work on skewed tables. I actually like looking at the percentage of the skew in combination with the absolute number and then the percentage that the table represents of the total storage in the database/system. Spending a bunch of time optimizing skew of a small table is generally wasted effort even if the table is significantly skewed since the effecct on the overall database/system is minimal. Likewise with a large table the absolute number of bytes of skew might be large but the percentage skew is small and again it is likely wasted effort to try to hyper-optimize. A badly skewed large table though is a good target on which to spend time and energy.