Solution : https://service.sap.com/sap/support/notes/825653 (SAP Service marketplace login required)
Summary :
This SAP Note elucidates common misconceptions about Oracle database operations within the SAP environment. The note systematically corrects popular beliefs regarding Oracle's cost-based optimizer, index setup, use of histograms in R/3, and the significance of the System Global Area (SGA) in memory allocation. It clarifies that the Oracle cost-based optimizer may prioritize rule-based decisions and disregards indexed columns not equated in the WHERE clause. It also explains that index arrangement should prioritize fields by their selectivity and mutability to enhance query performances and index compression. Additionally, the note discusses the changing protocols for local connections in newer Oracle versions since 8i, highlighting that IPC protocol usage has altered. Recommended improvements involve reevaluation of index and join hints in ABAP views, rectifying misconceptions about automatic ORDER BY operations, and explaining the insufficient reliability of Oracle buffer pool hit rates in assessing database performance. Furthermore, it points out the potential for temporary locks during ONLINE functions, and disconnects between index level counts and fragmentation.
Key words :
index fast full scan execute multi block reads, triggering sql statement executes unnecessarily large sortings, system frequently reads incomplete resulting sets, distinct values frequently considerably improve, alter session enable parallel ddl, unfavorable index unique scan access, normal brconnect statistical run ensures, takes rule-based decisions, cbo making wrong decisions, tx - allocate itl entry
Related Notes :
| 1259766 | Incomplete consistent table export using data pump |
| 1093485 | Long parsing times on Oracle |
| 927295 | FAQ: Oracle system statistics |
| 910389 | FAQ: Oracle Segment Shrinking |
| 805934 | FAQ: Database time |
| 797629 | FAQ: Oracle histograms |
| 789011 | FAQ: Oracle memory areas |
| 772497 | FAQ: Oracle Hints |
| 771929 | FAQ: Index fragmentation |
| 766349 | FAQ: Oracle SQL optimization |
| 745639 | FAQ: Oracle enqueues |
| 723879 | Explain: System displays an incorrect access path |
| 682926 | Composite SAP note: Problems with "create/rebuild index" |
| 659946 | FAQ: Temporary tablespaces |
| 658322 | Composite SAP Note: Incorrect resulting set with Oracle |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |
| 592393 | FAQ: Oracle |
| 588668 | FAQ: Database statistics |
| 562403 | FAQ: Oracle Net |
| 540463 | FAQ: Consistency Checks + Block Corruptions |
| 521264 | Hang situations |
| 176754 | Problems with CBO and RBO |