Posted: Mon Mar 17, 2008 2:09 pm Post subject: Note 724545 - Adjusting the Cost Based Optimizer statistics
Note 724545 - Adjusting the CBO statistics manually using DBMS_STATS
The Cost Based Optimizer (CBO) uses an unsuitable access path. This causes a longer runtime and higher system load.
Reason and Prerequisites
If performance problems occur because the CBO does not use the best access path, you can use various solutions, for example:
You can create more exact statistics.
You can create histograms (Note 797629)
You can specify hints.
You can adjust the statistics manually with DBMS_STATS.
Before you adjust the statistics using DBMS_STATS, check whether there are other options to solve the problem.
Refer to Note 176754 for a description of typical situations in which it makes sense to adjust the statistics. Refer to Note 588668 for background information on database statistics. Refer to Note 448380 for information about the DBMS_STATS functions. Note 750631 contains details on the cost accounting of the CBOs.
Note 1020260 contains a script for the implementation of CBO statistics for critical tables in the SAP environment.
Use the following commands to adjust the statistical values for tables, indexes or columns:
The following gives you an overview over typical statistical changes to optimize the access path:
Increasing or decreasing the distinct values of a column:
- Oracle DDIC reference: NUM_DISTINCT in DBA_TAB_COLUMNS;
- Result: If you increase the distinct values, an index access using this column becomes more attractive to the system. If you are using a Join, a Nested Loop Join with an access point using this index becomes more likely.
If you decrease the distinct values, an index access using this column becomes less attractive to the system (which is useful if several indexes have a costing of 1 and the incorrect index is used; see Note 176754)
'"<column>"', DISTCNT => <new_value>, NO_INVALIDATE=>FALSE);
- Caution: After you execute this command, the column may lose histogram statistics that may exist.
Reducing the clustering factor of an index:
- Oracle DDIC reference: CLUSTERING_FACTOR in DBA_INDEXES;
- Result: The access with index range scans on this index becomes more attractive to the system.
Reducing the number of rows in a table:
- ORACLE DDIC reference: NUM_ROWS in DBA_TABLES;
- Result: The access on a table becomes more attractive. If you are using Joins, a Nested Loop Join with an access point using this table become more likely.
Index statistics: NUMROWS, NUMLBLKS, NUMDIST, AVGLBLK, AVGDBLK, CLSTFCT, INDLEVEL
Table statistics: NUMROWS, NUMBLKS, AVGRLEN
Refer to the Oracle online documentation for further information.
As of SAP Basis 7.10, SAP has made the RSORASTE report available, so that statistics can be created easily from the SAP system.
Only adjust the statistics after you have performed a thorough check. In a non-production environment, check whether the changed statistics have unwanted side effects before you transfer the changes to the production system. Change the statistical values as little as possible to avoid superfluous side effects. To determine a good statistical value, you may have to perform a large number of tests with different values.
To keep the changed statistics, you must prevent BRCONNECT from overwriting the statistics during its statistical runs. For this purpose, create an entry with ACTIV=I in the DBSTATC table for the relevant table (refer to Note 106047).
As of Oracle 10g, you can also protect statistics from being overwritten at Oracle level. With the following command, the statistics can be locked for a table and then released again:
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.