Database statistics Concept:
• 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
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 Cost Based Optimizer (CBO) requires database statistics to use the most suitable access path (for example, index access, full table scan, join types) to access the required data for SELECT, UPDATE and DELETE statements. To do this, the system uses the statistics to select the access path with the fewest expected I/O operations.
Database statistics can also be used as part of database monitoring (for example, to determine memory requirements).
• Below are some of cases where we don’t required any statistics.
No statistics are required if you are using the Rule Based Optimizer (RBO):
SAP table pools and table clusters
Other tables with exception processing in accordance with DBSTATC
If RULE hints are explicitly specified
Oracle DDIC objects (exception: Large BW systems)
When the TP transport control program is used for an import, statistics are deleted by default on the DDXTT, DDXTF, TATAF, TRBAT, TRBATC and TRBAT2 tables so that you can use the RBO.
No statistics are required for insert statements either, since the inserts carried out are based on Free list entries.
As of Oracle Release 10g, you generally require database statistics.
• Usually Statistics are saved in the Oracle Data Dictionary.
• Different types of statistics are available:
Table statistics:
Access using the DBA_TABLES view
These contain information such as the number of rows (NUM_ROWS), number of blocks used (NUM_BLOCKS), accuracy (SAMPLE_SIZE) or date of last statistics creation (LAST_ANALYZED).
Index statistics:
Access using the DBA_INDEXES view Contain information such as the size of the index tree (BLEVEL), number of leaf blocks (LEAF_BLOCKS), number of different keys (DISTINCT_KEYS), key figure for assigning index vs. table (CLUSTERING_FACTOR, see SAP Note 832343), accuracy (SAMPLE_SIZE) or date of last statistics creation (LAST_ANALYZED).
Column statistics:
Access using the DBA_TAB_COLUMNS view Contain information such as the number of different values (NUM_DISTINCT), lowest value (LOW_VALUE), highest value (HIGH_VALUE), accuracy (SAMPLE_SIZE) or date of the last statistics creation (LAST_ANALYZED)
Histogram information (optional):
Access using the DBA_TAB_HISTOGRAMS view
Contains (75 items or less by default) bucket information consisting of the bucket number (ENDPOINT_NUMBER) and bucket final value (ENDPOINT_ACTUAL_VALUE).
System statistics (Note 927295)
Access using SYS.AUX_STATS$
Contains information about the weighting of Single Block Reads, Multi Block Reads and CPU consumption. Only used in the SAP environment as of Oracle Release 10g.
• There are various ways to create database statistics:
• Oracle tools and commands:
ANALYZE command (see Oracle documentation)
DBMS_STATS package (see Oracle documentation and Note 448380)
• SAP tools:
BRCONNECT 6.10 or higher
Transaction DB20
Report RSANAORA
• Every change to the statistics can cause changed accesses to the CBO In most cases, changed access caused by new statistics have a positive effect on performance. Such changes can have negative consequences in a small number of cases. In this context, note the following recommendations.
If you have manually modified the statistics to optimize performance, as described in Note 724545, you should avoid creating any new statistics for the affected tables. However, if you still want to create new statistics, you will need to make the manual changes described in Note 724545 again.
No conclusions can be made as to how good or bad the existing statistics are, based on their age. The decisive factor affecting the quality of statistics is how well the current dataset is represented. Even “old” statistics generally still correspond to the current dataset.
• Creating statistics does not block any database objects. In theory, therefore, you can create statistics while the system is running. However, since the creation of statistics involves an additional CPU and I/O load, it should be carried out during a period of minimal workload, if possible.
To keep the statistics up-to-date, we recommend that you schedule the following at least once weekly (Oracle 9i or lower) or at least once daily (Oracle 10g or higher):
brconnect … -f stats -t all
Irrespective of the number of table lines or (in the case of activated table monitoring) the contents of DBA_TAB_MODIFICATIONS, this run checks which table requires new statistics in the first step. In the second step, new statistics are created for the tables that were determined to need them. You can use transaction DB13 to schedule these runs.
If new columns or indexes are created (even in transports), the table statistics must be updated (for instance, using transaction DB20). Other functions such as DB13, BRCONNECT without special force options, or the sole analysis of an index (whether newly created or reorganized) can cause incomplete statistics and then cause considerable performance problems. As of Oracle 10g, Oracle automatically creates index statistics when an index is created. This means that you do not need to also create the statistics manually.
In some situations, statistics are also created implicitly:
After special BW actions such as aggregate rollup or change runs; Note 555030 describes how you can deactivate this automatic creation if required. At the end of a reorganization with SAPDBA.
Note 1057511 describes an alternative to generating statistics regularly using BRCONNECT.
• Creating new statistics always involves the risk that individual accesses may be compromised. In this case, it is helpful if you can retrieve the previous statistics quickly. In addition, the following possibilities are available:
If you use DBMS_STATS to create the statistics, as of Oracle 10g, you can retrieve previous statistics from a table using DBMS_STATS.RESTORE_TABLE_STATS. Using the following command, you can restore the statistics that existed a day ago, for example:
EXEC DBMS_STATS.RESTORE_TABLE_STATS(”, ”,SYSDATE – 1);
Otherwise, you can only restore old statistics if you saved them explicitly using DBMS_STATS.EXPORT_TABLE_STATS. You can retrieve statistics that you saved using DBMS_STATS.IMPORT_TABLE_STATS. Refer to Note 448380 for further details.
• The accuracy of new statistics depends on the size of the table. For more information, refer to SAP Note 892296.
If the number of table blocks (BLOCKS) is greater than the number of rows (NUM_ROWS), the system uses the blocks instead of the rows as the basis for the sample size.