Function documentationSQL Optimizer Statistics Locate this document in the navigation structure

 

The database system automatically saves basic statistical information on the size of tables and indexes in the internal file directory. On request, the database system also determines a range of additional statistical information, which it saves in the database catalog (this operation is called updating the statistics).

The following kinds 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.

Activities

How does the system calculate the statistics?

The system uses a sample to estimate the statistics for a table.

Sample Type and Size for Updating the SQL Optimizer Statistics

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.

Determining a range of additional statistical information can be requested by kernel automatism, by database procedure SYSCHECKSTATISTICS or by database administrators. More information: Defining System Tables, SYSUPDSTATWANTED.

With the database parameter UpdateStatSampleAlgorithm you can configure which algorithm the system uses for the estimation. More information: Database Administration, Special Database Parameters

Updating statistics

Recommendation Recommendation

We recommend that you update the statistics once a week. You should also update the statistics in the following cases:

  • If the proportions or values in the database tables have changed significantly

  • If you have upgraded the database (for example, from version 7.6 to version 7.7)

End of the recommendation.

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 Recommendation

We recommend that you update the statistics at a time of minimal work load.

End of the recommendation.

You can also switch on automatic update statistics.

Recommendation Recommendation

Do not switch on automatic update statistics in SAP systems. For more information about updating statistics, see SAP Note 927882.

End of the recommendation.

Use one of the following database tools:

Database Studio, Database Manager CLI, or CCMS (in SAP systems only)

More Information