Entering content frameFunction documentation Update Statistics with BRCONNECT Locate the document in its SAP Library structure

Use

You can use this BRCONNECT function to update the statistics on the Oracle database for the cost-based optimizer.

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.

BRCONNECT supports update statistics for the following:

Recommendation

We strongly recommend you to use this BRCONNECT function rather than the equivalent 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.

Integration

You can update statistics using one of the following methods:

For more information, see Structure linkUpdate Statistics for the Cost-Based Optimizer in CCMS (Oracle). The DBA Planning Calendar uses the BRCONNECT commands.

Recommendation

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

Prerequisite

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

Features

BRCONNECT performs update statistics using a two-step approach.

BRCONNECT:

  1. Checks each table to see if the statistics are out-of-date
  2. If required, updates the statistics on the table immediately after the check

For more information about how update statistics works, see Internal Rules for Update Statistics.

Activities

You can influence how update statistics works by using the -force options. For more information, see -f stats.

Recommendation

Unless you have special requirements, we recommend you to perform the standard update statistics, using one of the following tools to schedule it on a regular basis (for example, weekly):

brconnect -u / -c -f stats -t all

This is also adequate after an upgrade of the database or SAP System. It runs using the OPS$ user without operator intervention.

The following are also standard commands that you can use to update statistics:

brconnect -u / -c -f stats -t missing

brconnect -u / -c -f stats -t dbstatc_tab

Example

For examples of how you can override the internal rules for update statistics, see -force with Update Statistics.

Leaving content frame