Show TOC

Update StatisticsLocate this document in the navigation structure

Use

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 Information published on SAP site.

Prerequisites

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

        Example

        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 Information published on SAP site.

    • 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 Information published on SAP site.

      Note

      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 Information published on SAP site and 628590 Information published on SAP site.

    Caution

    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.

Process

You choose one of the following approaches to update statistics: