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)
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
To use the CBO, make sure that the parameter OPTIMIZER_MODE in the Oracle initialization profile init<DBSID>.ora is set to CHOOSE.
BRCONNECT performs update statistics using a two-phase approach.
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.
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