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 - HowTo - Execute Update Statistics

 

 

Moderator: Birgit Malik

WIKI Space Editor: Thiago Lüttig

Details

UPDATE STATISTICS using Database Studio

You can execute the UPDATE STATISTICS <table_name>, UPDATE STATISTICS COLUMN <column_name> and UPDATE STAT AS PER SYSTEM TABLE commands (see Methods for Determining Statistics) with an SQL editor. See also 927882 FAQ.

back to top

UPDATE STATISTICS using Database Manager CLI (DBMCLI)

The following DBM command can be used for UPDATE STATISTICS: sql_updatestat

sql_updatestat [<table_or_column_spec>]

<table_or_column_spec> -> Table or column specification
for possible arguments and keywords see Methods for Determining Statistics.

back to top

Automatic UPDATE STATISTICS

You can switch on/off automatic update of statistics information automatically when creating a database. See SQL Optimizer Statistics.

back to top

UPDATE STATISTICS using XPU (only SAP MaxDB versions < 7.6)

With program XPU several tasks can simultaneously carry out statistical maintenance on several tables. You can specify the number of parallel tables using the <processes> option. A sensible choice would be: number of CPUs * 3 (max. 50).

To be able to use XPU, the connection information (database name, user, password, ...) must be stored by means of XUSER or SQLOPT.
If the statistics are updated by means of XPU, a log file named updcol.prt is generated in the call directory.
XPU can execute two types of Update Statistics:
a. Unconditional UPDATE STATISTICS:
xpu -s -u <processes>
b. Conditional UPDATE STATISTICS:
xpu -s <processes>
You can use the addition -e [<number>] to also generate statistics using XPU on the basis of estimated values. If you do not specify a <number>, the sample size is fetched from system table tables. If you specify <number> < 100, Update Statistics is executed with the corresponding PERCENT number. For larger values Update Statistics is executed with the corresponding ROWS number.
If you execute an unconditional UPDATE STATISTICS, an UPDATE STATISTICS <table> is executed for all tables (fetched from system table tables) and an UPDATE STATISTICS COLUMN is executed for all key and index columns (fetched from system table columns).
For conditional UPDATE STATISTICS the statistical values are only updated for tables whose size has changed by more than 60% (comparison of the actual size from table sysdba.pages with the last determined statistical value in table sqd$tpages). Here, an UPDATE STATISTICS <table> and an UPDATE STATISTICS COLUMN are also executed.
As of SAP MaxDB version 7.6 the XPU tool is no longer supported.

back to top

UPDATE STATISTICS in the SAP System

In the SAP system, the statistics can be updated by means of transactions DB13 or DB13C (for MCOD systems and SAP MaxDB databases of other (SAP) systems) or DBACOCKPIT (as of SAP Release 7.00). In doing so the statistical values are estimated by SAMPLE values from table TABLES.
See Scheduling Updates of the SQL Optimizer Statistics, 927882 FAQ, 1229738 Parallel UPDATE STATISTICS,  873286 Export/Import statistics, 958182 Update Statistics and table DBSTATC, 808060 Change UPDATE STATISTICS SAMPLE value

The SAP system no longer calls up the XPU tool (UPDATE STATISTICS using XPU. The UPDATE STATISTICS of DB13 is executed via the SAP database interface (DBSL). In doing so, the corresponding tables are determined (UPD_UNCOND or UPD_COND) and their statistics are updated. In addition, the column statistics of the indexed columns as well as the key columns are brought up-to-date for these tables.
Transactions DB13, DB13C and DBACOCKPIT will probably also offer the Update Statistics as per System Table as of the following Basis SPs:
4.6C Basis SP 52,
4.6D Basis SP 43,
6.10 Basis SP 47,
6.20 Basis SP 59,
6.40 Basis SP 17,
7.00 Basis SP 08.
back to top

As of the following Releases it is also possible to trigger the updating of the statistical values for individual tables in transaction DB50 -> Problem Analysis -> Tables/Views/Synonyms, Optimizer Statistics tab:
4.6C Basis SP44,
4.6D Basis SP33,
6.10 Basis SP34,
6.20 Basis SP25,
6.40 and 7.00 from the beginning.
Statistics can also be estimated in DB50. This procedure is especially useful if you find out that the statistical values are incorrect for individual tables and exactly these have to be updated. You can specify the sample size for the individual Update Statistics execution but you don't set it permanently in DB50 -> Tables/Indexes/Synonym -> Optimizerstatistics.

In the table/view information only those column statistics are updated, which have been indexed or on which an Update Statistics Column has already been executed once before.
The cardinality of the column is of utmost importance with regard to the decision whether an index across a new column or several new columns makes sense. Since there are usually no statistics available for these columns, you can specifically create the column statistics in transaction DB50  -> Tools -> Database Manager (CLI).

Example:
sql_connect MONA,RED
sql_execute UPDATE STAT COLUMN (ZIP,ADDRESS) FOR MONA.CUSTOMER ESTIMATE SAMPLE 20000 ROWS
sql_execute commit
sql_release


Representative, up-to-date statistics are especially relevant in the BW system because the statistics are required for the join transitions and the BW environment works almost exclusively with joins. 

back to top

Loading and Unloading Statistical Values

The runtime of UPDATE STATISTICS increases when more precise statistics are generated (increase of sample value). Since you should run UPDATE STATISTICS at a time when the system load is low in order to avoid performance problems, finding a sufficiently large maintenance window might be a problem in production SAP systems environments.
In these cases, you can create a system copy and run UPDATE STATISTICS there. You can load the statistics generated in the system copy to the production system be means of the loader and activate them there. See also 873286 Unloading/loading SQL optimization statistics data

back to top