Is it good to run update statistics as follows for system tables ?
--0-1818311736-1085258509=:39755
Content-Type: text/plain; charset=us-ascii
Hi all,
I am dropping dustributions while updating statistics for sytem tables. Is this a good idea ?
Examples :
update statistics low for tablesysaggregates
drop distributions;
update statistics low for tablesysams
drop distributions;
update statistics low for tablesysattrtypes
drop distributions;
update statistics low for tablesysblobs
drop distributions;
update statistics low for tablesyscasts
drop distributions;
update statistics low for tablesyschecks
drop distributions;
update statistics low for tablesyscolattribs
drop distributions;
update statistics low for tablesyscolauth
drop distributions;
update statistics low for tablesyscoldepend
drop distributions;
update statistics low for tablesyscolumns
drop distributions;
update statistics low for tablesysconstraints
drop distributions;
Thanks.
---------------------------------
Do you Yahoo!?
Yahoo! Domains - Claim yours for only $14.70/year
--0-1818311736-1085258509=:39755
Content-Type: text/html; charset=us-ascii
<DIV>Hi all,</DIV>
<DIV> </DIV>
<DIV>I am dropping dustributions while updating statistics for sytem tables. Is this a good idea ?</DIV>
<DIV> </DIV>
<DIV>Examples :</DIV>
<DIV> </DIV>
<DIV>update statistics low for table<BR>sysaggregates<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>sysams<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>sysattrtypes<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>sysblobs<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>syscasts<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>syschecks<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>syscolattribs<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>syscolauth<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>syscoldepend<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>syscolumns<BR>drop distributions;</DIV>
<DIV>update statistics low for table<BR>sysconstraints<BR>drop distributions;</DIV>
<DIV> </DIV>
<DIV>Thanks.</DIV>
<DIV> </DIV><p>
<hr size=1><font face=arial size=-1>Do you Yahoo!?<br>Yahoo! Domains - <a href="http://us.rd.yahoo.com/evt=23613/*http://smallbusiness.promotions.yahoo.com/offer">Claim yours for only $14.70/year</a>
--0-1818311736-1085258509=:39755--
sending to informix-list
Why?
The engine uses a fixed execution plan to get stuff from the dictionary.
The purpose for statistics is for the optimizer. The optimizer isn't used
to read the sys tables.
"Mike Smith" <idsquiz@yahoo.com> wrote in message
news:c8ofq4$bs3$1@terabinaries.xmission.com...
>
> --0-1818311736-1085258509=:39755
> Content-Type: text/plain; charset=us-ascii
>
> Hi all,
>
> I am dropping dustributions while updating statistics for sytem tables.
Is this a good idea ?
>
> Examples :
>
> update statistics low for table> sysaggregates
> drop distributions;
> update statistics low for table> sysams
> drop distributions;
> update statistics low for table> sysattrtypes
> drop distributions;
> update statistics low for table> sysblobs
> drop distributions;
> update statistics low for table> syscasts
> drop distributions;
> update statistics low for table> syschecks
> drop distributions;
> update statistics low for table> syscolattribs
> drop distributions;
> update statistics low for table> syscolauth
> drop distributions;
> update statistics low for table> syscoldepend
> drop distributions;
> update statistics low for table> syscolumns
> drop distributions;
> update statistics low for table> sysconstraints
> drop distributions;
>
> Thanks.
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Domains - Claim yours for only $14.70/year
> --0-1818311736-1085258509=:39755
> Content-Type: text/html; charset=us-ascii
>
> <DIV>Hi all,</DIV>
> <DIV> </DIV>
> <DIV>I am dropping dustributions while updating statistics for sytem
tables. Is this a good idea ?</DIV>
> <DIV> </DIV>
> <DIV>Examples :</DIV>
> <DIV> </DIV>
> <DIV>update statistics low for table<BR>sysaggregates<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>sysams<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>sysattrtypes<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>sysblobs<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>syscasts<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>syschecks<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>syscolattribs<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>syscolauth<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>syscoldepend<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>syscolumns<BR>drop
distributions;</DIV>
> <DIV>update statistics low for table<BR>sysconstraints<BR>drop
distributions;</DIV>
> <DIV> </DIV>
> <DIV>Thanks.</DIV>
> <DIV> </DIV><p>
> <hr size=1><font face=arial size=-1>Do you Yahoo!?<br>Yahoo! Domains - <a
href="http://us.rd.yahoo.com/evt=23613/*http://smallbusiness.promotions.yaho
o.com/offer">Claim yours for only $14.70/year</a>
> --0-1818311736-1085258509=:39755--
> sending to informix-list
"Madison Pruet" <mpruet@comcast.net> wrote in message
news:OF7sc.17407$JC5.1572229@attbi_s54...
> Why?
>
> The engine uses a fixed execution plan to get stuff from the dictionary.
> The purpose for statistics is for the optimizer. The optimizer isn't used
> to read the sys tables.
>
>
Well Art Kagels dostats does this to fix a bug where space for deleted
rows does not
get freed and reused in the systables hence if you keep doing lots of
table creates/drops
your systables get huge!
>
> "Mike Smith" <idsquiz@yahoo.com> wrote in message
> news:c8ofq4$bs3$1@terabinaries.xmission.com...
> >
> > --0-1818311736-1085258509=:39755
> > Content-Type: text/plain; charset=us-ascii
> >
> > Hi all,
> >
> > I am dropping dustributions while updating statistics for sytem tables.
> Is this a good idea ?
> >
> > Examples :
> >
> > update statistics low for table> > sysaggregates
> > drop distributions;
> > update statistics low for table> > sysams
> > drop distributions;
> > update statistics low for table> > sysattrtypes
> > drop distributions;
> > update statistics low for table> > sysblobs
> > drop distributions;
> > update statistics low for table> > syscasts
> > drop distributions;
> > update statistics low for table> > syschecks
> > drop distributions;
> > update statistics low for table> > syscolattribs
> > drop distributions;
> > update statistics low for table> > syscolauth
> > drop distributions;
> > update statistics low for table> > syscoldepend
> > drop distributions;
> > update statistics low for table> > syscolumns
> > drop distributions;
> > update statistics low for table> > sysconstraints
> > drop distributions;
> >
> > Thanks.
> >
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Yahoo! Domains - Claim yours for only $14.70/year
> > --0-1818311736-1085258509=:39755
> > Content-Type: text/html; charset=us-ascii
> >
> > <DIV>Hi all,</DIV>
> > <DIV> </DIV>
> > <DIV>I am dropping dustributions while updating statistics for sytem
> tables. Is this a good idea ?</DIV>
> > <DIV> </DIV>
> > <DIV>Examples :</DIV>
> > <DIV> </DIV>
> > <DIV>update statistics low for table<BR>sysaggregates<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>sysams<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>sysattrtypes<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>sysblobs<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>syscasts<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>syschecks<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>syscolattribs<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>syscolauth<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>syscoldepend<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>syscolumns<BR>drop
> distributions;</DIV>
> > <DIV>update statistics low for table<BR>sysconstraints<BR>drop
> distributions;</DIV>
> > <DIV> </DIV>
> > <DIV>Thanks.</DIV>
> > <DIV> </DIV><p>
> > <hr size=1><font face=arial size=-1>Do you Yahoo!?<br>Yahoo! Domains -
<a
>
href="http://us.rd.yahoo.com/evt=23613/*http://smallbusiness.promotions.yaho
> o.com/offer">Claim yours for only $14.70/year</a>
> > --0-1818311736-1085258509=:39755--
> > sending to informix-list
>
>
On Sat, 22 May 2004 16:41:49 -0400, Mike Smith wrote:
In general, the data distributions and other statistics on the system catalog
tables are not used much. Between most of the 'queries' the engine performs
internally being pre-optimized or 'fixed' as Madison puts it, and the Catalog
Cache hash tables in memory, it's usually not a big deal. HOWEVER, I have
seen cases where if the tables are frequently altered or new tables are
created dropped frequently, (or a large schema change was made recently) that
performance improves after updating stats on the system catalog including
data distributions. This is why dostats has an option (-m) to enable
processing of system catalog tables but normally ignores them.
Art S. Kagel
> --0-1818311736-1085258509=:39755
> Content-Type: text/plain; charset=us-ascii
>
> Hi all,
>
> I am dropping dustributions while updating statistics for sytem tables. Is
> this a good idea ?
>
> Examples :
>
> update statistics low for table> sysaggregates
> drop distributions;
> update statistics low for table> sysams
> drop distributions;
> update statistics low for table> sysattrtypes
> drop distributions;
> update statistics low for table> sysblobs
> drop distributions;
> update statistics low for table> syscasts
> drop distributions;
> update statistics low for table> syschecks
> drop distributions;
> update statistics low for table> syscolattribs
> drop distributions;
> update statistics low for table> syscolauth
> drop distributions;
> update statistics low for table> syscoldepend
> drop distributions;
> update statistics low for table> syscolumns
> drop distributions;
> update statistics low for table> sysconstraints
> drop distributions;
>
> Thanks.
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Domains - Claim yours for only $14.70/year
> --0-1818311736-1085258509=:39755
> Content-Type: text/html; charset=us-ascii
>
> <DIV>Hi all,</DIV>
> <DIV> </DIV>
> <DIV>I am dropping dustributions while updating statistics for sytem
> tables. Is this a good idea ?</DIV> <DIV> </DIV> <DIV>Examples
> :</DIV>
> <DIV> </DIV>
> <DIV>update statistics low for table<BR>sysaggregates<BR>drop
> distributions;</DIV> <DIV>update statistics low for table<BR>sysams<BR>drop
> distributions;</DIV> <DIV>update statistics low for
> table<BR>sysattrtypes<BR>drop distributions;</DIV> <DIV>update statistics
> low for table<BR>sysblobs<BR>drop distributions;</DIV> <DIV>update
> statistics low for table<BR>syscasts<BR>drop distributions;</DIV>
> <DIV>update statistics low for table<BR>syschecks<BR>drop
> distributions;</DIV> <DIV>update statistics low for
> table<BR>syscolattribs<BR>drop distributions;</DIV> <DIV>update statistics
> low for table<BR>syscolauth<BR>drop distributions;</DIV> <DIV>update
> statistics low for table<BR>syscoldepend<BR>drop distributions;</DIV>
> <DIV>update statistics low for table<BR>syscolumns<BR>drop
> distributions;</DIV> <DIV>update statistics low for
> table<BR>sysconstraints<BR>drop distributions;</DIV> <DIV> </DIV>
> <DIV>Thanks.</DIV>
> <DIV> </DIV><p>
> <hr size=1><font face=arial size=-1>Do you Yahoo!?<br>Yahoo! Domains - <a
> href="http://us.rd.yahoo.com/evt=23613/*http://smallbusiness.promotions.yahoo.com/offer">Claim
> yours for only $14.70/year</a>
> --0-1818311736-1085258509=:39755--
> sending to informix-list