Re: Is there a way to see what UPDATE STATISTICS level was last run?

In your older versions (ie 9.40) only the data distributions recorded their create times. Look at the sysdistrib catalog table, you will find the 'constructed' column which has the date on which the distributions for that column were last calculated and the 'mode', 'resolution', and 'confidence' columns indicate what level and quality of distributions were generated. Details about when LOW distributions were run was not retained in this version. In 11.10 and later, there is additional information available including the time that distributions were gathered and the date and time that LOW statistics were generated are kept in the syscolumns and sysindices tables as well as details about sampling size for MEDIUM distributions which is a new option in 11.10 and later. As to what stats to gather, it is best to follow the recommendations in the Performance Guide and in John Miller III's white paper on the subject, or, just get my dostats utility which implements these protocols automatically. Dostats is part of the package utils2_ak which you can download free from either the Oninit WEB site (www.oninit.com/utils) or from the IIUG Software Repository (www.iiug.org/software). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Oct 14, 2009 at 3:05 PM, steven_nospam at Yahoo! Canada < steven_nospam@yahoo.ca> wrote: > We have two Informix 9.4 FC7 instances (call them TEST and PROD) that > were inherited from a previous management team. As part of our > mandate, our goal is to upgrade the system to a more modern version of > Informix that is still certified by our third-party software vendor > with their product. > > We recently upgraded the TEST instance to Informix 11.10 FC2 and after > doing so ran UPDATE STATISTICS LOW as most of the queries the software > does are SQL statements that only make use of one table, and they have > indexes designed specifically for those tables to reduce temporary > dbspace usage and the performance hit. The software that makes calls > to the databases uses hints to make sure that it uses the correct > index. > > Our problem is that some of our people have been developing their own > queries that are more complex, and we have noticed a severe slowdown > in performance related to these queries. Single table queries: really > fast...Queries that use multiple tables and OUTER joins: really slow. > > Example of a problem query: > BEFORE Informix 11: 381 rows per second > AFTER Informix 11: 89 rows per second > > I don't want anyone to try and optimize the code or suggest ways to > improve doing our queries, as that is something our team has to deal > with internally. > > What I would like to know is if sysmaster database or some other area > of the system keeps track of the UPDATE STATISTICS command(s) that > were used in a database previously? > > For example, if I wanted to check the production (PROD) database to > see what the last settings were for calling UPDATE STATISTICS, to see > if individual tables may have been adjusted to make the searching more > efficient. > > I ask this because the users are telling me that the previous DB team > had to issue some sort of special UPDATE STATISTICS commands to fine- > tune things on the production database, and being able to find out > some detail on this would save me a lot of running queries with SET > EXPLAIN on. > > I'm afraid I'm more of an UNIX person than a DB person, so forgive me > if I left anything out or used the wrong terminology. > > Steve > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >