UPDATE STATISTICS METHODS

To update statistics, BRCONNECT normally:
1. Deletes statistics for certain tables, where the statistics are misleading, including pool and cluster tables
(up to Oracle 9i) and objects where the ACTIVE field in the DBSTATC table is set to N.
2. Checks each table to see if the statistics are out-of-date or non-existent.
3. If required, updates the statistics on the table immediately after the check
BRCONNECT uses the DBSTATC table as a control table. It performs the check and update (the final two
steps above) in a single phase. The section "BRCONNECT Option -force with Update Statistics" [Page 19]
describes how you can influence update statistics.
CONFIGURATION
The DBSTATC control table controls update statistics. In a new installation of the SAP System, the DBSTATC
table includes entries for the most important tables, that is, large and dynamic tables. The DBSTATC table
influences how update statistics is executed, but no longer contains the intermediate results (as in earlier
releases).
 You can create entries in the DBSTATC table for either of the following reasons:
 To specify non-standard analysis method and sample size
To create temporary statistics for the SAP application monitor for tables that normally have no statistics, such
as pool and cluster tables (up to Oracle 9i)
You can customize update statistics by editing this table using transaction DB21, in which you can change
the following (the field names are given in brackets):
Object Information
 Database object (DBOBJ)
Table name (for example, APQD)
 Object type (DOTYP)
Database object type (standard setting for tables 01)
 Object owner (OBJOW)
Table owner (standard setting SAPS3/SAPSR3 or SAP<SAPSID>)
 Database (DBTYP)
Database system used (for example, ORACLE, or blank if database-independent)
Default Settings
 Use (VWTYP)
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 (ACTIV)
This indicates if and how the statistics will be updated and in which context they will be used. Possible
settings are:
 A (Active)
Statistics are checked and updated if needed.
 N (No) (up to Oracle 9i, later only in exceptional circumstances)
Statistics cannot be created. You can only set this if Use is O. BRCONNECT deletes existing statistics
during 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)
Certain statistics values are externally determined. Other values follow the standard check and update
procedure (as with value A , see above). For more information, see SAP Note 1020260.
 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.
 Changed on (AEDAT)
Date when one of the settings was changed. This field is automatically set by transaction DB21.
 Analysis method (AMETH)
This is the analysis method used to create or update statistics. 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
Caution: this locks indexes.
 EX
Estimate table statistics, create histograms, and verify index structure
Caution: this locks indexes.
 C
Compute table statistics including indexes
 CH
Compute table statistics and create histograms
 CI
Compute table statistics and verify index structure
Caution: this locks indexes.
 CX
Compute table statistics, create histograms, and verify index structure
Caution: this locks indexes.
 A
Collect statistics using auto-sampling, including indexes
 AH
Collect statistics using auto-sampling, including indexes, and create histograms
 AI
Collect statistics using auto-sampling, including indexes, and verify index structure
Caution: this locks indexes.
 AX
Collect statistics using auto-sampling, including indexes, create histograms, and verify index structure
Caution: this locks indexes.
 Analysis Option (OPTIO)
Specification of sample size (only relevant to E, EH , EI, and EX)
 P<p>
<p> percent of the table rows are used for the analysis (1 - 100, .001 - .999)

 R<r>
<r> thousand table rows are used for the analysis (1 – 9999)
 History (HISTO)
If this is selected, the statistic results are archived to the DBSTATHORA and DBSTAIHORA tables.
 Customer (PLAND)
Use this field to suppress warnings issued by BRCONNECT if pool or cluster tables were specified in the
DBSTATC table to always have statistics (up to Oracle 9i). Only use this field in exceptional
circumstances.
TODO Settings
 Check (DURAT)
If this is selected, BRCONNECT checks once whether new statistics are required and updates them if
necessary, even when Analysis method is I or N. If required, you can manually change the entries in
the DBSTATC control table with transaction DB21. When it has finished, brconnect -f stats resets
the Check field.
 Update (TOBDO)
If this is selected, BRCONNECT updates statistics once without check even when Analysis method is
I or N. When it has finished, brconnect -f stats resets the Update field.
 Changed on (TDDAT)
Date on which the Check or Update fields were set. This field is automatically set by transaction DB21.
BRCONNECT does not add or delete rows to or from the DBSTATC table and does not change the analysis
method and sample size. The DBSTATC table is used purely as a control table.

INTERNAL RULES FOR UPDATE STATISTICS
This algorithm is used by BRCONNECT to update statistics.
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 set with brconnect -f stats (these options take
priority)
 stats_table and stats_exclude parameters from init<DBSID>.sap
2. If the working set contains pool, cluster (up to Oracle 9i) or other tables that have the ACTIVE flag in the
DBSTATC table set to N, BRCONNECT immediately deletes the statistics for these tables if available,
because they can 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, P, or R as follows:
 If the table has the MONITORING attribute set (standard since Oracle 10g), BRCONNECT reads the
number of inserted, deleted, and updated rows from the DBA_TAB_MODIFICATIONS table.
 Otherwise, BRCONNECT uses the standard method (see table below) to update statistics on a 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_size of -f
stats (takes priority) or the stats_method or stats_sample_size parameter
 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.
The sample size is doubled if single table partitions are chosen for update 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:
 For tables without the MONITORING attribute:
o <new_number_rows> >= <old_number_rows> * (100 + <threshold>) / 100
o <new_number_rows> <= <old_number_rows> * 100 / (100 + <threshold>)
 For tables with the MONITORING attribute (see SAP Note 744483):
o <old_number_rows> + <number_of_inserted_rows> >= <old_number_rows> * (100 + <threshold>)
/ 100
o <old_number_rows> + <number_of_updated_rows> >= <old_number_rows> * (100 + <threshold>)
/ 100
o <old_number_rows> + <number_of_inserted_rows> >= (<old_number_rows> +
<number_of_inserted_rows> - <number_of_deleted_rows>) * (100 + <threshold>) / 100
The standard threshold is 50, but the value defined in the -c|-change option of -f stats or the
stats_change_threshold parameter is used if specified.
The threshold is automatically halved for partitioned tables, if they are being checked as complete tables
(not for single partitions).
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 the value ACTIVE field U
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 EX, AI, or AX, 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.