Update Statistics with BRCONNECT

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:

·        Partitioned tables, except where partitioned tables are explicitly excluded by setting the active flag in the DBSTATC table to I. For more information, see SAP Note 424243.

·        InfoCube tables for the SAP Business Information Warehouse (SAP BW)

Integration

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). The DBA Planning Calendar uses the BRCONNECT commands.

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

·        BRCONNECT, as described here

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-phase 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.

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, daily or weekly):

·         DBA Planning Calendar, as described above in "Integration."

·         A tool such as cron (UNIX) or at (Windows NT) to execute the following standard call:

 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:

·        Update statistics only for tables and indexes with missing statistics

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

·        Check and update statistics for all tables defined in the DBSTATC table

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.