Start of Content Area

Procedure documentation Monitoring Optimizer Statistics (Informix)  Locate the document in its SAP Library structure

Use

You can use the SAP/Informix database monitor to monitor Informix optimizer statistics. The Informix cost-based optimizer needs information on the data in a table to work out how best to process a query, as shown in the following example:

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? If so, it would not 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 this example, the optimizer could then recognize that it is much more efficient to retrieve data with the NAME index than the STATE index.

Prerequisites

SAP and Informix have developed tools to run update statistics efficiently. These tools are preferable to running update statistics with the Informix tool dbaccess . For more information on using these tools, see SAP Note 12184.

Procedure

  1. Choose Tools ® CCMS ® Control/Monitoring ® Performance Menu ® Database ® Activity ® Detail Analysis Menu ® Table Activity to display the table activity monitor.

    2. This 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 might indicate that the optimizer is choosing the wrong path for querying this table.

  2. Choose Tools ® CCMS ® Control/Monitoring ® Performance Menu ® Database ® Activity ® Detail Analysis Menu ® State on Disk ® Checks ® Update Statistics to display information about the statistics update.

You can display information for a specified table or for all tables. Note that this operation consumes a lot of processing power if you run it for the entire database.