Solving Problems with Out-of-Date Optimizer Access Paths (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

Solving Problems with Out-of-Date Optimizer Access Paths

Use

When the access paths used by the Informix optimizer are out-of-date, data access gets worse. This affects especially access to large tables and views. If you run Explain SQL this often shows that the correct index is being used but that the costs for the access are still very high. This problem is worsened if the database server is not often restarted.

Database data changes dynamically during production operation. However, if the number of rows in a database table does not change, the access path is not updated by the optimizer – although the path no longer corresponds to the current data distribution. Note that the access path is always updated if statistics are updated with SAPDBA using zero deviation.

The data distribution in some database tables of the SAP System can lead the optimizer to choose a poor access path. This problem is worsened if there are large changes in the data distribution after the optimizer statistics are generated. Therefore, it is sometimes better for particular tables not to use the access path suggested by the optimizer.

Procedure

Analyze the problem:

Check that the following parameter is set in the ONCONFIG file:

OPTCOMPIND = 0

For more information, see SAP note 143956.

Check that the environment parameters specified in SAP notes 141054, 152378, and 176175 are correctly set.

Check whether the SYSDISTRIB table contains entries by entering the following SQL command:

select * from sysdistrib
where tabid in
(select tabid from systables
where tabname = '<table name>');

Solve the problem:

Restart the database and the SAP System. This refreshes the database shared memory.

Delete the entries that you identified above from the sysdistrib table by entering the following SQL command:

delete from sysdistrib
where tabid in
(select tabid from systables
where tabname = '<table name>');

Another way to do this is by entering the following SQL command:

update statistiscs low for table <table name>
drop distributions

The second method generates new statistics and drops the distributions. Therefore, it takes longer than the first method.

Generate new distribution information for the tables identified above by running update statistics. Refer to Updating Statistics for One Table with SAPDBA. Refresh the statistics daily or as often as possible.

You might need to customize update statistics for the tables identified above:

Set the threshold to 0 or perform update statistics without distributions.
For views, check whether there is an index for the join condition and whether this has had an update statistics.

For more information on update statistics parameters, see Updating Statistics from the Command Line with SAPDBA.

When you have identified the best way to perform update statistics for a particular table, you can enter the parameters in the control table DBSTATC . Refer to Configuring Update Statistics in CCMS (Informix). This means that all future update statistics use the new parameters.

Result

The statistics are more up-to-date and reflect the current data distribution. Monitor problematic tables in future and update statistics on such tables frequently, customizing the update statistics parameters if necessary.

See also:

Update Statistics with SAPDBA