All Forums Marketing & CRM
Ryan Murphy 2 posts Joined 11/07
05 Nov 2007
IOS table statistics

Today I discovered that our IOS_ tables have no statistics defined, and I believe this may be contributing to a significant system performance degradation we're experiencing. Is anyone aware of any best practices documentation covering IOS table statistics? The issue concerns only the IOS_ tables, like IOS_ECAMP and IOS_ECAMP_X_SEG. Our IOS1_[BBBBXXXXXXXX] tables created by segmentation processing do have fresh stats.Thank you,Ryan

Carol Barker 24 posts Joined 04/06
06 Nov 2007

Working on an answer for you Ryan.  Please stand by....Carol

Tuvix 7 posts Joined 04/07
06 Nov 2007

In the CRM maintenance guide, there's sample SQL along with guidelines on how frequently to run it. We created a BTEQ script that we run weekly that collects the stats we need against the IOS_* tables.DavidVerizon

Ryan Murphy 2 posts Joined 11/07
06 Nov 2007

Thank you both. I found the section in the Maintenance and Operations manual detailing statistics recommendations and including the scripts to generate the SQL. I'll create a BTEQ script and get these scheduled.Ryan

Ince 31 posts Joined 05/08
09 May 2008

HiOn the , do I mention all the tables that I need to collect statistics on?Refer to the script provided on the TCRM maintenance guideline below/**/*----------------------------------------------------------------------------Description:This SQL will generate a Collect Statistics script for all of the columnsof each index in dbc.Indices.Instructions:1. Replace @YOURDATABASENAME w/ the correct DatabaseName2. Modify the "where" clause as necessary to target specific tables3. Execute this SQL and capture the resulting "Collect Stats" scripts4. Execute the resulting Collect Stats script as necessaryRequirements:- Select access to DBC database----------------------------------------------------------------------------*/select (case Min_Poswhen 1 then 'Collect Statistics on ' ||trim(upper(DatabaseName)) || '.' ||trim(upper(TableName)) || ' INDEX ('else ' ,' end) ||trim(upper(ColumnName)) ||(case Max_Poswhen 1 then ');'else '' end)from(selectDatabaseName,TableName,ColumnName,IndexNumber,ColumnPosition,rank() over (partition by DatabaseName, TableName, IndexNumber order by ColumnPosition)Min_Pos,rank() over (partition by DatabaseName, TableName, IndexNumber order by ColumnPositiondesc) Max_Posfrom dbc.Indiceswhere DatabaseName in('@YOURDATABASENAME')and TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')) xxxorder bydatabasename, tablename, indexnumber, columnposition;/*----------------------------------------------------------------------------Description:This SQL will generate a Collect Statistics script for individual columnsof index fields.Instructions:1. Replace @YOURDATABASENAME w/ the correct DatabaseName2. Modify the "where" clause as necessary to target specific tables3. Execute this SQL and capture the resulting "Collect Stats" scripts4. Execute the resulting Collect Stats script as necessaryRequirements:- Select access to DBC database----------------------------------------------------------------------------*/select'Collect Statistics on ' ||trim(upper(a.TableName)) ||' Column ' ||trim(upper(ColumnName)) ||';' ansfromdbc.indices awhere12.3 Periodic Maintenance Tasks12-63a.DatabaseName = '@YOURDATABASENAME' and-- Modify the TableName where clause to target specific tablesa.TableName like any ('IOS%','WCM%') anda.TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')order byTableName, ColumnPosition;**/Regards,Ince Chauke

You must sign in to leave a comment.