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

Internal Rules for Update Statistics

This algorithm is used by BRCONNECT to update statistics. For more information, see Update Statistics with BRCONNECT.

BRCONNECT determines the working set of tables and indexes to be checked and updated. To do this, it uses:

Options -t|-table and -e|-exclude, as described in -f stats (these options take priority)

If the working set contains pool, cluster or other tables that have the ACTIVE flag in the DBSTATC table set to N or R, BRCONNECT immediately deletes the statistics for these tables, because they negatively affect database performance.

BRCONNECT checks statistics for the remaining tables in the working set, including tables that have the ACTIVE flag in the DBSTATC table set to A or P, as follows:

If the table has the MONITORING attribute set, BRCONNECT reads the number of inserted, deleted, and updated rows from the DBA_TAB_MODIFICATIONS table (this is available from Oracle 8.1 onwards).

Otherwise, BRCONNECT uses the standard method (see table below) to update statistics by using the unique index.

Note

BRCONNECT uses the following standard method to check and update a table's statistics:

Method and sample defined for the table in the DBSTATC table (has highest priority)

Method and sample from the options -m|-method or -s|-sample of -f stats -method (takes priority) or the stats_method and stats_sample_size parameters

Default method and sample (has lowest priority)

The following table describes the default method:

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

For example, "E P10" means that BRCONNECT takes an estimated sample using 10% of rows.

For the CH, CX, EH, and EX methods, histograms are created.

For the CI, CX, EI and EX methods, the structure of indexes is validated in addition to collecting statistics.

BRCONNECT uses the number of new rows for each table in the working set, as derived in the previous step, to see if either of the following is true:

Number of new rows is greater than or equal to number of old rows * (100 + threshold) / 100
Number of new rows is less than or equal to number of old rows * 100 / (100 + threshold)

The standard threshold is 50, but the value defined in -f stats -change or the stats_change_threshold parameter is used if specified.

BRCONNECT immediately updates statistics after checking for the following tables:

Tables where either of the conditions in the previous step is true

BRCONNECT writes the results of update statistics to the DBSTATTORA table and also, for tables with the DBSTATC history flag or usage type A, to the DBSTATHORA table.

For tables with update statistics using methods EI, EX, CI, or CX, BRCONNECT validates the structure of all associated indexes and writes the results to the DBSTATIORA table and also, for tables with the DBSTATC history flag or usage type A, to the DBSTAIHORA table.

BRCONNECT immediately deletes the statistics that it created in this procedure for tables with the ACTIVE flag set to N or R in the DBSTATC table.