Solution : https://service.sap.com/sap/support/notes/724545 (SAP Service marketplace login required)
Summary :
The SAP Note outlines issues and solutions regarding the Cost Based Optimizer (CBO) performance in SAP systems. The main symptom described is that the CBO uses unsuitable access paths, leading to increased runtime and system load. The root cause is identified as inadequate database statistics. Solutions include creating more accurate statistics, using histograms, applying hints, or manually adjusting with DBMS_STATS. The note emphasizes checking other solutions before manual adjustments and mentions several related SAP Notes for further guidance. For enhancing performance, commands like SET_COLUMN_STATS, SET_INDEX_STATS, and SET_TABLE_STATS can adjust statistical values affecting the optimizer’s behavior. The note insists on careful consideration and testing in non-production environments before applying changes to production, with a safety measure against overwriting by BRConnect.
Key words :
avoid superfluous side effects, unwanted side effects, performance problems occur, nested loop join, oracle ddic reference, oracle online documentation, cost based optimizer, index range scans, higher system load, lose histogram statistics
Related Notes :
| 1374807 | Freezing single kinds of statistics |
| 1020260 | Delivery of Oracle statistics (Oracle 10g, 11g) |
| 852365 | Performance of BBP_PDVIEW_LIST / BBP_PDHGP |
| 799649 | Runtime IQ03 (history) long SELECT to VIAUFKST |
| 772497 | FAQ: Oracle Hints |
| 766349 | FAQ: Oracle SQL optimization |
| 756335 | Statistics in tables w/ heavily fluctuating volumes of data |
| 750631 | Approximations for cost calculation of the CBO |
| 744315 | LDB PSJ: Elimination of 'rule' hints Oracle |
| 735510 | LDB PSJ: Elimination of the Hints Oracle 'rule' statement |
| 588668 | FAQ: Database statistics |
| 448380 | Information: Oracle Package DBMS_STATS |
| 106047 | DB21: Customizing the DBSTATC |