Internal Rules for Update
Statistics
This algorithm is used by BRCONNECT to update statistics. For more information, see Update Statistics with BRCONNECT.
...
1. 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)
¡ stats_table and stats_exclude parameters
2. If the working set contains pool, cluster (for Oracle 9i only) 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.
3. 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 standard in Oracle 10g).
¡ Otherwise, BRCONNECT uses the standard method (see table below) to update statistics by using the unique index.
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:
Number of rows in table |
Analysis method |
Sample size |
||
|
Rows |
< 10,000 |
C |
|
10,000 <= |
Rows |
< 100,000 |
E |
P30 |
100,000 <= |
Rows |
< 1,000,000 |
E |
P10 |
1,000,000 <= |
Rows |
< 10,000,000 |
E |
P3 |
10,000,000 <= |
Rows |
|
E |
P1 |
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.
4. 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:
¡ If table MONITORING is used (standard in Oracle 10g):
§ #iold rows + # inserted rows >= # old rows * (100 + threshold) / 100
§ #iold rows + # updated rows >= # old rows * (100 + threshold) / 100
§ #iold rows - # deleted rows <= # old rows * 100 / (100 + threshold)
¡ If table MONITORING is not used:
§ 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.
5. BRCONNECT immediately updates statistics after checking for the following tables:
¡ Tables where either of the conditions in the previous step is true
¡ Tables from the DBSTATC table with either of the following values:
· ACTIVE field U
· ACTIVE field R or N and USE field A(relevant for the application monitor)
6. 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.
7. 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.
8. 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.
For more information on special rules for updating statistics of individual table partitions, see SAP Notes 744483 and 865366.