Support for the Cost-Based Optimizer (SAP Library - Database Administration (Oracle) with SAPDBA)

Support for the Cost-Based Optimizer

The cost-based optimizer (CBO) is an essential component for speeding up the performance of the Oracle database system. It uses statistics to examine how the database has been accessed and then define an optimum access strategy. The CBO replaced rule-based optimization in R/3 Release 4.0.

As a prerequisite for using the cost-based optimizer the parameter OPTIMIZER_MODE in the Oracle initialization profile init<SID>.ora must be set to CHOOSE .

Two-Phase Concept

SAP has introduced a two-phase concept to keep the CBO’s statistics permanently up-to-date:

Phase 1 (determine need)
SAPDBA determines the need for new statistics and enters this in the control table DBSTATC . Using SAPDBA internal rules, the analysis method and option for phase II are entered in the control table.

Phase II
SAPDBA updates the old statistics or creates new statistics, taking into account the entries in the control table DBSTATC .

Run phases I and II regularly. You can make this process fully automatic by scheduling the commands sapdba -checkopt and sapdba -analyze in the DBA Planning Calendar in CCMS.

For more information, see:

Support for the Cost-Based Optimizer

CCMS Support for the Cost-Based Optimizer

SAPDBA Internal Rules for Determining Statistics for the CBO