The table DBSTATC controls the update of statistics for the cost-based optimizer. The entries in the control table DBSTATC are administered according to the SAPDBA Internal Rules for Determining Statistics. Only change the control table under special circumstances.

This function allows you to:

• Display the control table, to check the status of the CBO update Use this, for example, to check if a statistics update is planned, the last time statistics were updated, or which method was used to update the statistics.
• Change the default method for analyzing a table and creating statistics
• Prevent SAPDBA from changing the method for creating statistics entered in the table
• Give a table higher priority for update of statistics

At delivery, the control table DBSTATC contains more than 400 entries. The tables in the control table are those (among others) that:

• Require regular updates of their CBO statistics
• Belong to the application monitor
• Are to be excluded from statistic updates
• Require a special analysis method

1. Choose Tools ® CCMS ® DB Administration ® Cost-based optimizer ® Configuration. Alternatively, call Transaction DB21.
2. Check the control table entries The table includes the following columns:

– DB object: Name of the R/3 table, whose statistics are to be checked and updated

– Use: Use of the table in the R/3 System The use types are:

A: For the R/3 application monitor (ST07) and also possibly for the optimizer

O: Only for the optimizer (default setting)

– Active: Control flag. It indicates if and how the statistics are updated or created, and in which context they are used. The possible settings are:

A (Active)
Statistics are created or updated, if the TODO flag is set (default setting)

N (No)
Statistics must not be created or updated; SAPDBA deletes existing statistics during the next analyze session

P (Priority)
Statistics that have the priority (P) are updated or created before those statistics with lower priority (A), if the TODO flag is set (otherwise, the same as A)

R (Restrictive)
Short-term statistics can be updated or created. SAPDBA deletes existing statistics during the next analyze session.

U (Unconditioned)
Statistics are updated or created during every analyze session, even if the TODO flag is not set in the control table DBSTATC.



Only use the setting N in emergencies. In terms of the CBO, the setting R is sufficient. It also allows reorganization, which reduces the amount of space required.

– TODO: When it is set (X), the statistics for the table will be updated or created during the next analyze session (phase 2). The TODO flag is automatically set by SAPDBA during the checkopt session. The field remains empty, if no update is required. To force an update of statistics for a table, set the TODO flag yourself.

– TODO chg. Date: Date when the TODO flag was set or deleted.

– Analysis method: Analysis method used to update or create the statistics: Automatically set by SAPDBA. You can manually change the setting if required. The possible analysis methods are:

E
Estimate table statistics, including the index (default setting)

EI
Estimate table statistics, check index structure

C
Calculate exact table statistics, including the index

CI
Calculate exact table statistics, check index structure

SAPDBA sets the appropriate analysis method, depending on whether a table grew or shrunk. For more information, see SAPDBA Internal Rules for Determining Statistics

3. Choose New entries, to add tables to the control table DBSTATC . You can edit the following fields:

– DB object: table name (for example, APQD )

– DB object type: database object type (default setting: 01 for table)

– Owner: Owner of the table (default setting SAPR3 )

– Database: database system used (for example, ORACLE )

– Use: see above

– Active: see above

– History: history flag: if set (X), the statistic results are archived (table DBSTATHORA )

– Analysis method: see above

– Analysis option: Specification of the analysis method (only relevant for E and EI )

Pxx
xx percent of table entries used for the analysis

Rx
x thousand of table records used for the analysis

– Cust.: Customer flag: If set (X), the settings cannot be changed by SAPDBA or other tools This allows customers to maintain their own settings for existing tables. These settings might be: do not create statistics (Active= N ), or always calculate exact statistics (analysis method= C ).

– Changed on: date when one of the settings was changed

– TODO: see above

– Changed on: see above

Save the new entry and exit the table maintenance function.



An alternative method to add entries to the control table is to choose Copy as.... Choosing Copy as... means you only have to modify and save the copied entry.

4. Choose Details to edit a selected table entry.
5. Choose Delete to delete a selected entry from the table DBSTATC .

For more information, see Updating Optimizer Statistics Automatically and Support for the Cost-Based Optimizer.