Update Statistics for the Cost-Based Optimizer with SAPDBA (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Update Statistics for the Cost-Based Optimizer with SAPDBA

Use

You can update statistics on the Oracle database using SAPDBA.

By running update statistics regularly, you make sure that the database statistics are up-to-date, so improving database performance. The Oracle cost-based optimizer (CBO) uses the statistics to optimize access paths when retrieving data for queries. If the statistics are out-of-date, the CBO might generate inappropriate access paths (such as using the wrong index), resulting in poor performance.

From Release 4.0, the CBO is a standard part of the SAP System. If statistics are available for a table, the database system uses the cost-based optimizer. Otherwise, it uses the rule-based optimizer.

Recommendation

We strongly recommend you to use the update statistics functionality in BRCONNECT instead of the SAPDBA functionality. The reason is that we will in future no longer develop the SAPDBA functions, whereas BRCONNECT functions will always be fully up-to-date.

Prerequisite

To use the CBO, make sure that the parameter OPTIMIZER_MODE in the Oracle initialization profile init<SID>.ora is set to CHOOSE.

Features

You can update statistics using one of the following methods:

DBA Planning Calendar in the Computing Center Management System (CCMS)

For more information, see Update Statistics for the Cost-Based Optimizer in CCMS (Oracle). This method uses the integrated approach described below under "SAPDBA."

Recommendation

We recommend you to use this approach instead of SAPDBA because you can schedule update statistics to run automatically at specified intervals (for example, weekly).

SAPDBA

There are the following approaches to update statistics in SAPDBA:

Integrated

This is the newer approach. It uses:

Parallel processing to reduce the time taken to update statistics
A single procedure to both check and then if necessary update statistics

For more information, see Updating Statistics using Integrated Approach with SAPDBA.

Recommendation

If you want to update statistics with SAPDBA, we recommend you to use the integrated approach instead of the two-phase approach.

Two-phase

This is the older approach. It uses separate phases to check and then if necessary update statistics. Although we recommend you not to use this approach, it is described here in case you are still using it. Be sure to run phases I and II regularly with this approach.

The following graphic shows the two-phase approach:

This graphic is explained in the accompanying text

For more information on how SAPDBA decides whether a table needs update statistics, see Update Statistics: Internal Rules with SAPDBA.