Configuring Update Statistics in CCMS (Oracle)

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 Internal Rules for Update Statistics. Only change the control table under special circumstances.

This procedure lets you:

Display the control table, to check the settings for the CBO update statistics. Use this, for example, to check if a statistics update is planned, the last time settings were changed, or which method was used to update the statistics.
Change the default method for collecting statistics

Prerequisites

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

Caution

If you are not completely certain a change is required, do not add, delete or edit any of the entries in the control table. Deleting entries may delay updating of old CBO statistics. Adding and editing entries may unnecessarily increase runtimes for updates of CBO statistics, or reduce the effectiveness of the statistics.

End of the caution.

Procedure

Choose Administration CCMS DB Administration Cost-based optimizer Configuration . Alternatively, call transaction DB21.
Check the control table entries.
The table includes the following columns:
- Database object: Name of the SAP table, whose statistics are to be checked and updated
- Database: Database system type (for example, ORACLE or space, which means relevant for all database systems)
- Use: Use of the table in the SAP System:
  - A: For the application monitor (ST07) and also for the optimizer
  - O: Only for the optimizer (default setting)
- Active: Control flag, indicating if and how the statistics are updated or created, and in which context they are used. The possible settings are:
  - A (Active)
    Statistics are checked and updated if needed.
  - N (No)
    Statistics cannot be created. You can only set this if Use is O. BRCONNECT deletes existing statistics during the next run of brconnect -f stats.
  - P (Priority)
    Priority Statistics (P) are checked and updated if needed before statistics with lower priority (A).
  - R (Restrictive)
    Statistics can be temporarily created. Set this for pool or cluster tables if Use is A. BRCONNECT deletes statistics immediately after they have been stored in the DBSTATTORA and DBSTATIORA tables.
  - U (Unconditional)
    Statistics are updated without checking during every run of brconnect -f stats.
  - I (Ignore)
    BRCONNECT does not check whether create or update statistics is required. If statistics are present, they are not updated or deleted.
    
    Note
    
    Only set this to N in emergencies. For the CBO, setting R is sufficient.
    
    End of the note.
- TODO: When set, the statistics for the table are updated or created during the next update statistics run. To force an update of statistics for a table, set the TODO flag yourself.
- Analysis: Analysis method used to update or create the statistics. You can manually change the setting if required. The possible analysis methods are:
  - E
    Estimate table statistics including indexes
  - EH
    Estimate table statistics and create histograms
  - EI
    Estimate table statistics and verify index structure
  - EX
    Estimate table statistics, create histograms, and verify index structure
  - C
    Compute table statistics including indexes
  - CH
    Compute table statistics and create histograms
  - CI
    Compute table statistics and verify index structure
  - CX
    Compute table statistics, create histograms, and verify index structure
    
    For more information, see stats_method.
- Sample: Shows the sample size for update statistics. For more information, see stats_sample_size.
- Hist: Statistics results are archived to the DBSTATHORA and DBSTAIHORA tables.
- Cust: Suppresses warnings issued by BRCONNECT if pool or cluster tables were specified in the DBSTATC table to always have statistics. Only use this field in exceptional circumstances.

Choose New entries to add tables to the control table DBSTATC as follows:

Screen Area	Field	Description
Object information	Database object	Table name (for example, APQD)
	Object type	Database object type (default setting: 01 for table)
	Object owner	Owner of the object (default setting SAPR3). You do not normally have to enter this field
	Database type	Database system type (for example, ORACLE). You do not normally have to enter this field
	EDM Cluster	An area in the Enterprise Data Model (EDM)
Default settings	Use	This identifies the service or application for which the table entry is used: A (application monitor) For the SAP application monitor (ST07) and the optimizer O (optimizer) Only for the optimizer (standard setting)
	Active flag	See Active above.
	Changed on	Date the entry in the `DBSTATC` control table was changed.
	Analysis method	See Method above
	Sample size	Sample size for updating statistics For more information, see stats_sample_size.
	History	Statistics results are archived to the DBSTATHORA and DBSTAIHORA tables.
	Customer flag	Suppresses warnings issued by BRCONNECT if pool or cluster tables were specified in the DBSTATC table to always have statistics Only use this field in exceptional circumstances.
ToDo settings	Changed on	Date the ToDo entry in the `DBSTATC` control table was changed.
	Check	BRCONNECT checks once whether new statistics are required and updates them if necessary, even when Active is I, N, or R. When it has finished, `brconnect -f stats` resets the Check field.
	Update	BRCONNECT updates statistics once without check, even when Active is I, N, or R. When it has finished, `brconnect -f stats` resets the Update field.

Save the new entry and exit the table maintenance function.

Note Note

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

End of the note.

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