Solution : https://service.sap.com/sap/support/notes/176754 (Connexion à SAP Service Marketplace requise)
Résumé :
Malgré une configuration appropriée, l'Oracle Cost Based Optimizer (CBO) ou Rule Based Optimizer (RBO) peut choisir des chemins d'accès inefficaces pour les déclarations SQL, rallongeant les temps d'exécution. Les bogues et comportements particuliers au sein des optimiseurs Oracle, détaillés dans plusieurs notes, reflètent des problèmes courants dans les environnements SAP. Les solutions incluent l'imposition de chemins souhaités via des indices de base de données (Notes 129385, 130480) ou l'ajustement des statistiques de l'optimiseur (Note 724545), bien que ces approches soient généralement temporaires. Des directives complètes pour utiliser le CBO pour des évaluations de coûts optimales sont décrites dans la Note 750631. Il est conseillé de tester les changements dans un environnement non productif pour minimiser les perturbations dues à des résultats imprévus.
Mots Clés :
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
Notes associées :
| 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 |