|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/6/2007 10:34:22 AM
Posts: 2,
Visits: 3
|
|
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
|
|
|
|
|
Junior Member
Group: Moderators
Last Login: 7/21/2008 2:27:50 PM
Posts: 24,
Visits: 9
|
|
|
Working on an answer for you Ryan. Please stand by....Carol
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 7:21:26 AM
Posts: 5,
Visits: 6
|
|
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.
David
Verizon
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/6/2007 10:34:22 AM
Posts: 2,
Visits: 3
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: Yesterday @ 3:18:48 AM
Posts: 24,
Visits: 112
|
|
Hi
On 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 columns
of each index in dbc.Indices.
Instructions:
1. Replace @YOURDATABASENAME w/ the correct DatabaseName
2. Modify the "where" clause as necessary to target specific tables
3. Execute this SQL and capture the resulting "Collect Stats" scripts
4. Execute the resulting Collect Stats script as necessary
Requirements:
- Select access to DBC database
----------------------------------------------------------------------------*/
select (case Min_Pos
when 1 then 'Collect Statistics on ' ||
trim(upper(DatabaseName)) || '.' ||
trim(upper(TableName)) || ' INDEX ('
else ' ,' end) ||
trim(upper(ColumnName)) ||
(case Max_Pos
when 1 then ');'
else '' end)
from
(
select
DatabaseName,
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 ColumnPosition
desc) Max_Pos
from dbc.Indices
where DatabaseName in
('@YOURDATABASENAME')
and TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')
) xxx
order by
databasename, tablename, indexnumber, columnposition;
/*----------------------------------------------------------------------------
Description:
This SQL will generate a Collect Statistics script for individual columns
of index fields.
Instructions:
1. Replace @YOURDATABASENAME w/ the correct DatabaseName
2. Modify the "where" clause as necessary to target specific tables
3. Execute this SQL and capture the resulting "Collect Stats" scripts
4. Execute the resulting Collect Stats script as necessary
Requirements:
- Select access to DBC database
----------------------------------------------------------------------------*/
select
'Collect Statistics on ' ||
trim(upper(a.TableName)) ||
' Column ' ||
trim(upper(ColumnName)) ||
';' ans
from
dbc.indices a
where
12.3 Periodic Maintenance Tasks
12-63
a.DatabaseName = '@YOURDATABASENAME' and
-- Modify the TableName where clause to target specific tables
a.TableName like any ('IOS%','WCM%') and
a.TableName not like all ('IOSV%', 'IOS1%' , 'IOS2%', 'IOS3%', 'IOS4%', 'IOS5%')
order by
TableName, ColumnPosition;**/
Regards,
Ince Chauke
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|