I need some help / suggestion in determining how to eliminate multi column stats without hampering Perfomance.
we have a 10 Node Teradata production system around 12K tables.
All in all we have 55K stats in total :)
On some of the important transation table we have more than 350+ stats and many of then are multi column stats which got colllected over the period due to various recommendations or needs.
example of few multi-column stats :
COLUMN ( SO_HdrNbr,SO_HdrCrtDt,SO_ItmNbr,OrigPhysSrcSysSK,SO_SchedNbr,CCD,DivCd,BusCalCd,SuperGeoID ) ,
COLUMN ( SO_HdrNbr,SO_HdrCrtDt,SO_ItmNbr,OrigPhysSrcSysSK,SO_SchedNbr,CCD,DivCd,PlntCd,SuperGeoID ) ,
COLUMN ( SO_HdrNbr,SO_HdrCrtDt,SO_ItmNbr,OrigPhysSrcSysSK,SO_SchedNbr,CCD,PlntCd,SuperGeoID,ActvInd ) ,
COLUMN ( SO_HdrNbr,SO_HdrCrtDt,SO_ItmNbr,OrigPhysSrcSysSK,SO_SchedNbr,CCD,SO_DocTypeCd,DivCd,SuperGeoID ) ,
COLUMN ( DistribMthdCd,SO_HdrNbr,SO_HdrCrtDt,SO_ItmNbr,OrigPhysSrcSysSK,SO_SchedNbr,CCD,DivCd,SlsOrgCd,SuperGeoID ) ,
above stats are on PIs as well as few additional non-PI columns.
How to determine which stat to keep and drop rest or atleast few ?
You probably need to start using the AutoStats feature of TD and the Viewpoint 'stats manager' portlet' I suggest you:
Have a look at the BEGIN QUERY LOGGING command, specifically the STATSUSAGE option.
Also look in the VP user guide about the 'stats manager' portlet.
Have a look on the Teradata Dev Ex site (maybe via Google!) for Blogs about the 'AutoStats' feature. Look for the ones by Carrie Ballinger.
This will allow you to identify the stats which are not being used and also which stats the optiimiser would like to have but are not there yet.
No, this is probably not an immediate answer for you because having got the DBQL option turned on you then need to run queries.
Activate the usecount option to determine what statistics are not being used.
In the short term, you can look at the demographics of the stats to look for unneeded ones. See this blog post taking into account the limit in newer teradata versions is 25 characters.