Solution : https://service.sap.com/sap/support/notes/771929 (SAP Service marketplace login required)
Summary :
This SAP Note discusses management of index fragmentation within SAP systems using Oracle databases. Index fragmentation occurs due to deletions and subsequent insertions causing inefficient space usage within index blocks, which can lead to performance degradation. Key tables such as RFC tables are prone to fragmentation due to their data volatility. Index fragmentation is measurable in terms of storage quality (space used versus available space) and leaf row quality (ratio of deleted leaf rows to total leaf rows). The note advises that indexes with less than 25% storage quality should be rebuilt. For identification, analysis, and correction of fragmented indexes, SAP recommends several Oracle commands and SAP tools, while always considering certain limitations like small index sizes or database-specific parameters that might affect accuracy of fragmentation measurement. Rebuilding or defragmenting indexes is necessary when performance issues are detected or when an index occupies disproportionate space in the buffer pool.
Key words :
db02-> detailed analysis-> enter index-> detailed analysis-> analyze index-> storage quality, <index>-> detailed analysis-> alter index-> coalesce / rebuild report rsoraisqn, sap tables typically undergo index fragmentation, oracle command analyze index validate structure, analyze index validate structure online, index size highly fragmented large indexes, alter session set events', analyze index validate structure, analyze table validate structure, oracle automatic segment advisor information
Related Notes :
| 1438410 | SQL: Script collection for Oracle |
| 1295200 | Oracle 10g or higher: Space statistics based on DBMS_SPACE |
| 979054 | RSORAISQN |
| 970538 | Collective note RSORAISQN |
| 928037 | FAQ: SAP MaxDB indexes |
| 927813 | Oracle 10g: Using Oracle Segment Advisor to optimize space |
| 915242 | FAQ: Reverse key indexes |
| 912620 | FAQ: Oracle indexes |
| 910389 | FAQ: Oracle Segment Shrinking |
| 883346 | Performance problems in realignment framework |
| 882425 | ConnTrans performance: Long Confirmation times |
| 880580 | Index rebuild on Lookup tables for R&R Performance Improvmnt |
| 875798 | Index rebuilds (Oracle) for R&R specific CRM tables |
| 825653 | Oracle: Common misconceptions |
| 821687 | FAQ: Space utilization and fragmentation in Oracle |
| 806554 | FAQ: I/O-intensive database operations |
| 789011 | FAQ: Oracle memory areas |
| 766349 | FAQ: Oracle SQL optimization |
| 706478 | Preventing Basis tables from increasing considerably |
| 618868 | FAQ: Oracle performance |
| 444287 | Checking the index storage quality |
| 439783 | BR986W Index ... is unbalanced - please rebuild the index |
| 435125 | Poor performance of tables used by CRM (Oracle only) |
| 332677 | Rebuilding fragmented indexes |