SQL Optimizer Statistics 
The database system automatically stores basic statistical information on the size of tables and indexes in the internal file directory. On request, it also collects a range of additional statistical information, which it stores in the database catalog (this operation is called updating the statistics).
The following types of statistics exist:
Table statistics: statistics about the total size of a table
Column statistics: statistics about the values in the individual table columns
The SQL optimizer requires the statistics to determine the optimum strategy for accessing the data in the database during complicated SQL statements, such as joins. If the statistics are not up-to-date, the SQL optimizer might use a strategy which is less than optimal. This can result in the system requiring longer than necessary to execute the SQL statement in question, or in unnecessarily large amounts of intermediate results being generated.
More information: How the SQL Optimizer Estimates the Costs of SQL Statements
How Is the Statistical Information Collected?
The system uses samples.
Number of Data Records (Sample Type Rows) |
Proportion of Data Records as Percentage of all Data Records in the Table (Sample Size Percent) |
Description |
|---|---|---|
0 |
0 |
The system does not update any statistics for the table. |
0 < Number of data records < Number of data records in the table The default value for the sample size is 20,000 data records. |
0 < Percentage of data records < 100 % For large tables (> 1,000,000 data records) in SAP systems, we recommend a sample size of 5 %. |
The system uses the sample size specified to estimate the statistics. |
Number of all data records in the table |
100 % |
The system calculates the statistics. This usually takes much longer than an estimation using a sample. |
With the database parameter UpdateStatSampleAlgorithm you can configure which algorithm the system uses. More information: Database Administration, Special Database Parameters
Updating the Statistics
Recommendation
Update the statistics once a week.
Also update the statistics in the following cases:
When the proportions or values in the database tables have changed significantly
After you have upgraded the database (for example, from version 7.6 to version 7.7)
When updating the statistics for a table, the system temporarily sets a shared lock on the entry for this table in the database catalog. As long as this shared lock exists, you cannot change this entry in the database catalog and cannot perform any DDL operations on the table. It is also not possible to set an exclusive lock for a table while the statistics of this table are being updated. During updating, the read costs increase and so does the number of I/O operations.
Recommendation
Update the statistics at a time of minimal work load.
You can also switch on automatic update statistics.
Recommendation
Do not switch on automatic update statistics in SAP systems. For more information about updating statistics, see SAP Note 927882.
Use one of the following database tools:
Database Studio, Database Manager CLI, or CCMS (in SAP systems only)
More Information
Database Studio, Updating the SQL Optimizer Statistics
Database Manager CLI, sql_updatestat, auto_update_statistics
Database Administration in CCMS, Scheduling the Update of the SQL Optimizer Statistics