Solution : https://service.sap.com/sap/support/notes/176754 (SAP Service marketplace login required)
Summary :
Despite appropriate configuration, Oracle Cost Based Optimizer (CBO) or Rule Based Optimizer (RBO) may select inefficient SQL access paths, lengthening executions. Bugs and peculiar behaviors within Oracle optimizers, detailed across various implementation notes, reflect common issues in SAP environments. Solutions include enforcing desired paths using database hints (Notes 129385, 130480) or tweaking optimizer statistics (Note 724545), although these are generally short-term fixes. Comprehensive guidelines for leveraging the CBO for optimal cost assessments are outlined in Note 750631. Testing changes in non-production settings is advised to minimize disruption from unforeseeable outcomes.
Key words :
select statement 4 sort aggregate 3 filter 1 table access full temp_useg 2 index range scan temp_ulobs_1, msg_id=n'4b34a370-17d7-11db-a301-e8330ac889e2', analyze table <table_name> estimate statistics sample <sample>percent, mandt = aufk-mandt aufpl = afko-aufpl, long-running control file accesses solution 1, 6 index unique scan /sapapo/matkey~0 search columns, aufk-mandt = afko-mandt view fields, adequate index fast full scan, fast full index scan inside, perform multiple full index scans
Related Notes :
| 1559304 | Performance of work item reorganization |
| 1116930 | FAQ: Sub-queries |
| 1041983 | F4 help:Pperformance problem with Oracle 10G |
| 1020260 | Delivery of Oracle statistics (Oracle 10g, 11g) |
| 1008433 | F4 help: Long runtime with Oracle V.10 |
| 1004794 | Performance problem when you use first_rows_n |
| 992261 | Optimizer merge fix for Oracle 9.2.0.8 |
| 981875 | Optimizer merge fix for Oracle 10.2.0.2 |
| 978710 | Transferring UP TO n ROWS with FIRST_ROWS(n) hint |
| 960633 | Merge Join Cartesian in complex join |
| 951416 | Semijoins and OR clause in inner conditions |
| 948197 | Merge fix for DBMS_STATS package on Oracle 9.2.x and 10.2.x |
| 927295 | FAQ: Oracle system statistics |
| 881083 | Blocking factors on Oracle-based systems |
| 880568 | WRONG CARDINALITY WITH RANGE PREDICATE AND HISTOGRAMS |
| 869006 | Composite SAP note: ORA-04031 |
| 846308 | Performance of SELECTs in WM using Oracle database |
| 841280 | Long runtime with OR and EXIST |
| 830576 | Parameter recommendations for Oracle 10g |
| 825653 | Oracle: Common misconceptions |
| 799649 | Runtime IQ03 (history) long SELECT to VIAUFKST |
| 797629 | FAQ: Oracle histograms |
| 772497 | FAQ: Oracle Hints |
| 766349 | FAQ: Oracle SQL optimization |
| 756335 | Statistics in tables w/ heavily fluctuating volumes of data |
| 753096 | DBMS_STATS: num_distinct = 0 when using ESTIMATE |
| 750631 | Approximations for cost calculation of the CBO |
| 742950 | Performance affected on Oracle DB with Supplement 11 |
| 722188 | FAQ: Oracle partitioning |
| 712624 | High CPU consumption by Oracle |
| 690702 | Histograms cause long runtimes on Oracle 9 |
| 651060 | FAQ: Oracle Parallel Execution |
| 641435 | FAQ: Oracle Index Organized Tables (IOTs) |
| 634263 | Selects with FOR ALL ENTRIES |
| 618868 | FAQ: Oracle performance |
| 448380 | Information: Oracle Package DBMS_STATS |
| 408215 | BCT-CO totals record DataSourcen for costs |
| 365480 | CBO: Field filled with leading "0" aligned to left |
| 335415 | Creating and Maintaining Histograms |
| 305225 | CBO and transitive fields |
| 135048 | Long runtimes statements w/ ROWNUM <= condition |
| 130480 | Database Hints in Open SQL for Oracle |
| 129385 | Database hints in Open SQL |
| 128648 | Incorrect path used due to rounding errors |
| 106047 | DB21: Customizing the DBSTATC |