Update Statistics
Purpose
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.
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.
Update statistics supports partitioned tables. For more information, see SAP Note 424243.
Prerequisites
For more information on how BRCONNECT performs update statistics, see
Update Statistics with BRCONNECT.You can improve the performance of update statistics as follows:
You can implement this with BRCONNECT or DBMS_STATS:
You can implement BRCONNECT parallel processing as follows:
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.
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.
You can use both the above types of parallel processing in the same BRCONNECT run.
With this feature, the Oracle database system constantly updates information concerning record counts for database tables, entering the results in the
For more information, see SAP Note 408527.
Process Flow
You choose one of the following approaches to update statistics:
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 weekly.
We strongly recommend you to use the
The reason is that we are no longer developing the SAPDBA functions, whereas BRCONNECT functions will always be fully up-to-date.