Scheduling Updates of the SQL Optimizer Statistics

You can schedule an update of the SQL Optimizer statistics in the DBA Planning Calendar. Up-to-date statistics are essential for the SQL Optimizer to run efficiently.

Prerequisites

DBA Planning Calendar

Setting Up and Using the DBA Planning Calendar

Procedure

...

       1.      Observe the planning concept .

       2.      Open the DBA Planning Calendar.

       3.      You can use an action patternto update the statistics.

       4.      Proceed as described in Scheduling an Action, to schedule the statistics update.

Use the following actions:

0     Update all Optimizer statistics: all the required statistics are updated.

To exclude a table from the updating of statistics, you select the value 0 for the sample size for this table.

0     Select tables for update statistics: all the tables necessary for a statistics update are identified.

0     Update statistics for selected tables: all the statistics for the selected tables are updated.

You should execute the action Update all Optimizer statistics after an installation or an upgrade.

Thereafter, you should execute both the Select tables for update statistics and the Update statistics for selected tables actions once a week.

Result

The statistics update is scheduled. The statistics are updated at the specified time.

Check the scheduling and the results of the UPDATE STATISTICS runs in the DBA Planning Calendar, as described in Displaying Actions. Use the information displayed in the DBA History. Also use Optimizer Statistics in the Database, or in the liveCache Alert Monitor (OLTP/ liveCache).

See also:

SQL Optimizer in the CCMS

Concepts of the Database System, SQL Optimizer Statistics