SAP Checking and Updating Optimizer Statistics for a Single Table: Oracle

Checking and Updating Optimizer Statistics for a Single Table: Oracle
Use this function to refresh the CBO statistics for a single table. Prerequisites Use this function only when you determine there is an immediate need for updated CBO statistics for a particular table or limited set of tables.
For example, use this function when the following occur:

• You or SAP’s EarlyWatch determine that a particular table or limited set of tables need updated CBO statistics. That is, you have a CBO-related performance problem relating to one or a few tables.

• You reorganize a particular table or limited set of tables. After a reorganization, the CBO statistics are no longer accurate.

• You import a lot of data into a particular table or limited set of tables (for example, with batch input). The additional records make the CBO statistics inaccurate.

Procedure

1. Start the function. Choose Tools ® CCMS ® DB Administration ® Cost-based optimizer ® Single table statistics or Transaction DB20.
2. Enter the name of the table that you wish to work with. Generic specifications are not supported. Then choose Continue.
The table whose name you enter should meet one of the qualifications listed above in Prerequisites.

3. Choose Count total number of rows to determine whether the table’s CBO statistics need refreshing. Then do the following:

• If you want to proceed, confirm the warning message that is displayed. Depending upon the size of the table, this check can take from a few milliseconds up to potentially several minutes or even longer.
• When the function finishes, check the Deviation field. If the deviation is 10% or greater, then you should update the statistics for the table. Otherwise, the deviation between the CBO statistics and the actual state of the table is not significant enough to require new statistics.

The Statistical value field shows the number of entries recorded for the table in the current CBO statistics. Total shows the actual number of entries in the table at the current time.

1. Do the table’s statistics need to be refreshed? If so, then:

• set the Accuracy to High

Warning: Large tables can cause very long run times. If you are unsure, then test the run time first with option Medium for tables with between 6,000 and 60,000 records, option Low for tables with over 60,000 records. These options approximate the SAPDBA default settings for the CBO method. If the runtime is acceptable, then repeat the statistics refresh with High, if desired.

• choose Refresh.

The system updates the CBO statistics for the table. If the table has a DBSTATC entry, then the Analyse needed flag is turned off in the table, if set. (The flag is reset only if you use Accuracy High.)
A success message is displayed when the update is completed, and the Last statistics fields are updated to show the current time and date.
Background information: Accuracy High is SAPDBA statistics method C (Calculate). For a single table, generating exact statistics is not prohibitively costly in time and computing resources. Low accuracy is estimate with sample of 1064 records. Medium accuracy is estimate with 20 percent sample.
If you cannot carry out the check from the Single table statistics because of the limit on interactive processing time, then you can use the SAPDBA program to check the table. Use SAPDBA -CHECKOPT with the name of the table, or use SAPDBA’s CBO control panel. Use SAPDBA -CHECKOPT as well to add the table to the DBSTATC control table for CBO statistics. See Updating Optimizer Statistics Automatically: Oracle. Result You have updated the CBO statistics for a particular table.
The table has not been added to the automatic statistics update mechanism. Only a one-time refresh of the statistics has been made. That is, no entry has been made for the table in the DBSTATC control table for CBO statistics. (An entry is added to DBSTATC only if you use SAPDBA -CHECKOPT.) If you used Accuracy High and if the table has a DBSTATC entry and was flagged for a statistics-refresh, then the flag has been reset. This automatic reset prevents a needless refresh of the statistics at the next regular SAPDBA -ANALYZE action. (See Updating Optimizer Statistics Automatically: Oracle.)