Anyone who has worked with an Informix database knows that the moment you run into a query performance issue, you start with update statistics on that table. This was always what we were taught in support and talking with big names at IIUG, it’s still the best first step to take. The only problem with that is how “expensive” running update statistics can be.
As of version 12.10, IBM has added a new feature to update statistics. It gets dubbed “Smarter Statistics”. As of 12.10, update stats will now check the current distribution data and determine if the current statistics are stale or not. If they are considered viable, the update statistics command that you just ran does nothing and gets skipped. That obviously makes those of us still running our own scripts (or Art Kagel’s dostats), see our nightly update stats jobs completing earlier than they used to.
To facilitate this feature, a few new items have been added. In the onconfig file (for system default settings) or in the table schema, you can set the following parameters: STATCHANGE and STATLEVEL.
STATCHANGE can be defined in the table (can be looked up in the systables table), in the session (set STATCHANGE…), or in the onconfig. That is the order of precedence as well. You can set it to a number between 0 & 100, for the % of table change before it will consider the current stats as stale, or AUTO, which will have the server set the level (normally to the onconfig value).
STATLEVEL sets the granularity or level of stats created for the table. You can set it to table, fragment or auto. Fragment allows for stats to be kept for each fragment, which will be a bit higher granularity than table (though the same if table is a single fragment). Again this can be set in the onconfig or table schema.
The other item added for this is a new column in both systables and sysindices, ustlowts. This column now keeps track of the last time an update stats low was executed. The constr_time column in sysdistrib is still used for the high and medium executions.
So if a table’s numbers don’t show the STATCHANGE amount of changes to that table, update stats will be skipped for that table. I can see where this can be both good (if you are running the same list nightly) and bad (You are running it because you have an issue.) I have seen where problems can occur prior to the stats getting stale. You can still run them by using the keyword, “force” at the end of your update statistics command.
What does this mean for the average DBA? If you are running into problems and update stats is not fixing that problem, you may need to add the force keyword to make it actually work. The logic on whether it is run or not is similar to the logic used by the AUS evaluator routine to determine which tables get run or not as well.