CCMS Support for the Cost-Based Optimizer: Oracle (SAP Library - Computing Center Management System (BC-CCM))

CCMS Support for the Cost-Based Optimizer: Oracle

From Release 4.0, the cost-based optimizer (CBO) is a standard part of the R/3 System. If statistics are available for a table, the database system uses the cost-based optimizer. Otherwise, it uses the rule-based optimizer.

The CCMS includes the follow functions for the cost-based optimizer:

Automatically recognize if table statistics need to be updated:
To check the database for tables with old CBO statistics, call the DBA Planning Calendar (Transaction DB13) and use the Check optimizer statistics function (for example: SAPDBA -CHECKOPT PSAP% -METHOD E ). This operation is the first step required to update CBO statistics.

See Updating CBO Statistics Automatically

Update Optimizer Statistics Automatically:
To automatically update CBO statistics, call the DBA Planning Calendar (Transaction DB13) and use the Update optimizer statistics function (for example, SAPDBA -ANALYZE DBSTATCO ) This operation is the second step required to update CBO statistics.

See Updating CBO Statistics Automatically

Update Statistics for a Single Table:
Use Edit DB Statistics (Transaction DB20), to create new CBO statistics, or update or delete statistics, for a particular table.

See: Checking and Updating Statistics for a Single Table: Oracle

Execute Standard CBO Operations:
Use Standard operations (Transaction DB20) to:
– Analyze the need for statistics for all database tables
– Create statistics according to control table DBSTATC
– Create statistics for tables without statistics
– Delete all illegal statistics

See: Standard Operations for CBO Statistics

Maintain the Optimizer Control Table DBSTATC:
Use DB Optimizer Control (Transaction DB21) to configure automatic updates of CBO statistics with the control table DBSTATC . Once you schedule the recommended CBO actions in the DBA Planning Calendar, the R/3 System will administer the control table on its own. Therefore, do not add, delete, or change table entries unless you are aware of the consequences of doing so.

See: Maintaining DBSTATC Control Table

CBO statistics, installations, and release upgrades:
Generating CBO statistics for all tables in the R/3 system is an activity you will need to perform in association with an installation or release upgrade. These actions are described in the relevant installation or upgrade guide.