Update Statistics: Internal Rules with SAPDBA (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Update Statistics: Internal Rules with SAPDBA

This section describes the rules used by the cost-based optimizer (CBO) to update statistics for the Oracle database. The rules apply to both the older two-phase approach and the newer integrated approach to update statistics with SAPDBA.

Recommendation

We strongly recommend you to use the update statistics functionality in BRCONNECT instead of the SAPDBA functionality. The reason is that we will in future no longer develop the SAPDBA functions, whereas BRCONNECT functions will always be fully up-to-date.

Phase I: sapdba -checkopt

Phase I is based on the analyze index command, which you can use to quickly display the current number of table records.

SAPDBA determines the number of old table records from one of the following:

The Oracle Data Dictionary (table DBA_TABLES and DBA_INDEXES )

The DBSTATTORA table if there are no entries in the Oracle Data Dictionary

SAPDBA decides on the analysis method and sample option to use for analyze index in the next step as follows:

Number of old table records	Analysis method	Sample option
Records < 6,000	C
6,000 < Records < 60,000	E	P30
60,000 < Records < 600,000	E	P10
600,000 < Records < 6,000,000	E	P3
Records > 6,000,000	E	P1

Analysis method C means compute the statistics exactly. Analysis method E means estimate the statistics using the sample option specified.

For example, "E P30" means that SAPDBA takes an estimated sample using 30% of rows.

SAPDBA determines the number of current table records with the command analyze index estimate/ compute statistics using the analysis method and sample option from the previous step.

SAPDBA updates the DBSTATC control table. It sets the analysis method and sample option based on the results of the second step. It sets the TODO flag if one of the following is true:

The current number of table records is less than 200,000 and the number has changed by at least 10%.
The current number of table records is greater than 200,000, less than the number of old table records, and the number has decreased by at least 10%.
The current number of table records is greater than 200,000, greater than the number of old table records, and the number has increased by at least 100%.

Note

If the customer flag is set, SAPDBA does not change the settings in the DBSTATC control table.

SAPDBA enters tables in the DBSTATC control table if either of the following is true:

Statistics already exist and the change is greater than the level required to set the TODO flag defined in this step.

The analysis method from the second step has changed.

SAPDBA enters the appropriate analysis methods and options in the DBSTATC control table according to the rules from the second step. SAPDBA uses the up-to-date number of table records from the third step when doing this.

SAPDBA deletes tables from the DBSTATC control table if either of the following is true:

The date in the field TODO chg.date is older than 30 days, the type of usage is set to O , the customer flag is not set, and the control flag is set to A .

The table no longer exists in the database.

Phase II: sapdba -analyze

SAPDBA uses the analysis method and option specified in the DBSTATC control table if they create more precise statistics than those specified in the command option, or if the customer flag is set. If there is no entry in the control table for a table, then the analysis method and option from the second step in phase I are used.

SAPDBA create statistics for all tables that require them:

Implicitly, with the command sapdba –analyze DBSTATC (DBSTATCO, DBSTATCA_TAB, DBSTATCAA_TAB) .

Explicitly, with the command sapdba –analyze NOOPTSTAT .

For each checkopt and analyze run, SAPDBA implicitly deletes the statistics of

Tables with the ACTIV flag set to R or N

Cluster tables and pooled tables

SAPDBA explicitly deletes statistics for each delete run.

These statistics are also used by applications that need space information (such as the R/3 Application Monitor). When statistics are created, all tables with the ACTIV flag set to R are analyzed; the statistics are then deleted. The statistics results are stored in the tables DBSTATTORA or DBSTATIORA .