Updating Statistics using Optimizer Control with SAPDBA (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Updating Statistics using Optimizer Control with SAPDBA

Use

With this procedure you can display statistical information, check the requirements for new statistics, delete superfluous statistics, and create missing statistics for the Oracle database with SAPDBA. Using this procedure, you can find information on the cost-based optimizer (CBO) and control how SAPDBA updates statistics.

For more information on update statistics with SAPDBA, see Update Statistics for the Cost-Based Optimizer 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.

Procedure

To display existing statistics, choose Additional functions ® DB Optimizer control ® Show all statistics information in SAPDBA.

This display tells you how many tables or indexes of the user SAPR3 have no statistics, and how many tables entered in the control table DBSTATC have obsolete statistics ( that is, TODO in DBSTATC is set).

It also gives you information about the number of tables entered in the control table DBSTATC , the number of tables explicitly excluded from the statistics ( Activ = N ), the number of tables and indexes excluded from the CBO statistics ( Activ = R or N ), and the number of tables that are marked with a customer indicator in the control table (.. user defined values).

To check which tables need new statistics, choose Additional functions ® DB Optimizer control ® Check for new statistics in SAPDBA. Then choose the option you require:

Option	Description
a	Delta (percent) for new stat. indicator
b	Forced check (refresh marked tables)
c	Time limitation
d	SAPR3 Table (for option g)
e	Tablespace (for option h)
f	Start check for tables in control table DBSTATC
g	Start check for table
h	Start check for tables in tablespace

With these options you can:

Specify the threshold for the percentage change in the number of records for a table using option a, Delta (percent) for new stat. indicator. If the threshold is exceeded, SAPDBA sets the TODO flag in the DBSTATC table so that the statistics are refreshed during the next update statistics

Update the entries in the control table, independently of the current status using option b, Forced check. This resets all existing TODO flags.

Restrict the check to a certain period of time using option c, Time limitation. However, do not normally set a time limit.

Check statistics for the tables specified in the DBSTATC control table, for a single table, or for all the tables in a tablespace, using options f, g, and h respectively.

To delete unnecessary statistics, choose Additional functions ® DB Optimizer control ® Delete statistics excluded for R/3.

SAPDBA deletes tables not required for the R/3 System, such as pool and cluster tables or tables marked as not required in the DBSTATC control table ( Activ = N or R ).

To create missing statistics for new tables or indexes, choose Additional functions ® DB Optimizer control ® Create missing statistics for all tables and indexes.

When, for example, you add one or more new tables or indexes, you can easily create statistics for them using this menu option.