SAP Note 12184 - Improved performance with 'UPDATE STATISTICS'

Symptom:

Long runtimes for individual database accesses or generally bad performance of the database.

Cause and prerequisites

To determine an optimum access path for search queries in the database, the optimizer requires current information on the following for the individual tables
o the number of data records,
o the size of the indexes and
o the distribution of the values of individual fields.

If this statistical data is obsolete or does not exist, this can cause performance problems because the optimizer may then use an unfavorable access path.

For this reason, it is necessary to update the statistics information regularly.

Solution
Regular scheduling of an 'Update Statistics' run
(recommendation: 1x per week).

As of Releases 2.1H and 2.2B, there is a program 'infupdstat' and as of Release 3.0A, the tool 'sapdba' for this.

ATTENTION
The SAPDBA v.45x strategie may lead to performance problems. If this
happens, see Note 184760.
ATTENTION

If your system has a release prior to Release 2.1H or 2.2B, you need to carry out the 'Update Statistics' manually.

You can check whether an Update Statistics was executed successfully using the 'Performance Monitor' (Transaction ST04) as follows:
o Call Transaction ST04
o 'Goto -> Activity -> Current statistics -> Table activity'
o 'Edit -> Update Statistics -> Display all tables'
The field 'Update Statistics' will now display the date and level
of the last Update Statistics for each table.
By sorting the list to this field, you can check when the last
statistics have been updated.


The following describes how you can schedule an 'Update Statistics' run (depending on your start release):


As of Release 3.0A =================
Scheduling is carried out using the DBA scheduling calendar:

(1) Call the scheduling calendar (transaction DB13).

(2) Choose the day on which the first 'Update Statistics' run should be carried out with a double-click.
(If anything is already scheduled for this day, this is displayed.
In this case, branch into the action list with 'Insert').

(3) In the action list, enter the required start time and repetition
period (1 => weekly) and choose the action 'Update optimizer
statistics (all tables)'.

(4) As of Release 3.1G, a screen now appears where you can specify the
following parameters:

o Threshold value for Update Statistics (default: 10%)
An Update Statistics is then only carried out for a table if
the current number of data records differs from the statistical
information by more than the percentage determined by the
threshold value.
o Statistics for the application monitor (default: not active)
If this parameter is activated, additional information is
gathered which is evaluated by the application monitor. This
function is very runtime-intensive and should therefore only be
activated if you use the application monitor.
o Maximum runtime (default: no restriction)
If you enter a limit here, then the 'Update Statistics' run
is exited after the current table as soon as this time limit is
exceeded.

In releases prior to 3.1G, 'Update Statistics' is automatically
scheduled
o with a threshold value of 10%,
o with the calculation of the application monitor statistics and
o without a time limit

(5) The screen is (compared with 3.1) expanded as of Release 4.0:
o Execution strategy (Default: Serial):
If the database computer has several processors and the
ONCONFIG parameter NUMCPUVPS is set to a value greater than
1, a suggestion value corresponding to NUMCPUVPS is
automatically made. In this case, the administrator can choose
the parallel strategy of execution. It is recommanded NOT
to use a value greater than the suggested value.
Caution: When carrying out the parallel execution of the 'Update
Statistics', more 'Workload' on the database computer is
generated as with the serial execution. Moreover,
more sort space (temporary database space) is
allocated.
As a rule, the parallel execution leads to a significant
reduction of the total runtime of the 'Update Statistics'.

o Log file (Default: $INFORMIXDIR/sapreorg/updstat_.log):
The sapdba, called for the execution of the 'Update Statistics',
writes a log file. If the field 'Detailed' is not activated
(default), a short log entry including statistic is logged into
the specified log file. If 'Detailed' is activated, all
activities are logged onto tables/columns and the log entry is
very long.

WARNING: If there is no corresponding scheduling, an upgrade
automatically schedules an 'Update Statistics' for 1x
weekly.
The standard scheduling patterns also contain a weekly
'Update Statistics' run.


Release 2.1x (as of 2.1H) or 2.2x (as of 2.2B)
============================================

To schedule a 'Update Statistics' run ('infupdstat'), proceed as follows:

(1) Start Transaction DB02 and choose the menu function 'Goto -> Check
-> Table statistics -> Sizes / Statistics'.

(2) Choose the function 'Create'.
In the popup that then appears, you can make the following entries:
o "job description" : Any job name.
o "Schedule" : Here, you enter the day of the week, the
time (hours, 0-23), the number of weeks
until the 1st run and the repeat interval.
o "Parameters" : o Job active or inactive. When you make a
new scheduling, you should always choose
active here.
Using this field, you can "unschedule"
a job without having to delete it.
o The level of the analysis should be
set to "high".
o Threshold (default 5%):
An 'Update Statistics' is only carried
out for the individual tables if the
current number of data records differs
from the statistics value by (at least)
the threshold value entered here.
o "Logfile" determines the name of the log
file (default: 'infupdstat.log' in the
log directory of the current instance).

(3) Save your settings with 'Save'.

It is sufficient to schedule the job on just one instance, for example on the database server.
The background job 'COLLECTOR_FOR_PERFORMANCEMONITOR' must be scheduled in client 000 for the user 'DDIC' (program name: RSCOLL00) and should run every hour.


Releases 2.1A to 2.1G or 2.2A
=============================

Carry out the 'Update Statistics' as the user 'informix' with the Informix utility 'dbaccess':
"su - informix"
"dbaccess -"
"update statistics medium;"
You can also do this by creating a corresponding shell script which can then be automated using a Cronjob.

WARNING: If your database has an INFORMIX Online Version 6.00.xx, you should restart the database afterwards because the 'Update Statistics' uses a relatively large amount of memory which is not automatically released again afterwards in this version.

Additional key words

db13, infupdstat, sapdba, runtime, performance, update statistics optimizer, sysdistrib