Is it good to run update statistics as follows for system tables ?
This is a discussion on Is it good to run update statistics as follows for system tables ? within the Informix forums, part of the Database Server Software category; --> --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 ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 10:29 PM
| ||||
| ||||
 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 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      |
|
04-19-2008, 10:29 PM
| |||
| |||
| Re: Is it good to run update statistics as follows for system tables ? 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 |
|
04-19-2008, 10:29 PM
| |||
| |||
| Re: Is it good to run update statistics as follows for system tables ? "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 > > |
|
04-19-2008, 10:29 PM
| ||||
| ||||
| Re: Is it good to run update statistics as follows for system tables ? 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 |
Linear Mode
