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.
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 also 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)
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 or even daily with Oracle 10g or higher).
BRCONNECT, as described here
Prerequisite
To use the CBO, make sure that the parameter OPTIMIZER_MODE is set to the Oracle standard value.
BRCONNECT performs update statistics using a two-phase approach.
BRCONNECT:
Checks each table to see if the statistics are out-of-date
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.
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 possible 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
For examples of how you can override the internal rules for update statistics, see -force with Update Statistics.