By running update statistics regularly, you make sure that the database statistics are up-to-date, so improving database performance. You can schedule the checks in the Computing Center Management System (CCMS) of the SAP System, using the DBA Planning Calendar.
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.
CBO is a standard part of the SAP database. If statistics are available for a table, the database system uses the cost-based optimizer. Otherwise, it uses the rule-based optimizer. Since from Oracle 10g the rule-based optimizer is no longer supported, in this case auto-sampling is performed.
Update statistics supports partitioned tables. For more information, see SAP Note 424243 .
For more information about how BRCONNECT performs update statistics, see Update Statistics with BRCONNECT.
You can improve the performance of update statistics as follows:
Parallel processing
You can implement this with BRCONNECT or DBMS_STATS:
BRCONNECT
You can implement BRCONNECT parallel processing as follows:
Command call brconnect -f stats -p <number of threads>
Parameter stats_parallel_degree in the init<DBSID>.sap file
Here are some examples:
brconnect -c -u / -f stats -p 4
stats_parallel_degree = 4
Each of these sets the number of threads - that is, the degree of parallelism - to 4. The default is 1, which means that update statistics runs in non-parallel, that is, serial mode. In either case, each table is processed by a single thread. For more information, see SAP Note 403713 .
DBMS_STATS Package (standard from Oracle 10g)
There is a parallel processing option in this package that considerably reduces run times for very large tables (that is, several hundred GB). Each table can be processed by several threads.
For more information, see SAP Note 408532 .
BRCONNECT calls DBMS_STATS to create the statistics for individual partitions with the granularity PARTITION. This optimizes and significantly accelerates processing for large partitioned tables.
To increase the accuracy of statistics for partitioned tables, the sample size is doubled ( <standard sample size> * 2) and DBMS_STATS recreates the global statistics for the entire table if half the threshold is exceeded ( <change_threshold> / 2).
You can use both the above types of parallel processing in the same BRCONNECT run.
Oracle table monitoring
With this feature, the Oracle database system constantly updates information concerning record counts for database tables, entering the results in the DBA_TAB_MODIFICATIONS table. It only takes BRCONNECT a short time to read this results table and determine whether update statistics is required for a given database table.
The activation of the table monitoring attribute is now automated with the monit option as follows:
brconnect -u / -c -f stats -t all -f monit
The advantages of this are:
For tables with the monitoring attribute activated by BRCONNECT, new statistics are collected at the same time. This gives Oracle a good basis for collecting information about changes to the table.
Tables that are dynamically created by the SAP system, such as in SAP BW, are automatically (that is, without any manual intervention) processed by BRCONNECT on the next processing run.
For more information, see brconnect -f stats and SAP Notes 408527 and 628590 .
As of Oracle 10g, table monitoring is active by default.
BRCONNECT support for system and Oracle dictionary statistics
You can use BRCONNECT to call the DBMS_STATS package to collect system statistics, as follows:
brconnect -u system /<password> -c -f stats -t SYSTEM_STATS
Or you can use BRCONNECT to collect Oracle dictionary statistics as follows:
brconnect -u / -c -f stats -t ORADICT_STATS
For more information, see brconnect -f stats.
You can also determine the interval in minutes for collecting system statistics using the BRCONNECT option -i or the init<SID>.sap parameter stats_system_interval.
You choose one of the following approaches to update statistics:
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 in production operation because you can easily schedule update statistics to run automatically at regular intervals. We recommend you to update statistics at least weekly or even daily for Oracle 10g or higher.