1. Add action Check optimizer statistics with the option PSAP%: All SAP tablespaces and the method E to the DBA Planning Calendar ( DB13)

This action is not one of the predefined action patterns. It must be added separately to the Planning Calendar. For more information, see Adding Actions to the DBA Planning Calendar: Oracle.

Planning Strategy:

We recommend one of two planning strategies for carrying out phases 1 and 2: Plan the check for old or missing statistics (phase 1) to run once a week (for example, on Saturdays). Plan the update of statistics (phase 2) to run after phase 1 has finished (for example, on Sundays). In this model, CBO statistics are checked and refreshed completely once a week. Updating CBO statistics is also important (even if only for individual tables) after significant imports of data (batch input, for example).

Runtime:

The check of the statistics may take several hours depending on the number and size of the tables in the database. SAPDBA -CHECKOPT places a load on the database system and should normally only be run when database load is low.

Background:

The action executes the function SAPDBA -CHECKOPT with the option PSAP% and the method E and checks the validity of all CBO statistics for all tables in the database. Tables requiring new statistics are entered in the control table DBSTATC (if not already in the control table) or flagged, if already in the table. In phase 2 ( SAPDBA -ANALYZE DBSTATCO ), the statistics are updated.

2. Add action Update optimizer statistics with the option DBSTATCO: All tables marked in DBSTATC to the DBA Planning Calendar ( DB13).

This action is not one of the predefined action patterns. It must be added separately to the Planning Calendar. For more information, see Adding Actions to the DBA Planning Calendar: Oracle.



Do not use the other options for this action.

Planning Strategy:

This action should be planned to run once a week after SAPDBA -CHECKOPT PSAP% -METHOD E (phase 1) (see above).

Runtime:

The update of the statistics may take several hours depending on the number and size of the tables in the database. SAPDBA -ANALYZE places a load on the database system and should normally only be run when database load is low.

Prioritize the statistics for critical tables by changing the priority in the control table DBSTATC . For more information, see Maintaining the DBSTATC Control Table

3. Check the results of the actions in the DBA Planning Calendar in the DBA Operations Monitor.

For more information, see Using the DBA Operations Monitor.

The CBO statistics for the tables in the DBSTATC control table will be regularly checked and updated.

At delivery, the control table contains more then 400 critical R/3 tables (these are tables that grow quickly, whose CBO statistics need to be updated regularly).

SAPDBA -CHECKOPT automatically updates the entries in the control table DBSTATC and changes the method used to create the statistics, once a table reaches a certain size. In addition, SAPDBA -CHECKOPT adds new entries as needed, when tables that are not yet in the control table grow or shrink, and therefore require new CBO statistics. SAPDBA administers the control table and automatically deletes table entries, once the size of a table has stabilized. Automatic administration of the control table provides a better overview.

In rare exceptions, you could also manually add entries to the control table DBSTATC . For more information, see Checking and Updating Optimizer Statistics for a Single Table (Oracle) and Maintaining the DBSTATC Control Table

For more information about the SAPDBA commands -CHECKOPT and –ANALYZE , see SAPDBA Command Mode .