Re: Update Statistics

The order I run update statistics is 1) MEDIUM DISTRIBUTIONS ONLY, 2) HIGH for first column in an index, 3) LOW for all other columns in an index, 4) UPDATE STATISTICS FOR PROCEDURE <procname> Is this still the recommended order? Regards Colin >From: "Andrew Ford" <andrewford@austin.rr.com> >To: "Doug Conrey" <doug_conrey@oci.com>, <informix-list@iiug.org> >Subject: Re: Update Statistics >Date: Mon, 13 Jun 2005 17:21:31 -0500 > >Yeah. I've got all of that. But is it true that running update statistics >high (or medium) without the 'distributions only' clause does the work of >low (building statistics) plus some extra stuff (building distributions). > >I've been putting a lot of work and thought into optimizing update >statistics for the 9.3xC2 and up engines and I think I'm really really >close. > >The general guidelines are > run 'upstats low' for columns in indexes that don't appear in upstats >high > (can get away with this since upstats high will do all of the > things you say about upstats low for the columns as long > as you do not specify 'distributions only') > > run 'upstats medium distributions only' for all columns in the table >that do > not appear in upstats high > > run 'upstats high' for every column that heads an index and the first > differing columns of composite indexes that begin with the same > subset of columns > (notice I'm leaving off the 'distributions only' here to do the >work > that would have been performed by 'upstats low' for these >columns) > >The main problem I'm trying to resolve now is: update stats low does >another thing besides building statistics. It also sends logically deleted >index pages to the btree cleaner for actual deletion. Which is nice. Does >update statistics high do the same thing for me when i do NOT specify the>'distributions only' stuff. If it does, I have a particular table that I >can run update statistics on using only 2 statements (medium and high) >because all of my index columns are either heads of indexes or are the >first >differeing column of a composite index that begins with the same subset of >columns, meaning I can possibly eliminate the update statistics low run >because I'm going to do the 'low' work when I run upstats high on them. > > >Andrew > > > >----- Original Message ----- >From: "Doug Conrey" <doug_conrey@oci.com> >To: <informix-list@iiug.org> >Sent: Monday, June 13, 2005 3:23 PM >Subject: RE: Update Statistics > > > > Update statistics low doesn't build distributions, the way high does. It> > compiles information about the structure of the indices (depth, leaf > > nodes, clustered-ness, second-highest and second-lowest values, etc), > > and places them in systables, sysindexes (or sysindices, in IDS 9.4...) > > and syscolumns, whereas medium and high build the distributions of the > > actual data and put it in sysdistrib. Therefore, it is important to run > > low and either medium or high for each column. We generally run low for > > the entire table, high for the leading edge of indices, and medium or > > high (depending on time and data size) for non-leading edge columns. > > Hope that helps. > > > > DC > > > > -----Original Message----- > > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > > On Behalf Of Andrew Ford > > Sent: Monday, June 13, 2005 1:53 PM > > To: informix-list@iiug.org > > Subject: Re: Update Statistics > > > > > > But... > > > > If every column in an index appears in the update statistics high, > > therefore eliminating the need for an update statistics low statement > > all together, is that a bad thing? > > > > Will an update statistics high without the 'distributions only' clause > > scan the indexes and send btree cleaner requests to clean up deleted > > index pages or is update statistics low the only operation that does > > that? > > > > Andrew > > > > "Andrew Ford" <andrewford@austin.rr.com> wrote in message > > news:73hre.43979$PR6.32863@tornado.texas.rr.com... > > > Or... > > > > > > update statistics low for table <table> (<columns>);> > > # for every column in an index that doesn't appear in the high run > > > > > > update statistics medium for table <table> (<columns>) distributions> > only; > > > # for every column of a table not in the update stats high > > > > > > update statistics high for <tablename> (<column list>);> > > # for every column that heads an index and the first differing > > > columns of > > > composite indexes that begin with the same subset of columns > > > > > > moved some colums from the low run to the high run and removed the > > > distributions only clause of the high run. > > > > > > Andrew > > > > > > "Andrew Ford" <andrewford@austin.rr.com> wrote in message > > > news:_vgre.43956$PR6.7994@tornado.texas.rr.com... > > > > I'm not sure I agree 100% with running update stats medium on the > > > > entire table. > > > > > > > > Sure, there is no performance penalty with regards to the update > > > > stats > > run > > > > times but the optimizer will be using less accurate medium > > > > statistics > > for > > > > heads of indexes to generate query plans until update stats high for > > those > > > > columns completes successfully. This may not be a problem for > > > > engines > > > that > > > > do light processing during maintenance windows, but for 24/7 engines > > this > > > > *could* be a problem. > > > > > > > > Other than the extra human work needed to generate a column list for > > > update > > > > stats medium, are there any downsides to running update statistics > > > > in > > the > > > > following way for newer versions of IDS that have the new update > > > > stats performance improvements (9.3xC2 and up)? > > > > > > > > update statistics low for <tablename (<column list>); # for every> > column > > > in > > > > an index > > > > update statistics medium for <tablename> (<column list>)> > > > distributions > > > only; > > > > # for every column not in the update stats high > > > > update statistics high for <tablename> (<column list>) distributions> > only; > > > > # for every column that heads an index and the first differing > > > > columns > > of > > > > composite indexes that begin with the same subset of columns > > > > > > > > Also, the order in which update statistics is run should no longer > > matter > > > > since we are not overwriting anything. > > > > > > > > Andrew > > > > > > > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > > > > news:42A8A5CD.3000204@bloomberg.net... > > > > > Andrew Ford wrote: > > > > > <SNIP> > > > > > > > > > > > Am I wrong in thinking that to update statistics against a table > > > > > > > > you > > > > only > > > > > > need 3 update statistics statements. > > > > > > > > > > > > low - every column that appears in an index > > > > > > high - every column that heads an

Colin Dawson schrieb: > The order I run update statistics is > > 1) MEDIUM DISTRIBUTIONS ONLY, > 2) HIGH for first column in an index, > 3) LOW for all other columns in an index, and please do not forget to set PDQPRIORITY to 0 right here [ whereas step 1-3 will run much faster if you give them more sort memory from MGM and use the Parallel Sort Package (PSORT-NPROCS and friends) Also DBUPSPACE does a good job, at least in 9.40.FC4W4 ] > 4) UPDATE STATISTICS FOR PROCEDURE <procname> > > Is this still the recommended order? > > > > > Regards > > Colin [ ... snip ... ] dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe