Updating Statistics in the DBA Planning Calendar (Oracle) 

Use

You can use the DBA Planning Calendar in the Computing Center Management System (CCMS) to schedule check and update statistics for the cost-based optimizer in the Oracle database.

All tables in the database are then automatically checked and updated on a regular basis.

The action that you plan with this procedure now includes both check statistics and update statistics in a single integrated phase. This is equivalent to the two-phase approach previously used. For more information, see Update Statistics with BRCONNECT.

Prerequisites

·        You are ready to use CCMS.

·        You know how to use the DBA Planning Calendar.

For more information about scheduling an action (for example, update statistics) in the DBA Planning Calendar, see Adding Actions to DBA Planning Calendar (Oracle). Update statistics is one of the predefined action patterns in the DBA Planning Calendar, so you do not need to add it separately.

·        You can prioritize the statistics for critical tables by changing entries in the control table DBSTATC. For more information, see Configuring Update Statistics in CCMS (Oracle).

·        If you want to update statistics for a single table or a group of tables, instead of for all tables (as in this procedure), see:

¡        Updating Statistics for a Single Table (Oracle)

¡        Updating Statistics for Groups of Tables in CCMS (Oracle)

Procedure

...

       1.      Choose Administration ® CCMS ® DB Administration ® DBA Planning Calendar. Alternatively, call transaction DB13.

       2.      Choose the day when you want the statistics to be checked and updated.

       3.      Choose Create action.

       4.      Select Check and update optimizer statistics to check and update statistics.

       5.      Enter data as required in the fields Start Time, Period (weeks), and Calendar. For more information, see Adding Actions to DBA Planning Calendar (Oracle).

We recommend you to schedule this action every week, preferably at a time when the database is not under heavy load. It might take several hours to complete and can significantly load the system.

       6.      Choose Continue.

Result

The check and update statistics are now scheduled. They are executed at the scheduled date and time. For more information about looking at the results, see Checking the Results of Actions in the DBA Planning Calendar.