SAPDBA Internal Rules for Determining Statistics (SAP Library - Database Administration (Oracle) with SAPDBA)

SAPDBA Internal Rules for Determining Statistics

Phase I ( -checkopt )

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

Step A

Determines the number of old table records from

the Oracle Data Dictionary (table DBA_TABLES ) or

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

Step B

decides which analysis method to use for analyze index .

Number of old table records (X)	Analysis method	Option
X < 6000	C
6000 < X < 60000	E	R6
60000 < X	E	P10
X unknown	E	P10

Step C

determines the number of current table records with the command analyze index estimate/ compute statistics and the analysis method from step B.

Step D

updates the control table DBSTATC (TODO flag and analysis method from step B). The TODO flag is set according to the number of current table records:

<200.000	and			Change > 10%
>200.000	and	< Number of old table records	and	Change > 10%
>200.000	and	> Number of old table records	and	Change > 100%

Tables are entered in the control table DBSTATC , if

statistics already exist and change > threshold

the analysis method (from step B) has changed

Tables are deleted from the control table DBSTATC , if

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

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

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

Phase II ( -analyze )

The analysis method and option specified in the control table are used DBSTATC 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 step B 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 R or N in the control flag ( Active)

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 control flag R are analyzed; the statistics are then deleted. The statistics results are stored in the tables DBSTATTORA or DBSTATIORA .

For all tables without statistics you can create them with the keyword NOOPTSTAT ( sapdba – analyze NOOPTSTAT ).