SAP Updating Optimizer Statistics Automatically: Oracle

Updating Optimizer Statistics Automatically: Oracle
Here is how to update cost-based optimizer (CBO) statistic using the DBA Planning Calendar.
Note that updating CBO statistics is only partially supported by the Planning Calendar. You will need to carry out step 1 in the procedure below manually, using the SAPDBA program. Step 2 is then automatically carried out by the Planning Calendar. Fully automatic support will be available with a later Release. Procedure


1. Add the action Check optimizer statistics with the option PSAP% - All SAP tablespaces in the DBA Planning Calendar (transaction DB13).
This action is not part of the pre-defined action patterns. You must therefore add it to the planning calendar yourself. Adding the action is a one-time task. For help, see Adding Actions to the Planning Calendar: Oracle .
When and how often to run: SAP recommends one of two models for carrying out steps 1 and 2:
– Optimum model: Run step 1 completely through once a week on the weekend. After step 1 completes, run step 2 to completion, also on the weekend. In this model, the CBO statistics are checked and refreshed completely once a week.
– Fallback model: Run step 1 for one hour every night. Use the time limit option described in BC SAP Database Administration: ORACLE . Thereafter, run step 2 for an hour every night. In this model, the system works its way through the DBSTATC over several days.
Use this model if there is not enough free time on the weekends to refresh the CBO statistics completely. Plan to run steps 1 and 2 after every reorganization (and never before). A reorganization invalidates optimizer statistics. Updating CBO statistics is also important (even if only for individual tables) after significant imports of data (batch input, for example).
Run time: This action can take several hours, depending upon the number and size of the tables in the database. Schedule this action well before the ANALYZE action is step 2 is scheduled to start
If necessary, you can run SAPDBA -CHECKOPT with a time limitation (see above).
Background: This action carries out the SAPDBA CHECKOPT function with the option PSAP%. The action checks the validity of the CBO statistics of all tables in your Oracle database. Tables that require new statistics are added to the worklist that is kept in the DBSTATC table. Step 2 in this procedure, the SAPDBA ANALYZE DBSTATC0 run, then refreshes these statistics.
SAPDBA -CHECKOPT places some additional workload on the database system. You can run it during normal production use of the R/3 System. We recommend, however, that you run it when the system is free of other work.
2. Add the action Create new optimizer/space statistics) with the option DBSTATC0: All tables marked in DBSTATC in the DBA Planning Calendar (Transaction DB13).
This action is not part of the pre-defined action patterns. You must therefore add it to the planning calendar yourself. Adding the action is a one-time task. For help, see Adding Actions to the Planning Calendar: Oracle .
Using the other options is not recommended; see the SAPDBA -ANALYZE command description for more information.
When and how often to run: This action should run weekly after the SAPDBA -CHECKOPT DBSTATC in step 1. See the models in step 1 for information on scheduling.
For other guidelines, see also step 1.
Run time: This action can take a long time to run; usually much longer than the SAPDBA -CHECKOPT in step 1. It is substantially more performance-intensive than step 1 and should therefore run when users are not on the system.
It is best to allow the action to run to completion (without a time limit). Otherwise, some outdated statistics will not be updated. That in turn can affect performance when the database accesses the affected table(s).
If necessary, however, you can apply a time limit, as described in step 1. You can specify a time limit in the action parameters in the DBA Planning Calendar. The command will then work its way through the tables in the DBSTATC control table.
If required, you can also specify priority handling for critical tables by changing DBSTATC options. See Maintaining the DBSTATC Control Table for Optimizer Statistics: Oracle for more information.
3. Check the results of the action in the DBA Planning Calendar to be sure that the CBO statistics have been updated.
You can display the .opt (CHECKOPT, check for old statistics) and .aly (ANALYZE, refresh statistics) logs that SAPDBA generates with the DB Optimizer function in Transaction DB14 ( Tools ® CCMS ® DB administration ® DBA logs). Result The CBO statistics for the tables in the R/3 control table will be regularly checked and updated.
DBSTATC is delivered to you with entries for more than 400 R/3 critical tables. These are tables that may grow rapidly and which therefore benefit from periodic CBO updates.
SAPDBA -CHECKOPT automatically keeps the entries in the DBSTATC up to date. It adjusts the method specified in DBSTATC for generating statistics as a table grows. It also adds new entries as required, if other tables grow and need new CBO statistics.
The system also manages the DBSTATC, automatically deleting table entries if a table's size has become stable. This automatic management helps to keep the DBSTATC table manageable. Should a table again need new CBO statistics, it is added back into the DBSTATC table.
See also:
· Should you or SAP’s EarlyWatch determine that an additional table needs regular CBO statistic updating, you can add the table to the DBSTATC table. It will then receive regular checking and maintenance of CBO statistics.
– Checking and Updating Optimizer Statistics for a Single Table: Oracle
– Maintaining the DBSTATC Control Table for Optimizer Statistics: Oracle · Background information on the SAPDBA -CHECKOPT and -ANALYZE commands:
– See the SAPDBA -CHECKOPT and -ANALYZE options in BC SAP Databse Administration: ORACLE .