Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

 

SAP® MaxDB - Statistics

 

The significance of the statistics for the SAP MaxDB SQL optimizer is described in detail in this section.

Moderator: Birgit Malik

WIKI Space Editor: Thiago Lüttig

 

The SQL optimizer needs statistics of the size of the tables and indexes and the cardinality of column value to determine the ideal access strategy for join statements.

Details

Introduction

Furthermore, data regarding the cardinality of all columns in the WHERE condition is required during performance analysis, for example, to estimate the chances of successfully using an alternative index. In the SAP environment, you can use transaction DB05 to determine this data for small table, whereby additional information is gained by means of the value distribution (equal or unequal distribution). For a first, quick analysis, the data determined using SAP MaxDB statistics determination often suffices.  If this statistics are not up-to-date, this can lead to the use of a non-ideal strategy under certain conditions. This has the effect that the execution of the corresponding SQL statement takes longer than necessary and that unnecessarily large result set have to be generated under certain conditions. Hence, you should update the statistics at regular intervals (for example, once a week on the weekend) when the system load is low.

back to top

You can determine how current your SQL optimizer statistics are by means of system table TABLES. The date of the last UPDATE STATISTICS for the affected table (column: updstatdate) is relevant. Particularity: If statistics have been loaded from another system (note 873286 resp. HowTo - Execute Update Statistics -> Loading and Unloading Statistical Values ), then the date of the statistics is not updated in any of the following tables.

SELECT * FROM domain.tables WHERE owner = <owner> WHERE tablename = <table_name>

Example: SELECT tablename, UPDSTATDATE, UPDSTATTIME FROM domain.tables WHERE tablename = 'ZZTELE'

Tablename

Updstatdate

Updstattime

ZZTELE

2003-08-19

14:30:31

In this example, the last UPDATE STATISTICS for table zztele was carried out on 19-08-2003 at 14:30:31. You can determine the date of the last UPDATE STATISTICS COLUMN by means of system table SYSUPDSTATLOG.

Handy Hint

To be able to access table SYSUPDSTATLOG, you have to log on to SQL editor as the database system administrator.

SELECT * FROM sysdba.sysupdstatlog WHERE owner = <owner> WHERE tablename = <table_name>

Example (in SQL mode internal): SELECT tablename, columnname, executed_at FROM sysdba.sysupdstatlog WHERE owner = 'SAPE30' AND tablename = 'ZZTELE' AND columnname != ' '

Tablename

Columnname

Executed_at

ZZTELE

CODE

2003-08-19 14:26:13.068360

ZZTELE

NAME

2003-08-19 14:26:13.068360

ZZTELE

CITY

2003-08-19 14:26:13.068360

ZZTELE

ZIP

2003-08-19 14:26:13.068360

ZZTELE

STR

2003-08-19 14:26:13.068360

back to top

Determining the Time of last UPDATE STATISTICS (Indexes)

You can also determine the UPDATE STATISTICS date for the indexes using system table SYSUPDSTATLOG.

Example (in SQL mode internal): SELECT tablename, indexname, executed_at FROM sysdba.sysupdstatlog WHERE owner = 'SAPE30' AND tablename = 'ZZTELE' AND indexname != ' '

Tablename

Indexname

Executed_at

ZZTELE

CODE

2003-08-19 14:29:13.05682

ZZTELE

ZZTELE~1

2003-08-19 14:29:13.05682

ZZTELE

ZZTELE~2

2003-08-19 14:29:13.05682

back to top

Determining the Time of last UPDATE STATISTICS using SAP System

Use SAP MaxDB CCMS transaction DBACOCKPIT/DB50. Choose Tables/Views/Synonyms. Enter the object name and choose Execute. The date of the last statistics generation is displayed on the Properties tab.

 back to top

Using SAP MaxDB System Tables

You can use system table OPTIMIZERSTATISTICS to determine the statistics of a table, which the SQL optimizer uses for the strategy search.

SELECT * FROM optimizerstatistics WHERE owner = <owner> AND tablename = <table_name>

Example: SELECT tablename, indexname, columnname, distinctvalues, pagecount FROM optimizerstatistics WHERE owner = 'SAPE30' AND tablename = 'ZZTELE'

Tablename

Indexname

Columnname

distinct Values

Pagecount

ZZTELE

?

CODE

1

?

ZZTELE

?

NAME

3985

?

ZZTELE

?

CITY

1

?

ZZTELE

?

ZIP

16618

?

ZZTELE

?

STR

6

?

ZZTELE

?

FIRSTNAME

2380

?

ZZTELE

CODE

?

?

1155

ZZTELE

ZZTELE~1

?

?

1165

ZZTELE

ZZTELE~2

?

?

1112

ZZTELE

?

TABLE
STATISTICS

114477

1902

In the result column in system table OPTIMIZERSTATISTICS tablename you see the table name for which the SQL optimizer statistics have been determined. You can see the index name in the indexname column. A '?' in the indexname column shows that you are not dealing with an index but with individual columns of a table for which the column statistics have been generated. Column names are shown in the columnname column. If a column of a table is not displayed this means that no column statistics have been generated for this column. The columns distinctvalues and pagecount show the actual statistical information that is analyzed by the SQL optimizer. Performance analysts must take a closer look at these values. The distinct values provide information on the selectivity of the individual table columns. The more selective a table column is, the more sense it make to access it via an index.

  • that consists of this column (single index)
  • or that contains this column (multiple index).

 

The pagecount column provides information on the size of an index or the table (table statistics) in 8 KB pages. The SQL optimizer uses this information to decide whether it makes more sense to use an index for access or access the table directly. If a large number of index pages have to be read, it is often preferable to use a different strategy and not use this index.

back to top

Using SAP System

Use SAP MaxDB CCMS transaction DBACOCKPIT/DB50. Choose Tables/Views/Synonyms. Enter the object name and choose Execute. You can see the values of the SQL optimizer statistics on the Optimizer Statistics tab page.

back to top

Lock for UPDATE STATISTICS

The lock behavior when updating the SQL optimizer statistics was changed as of SAP MaxDB version 7.5.00 Build 31. The following applies up to SAP MaxDB version 7.5.00 build 30: During Update Statistics, the statistical record of the affected table is locked exclusively. This can also affect SELECT statements because the statistical information is also read when a SELECT is executed. However, this only affects open sessions and sessions which have not yet read the table in question. Sessions during which the respective table has already been accessed still have the required statistical data in the cache. This exclusive lock is applied irrespective of the type of UPDATE STATISTICS; the only thing that varies is the duration of the lock.

The following applies as of SAP MaxDB version 7.5.00 Build 31 and in SAP MaxDB version 7.6: During UPDATW STATISTICS a read lock (share lock) is set on the statistical record of the affected table. Hence, no DDL commands are possible on the table, but SELECT statements are not affected. This read lock is applied irrespective of the type of Update Statistics; the only thing that varies is the duration of the lock.

back to top