Monitoring Optimizer Statistics (Informix) 

The Informix cost-based optimizer needs information on the data in a table to work out how best to process a query.

We can illustrate the importance of this information with a simple example:

Consider a table EMPLOYEE which includes the columns NAME and STATE . Assume there is one index on each of these columns. If a query comes with a WHERE clause " WHERE NAME = ‘JONES’ and STATE = ‘NY’ ", how does the database know which index to use? What would happen if all employees lived in New York? It would hardly make sense to use the STATE index.

The "Update Statistics" command tells the Informix optimizer about the distribution and selectivity of data in table columns. In our example, the optimizer could then recognize that it is much more efficient to retrieve its data using the NAME index than with the STATE index.

SAP and Informix have developed tools to run Update Statistics efficiently. These tools are preferable to running update statistics in dbaccess.

See also: CCMS DBA documentation, R/3 Note 12184 for how to use these tools.

The Table Activity Monitor in the Detail Analysis Menu provides information on the most recent update statistics run for the tables displayed. If you see a table with a high level of activity and an old date for the last statistics update, this could be a sign that the optimizer is choosing the wrong path for querying this table.

 

To display information about the statistics update

From the Detail Analysis Menu, select: State on Disk ® Checks ® Update Statistics.

You can display information for a specified table or for all tables.

Note that this operation is expensive if you run it for the entire database.