Show TOC

SQL Optimizer StatisticsLocate this document in the navigation structure

Use

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.

Table 1: 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.

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

Use one of the following database tools:

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

More Information