Is there a way to see what UPDATE STATISTICS level was last run?
Posted in 2009
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