Re: update statistics medium/high: how often?

This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. ---1607792580-1099951567-884736134=:11568 Content-Type: TEXT/PLAIN; charset=US-ASCII On Tue, 13 Jan 1998, Ofer Inbar wrote: > > > What are some good guidelines for determining how often I should run > > > an update statistics high or medium? > > > > Look in the release notes: > > > > $INFORMIXDIR/release/en_us/0333/SERVERS_7.2 (or _7.1 for versions before > > 7.20). If you have an _7.2 file and it has the same advice as te _7.1 > > file you have one of the releases without updated release notes. Let us > > know and I will post the _7.2 text. > > Thanks - we're running 7.22UC2. I have both of these files, and they > are different. The 7.2 file gives no advice about update statistics > at all, but the 7.1 file has a section titled "Importance of running > update statistics". This isn't very different from what's in the > manual, which I already read. It's just as vague about deciding how> often to run the update: "UPDATE STATISTICS should be run as often as > necessary to ensure that the number of rows statistic is as up-to-date > as possible. Therefore, if the cardinality of a table changes often, > the command should be run more often for that table." That's useful, > as far as it goes, but it still doesn't answer the question. Yup you've got one of the scr---d up doc files. I am attaching the extract from my server. I am also posting this to C.D.I. I have highlighted with Bars along the left margin the changes from the 7.1 recommendations which were indeed the ones in the manual. BTW you can get my dostats.ec utility, which implements these rules, from the iiug archives and someone else posted shell scripts to do something very similar. Art S. Kagel, kagel@bloomberg.com ---1607792580-1099951567-884736134=:11568 Content-Type: TEXT/PLAIN; charset=US-ASCII; name=stats Content-Transfer-Encoding: BASE64 Content-ID: <Pine.D-G.3.96.980113190214.11568G@dg1> Content-Description: OPTIMIZER: IMPORTANCE OF RUNNING UPDATE STATISTICS ================================================== As with every version, it is particularly important to run the UPDATE STATISTICS command occasionally. This command updates the statistics used by the optimizer. Because the optimizer is cost- based, it determines the most effective method for retrieving data from the database based on statistics gathered by the UPDATE STATISTICS command. UPDATE STATISTICS LOW should be run as often as necessary to insure that the number of rows statistic is as up-to-date as possible. Therefore, if the cardinality of a table changes often, the command| should be run more often for that table. UPDATE STATISTICS MEDIUM | and HIGH generate distribution information. They only need to be | rerun if the distribution of values for a particular column has | changed significantly since the last time the command was run for | that particular column. The following are guidelines that should be used in deciding which modes of UPDATE STATISTICS should be used for any given table. 1) Run update statistics MEDIUM for all columns in a table that DO NOT head an index. This will be a single update statistics command. The default parameters are sufficient unless the table is very large, in which case you should use a resolution of 1.0, 0.99. (Beginning with version 7.10.UD1, with the DISTRIBUTIONS ONLY option, this becomes simpler; you can execute an update statistics MEDIUM at the table level or for the entire system since the overhead of the extra columns isn't that expensive.) 2) Run update statistics HIGH for all columns which head an index. For the fastest execution time of the update statistics command in Informix-OnLine, you MUST execute one "update statistics HIGH ..." for EACH column. A single command suffices for Informix-SE. | In addition when you have indices which begin with the same | subset of columns, you should also run update statistics high | for the first column in each which differs. For example if you | had index1 -> (a,b,c,d) and index2 -> (a,b,e,f), then you would | run update statistics high on "a" by itself and then on "c" and | "e". In addition it would probably be a good idea to run it on | "b" but it is usually not necessary. 3) For each multi-column index, execute update statistics LOW for ALL of its columns. (For the single column indices in 2) you've already executed LOW implicitly when you executed HIGH.) | These steps will insure that UPDATE STATISTICS executes most | rapidly because it only constructs the index information statistics | once for each index. Several improvements have been made to the | optimizer and the cost estimates have been adjusted to establish | better query plans. These modifications, however, increased the | optimizer's dependence on an accurate understanding of the | underlying data distributions in certain cases. While executing | complex queries involving equality predicates, if after following | the above prescription, you feel that the query is not executing | with sufficient rapidity, please do one of the following : | | - Run update statistics HIGH on columns which participate in | equality join predicates but do not head indices. Having |