Solution : https://service.sap.com/sap/support/notes/806554 (SAP Service marketplace login required)
Summary :
The SAP Note focuses on optimizing I/O-intensive database operations which include tasks like index creation, rebuilding, and table reorganizations, crucial for improving performance in SAP environments. It outlines conditions under which such operations are necessary, for instance, if a new index is required or an existing one is fragmented. Effective strategies to minimize disruption during these processes include performing operations online to avoid production locks, utilizing parallel processing, and configuring Oracle parameters like DB_FILE_MULTIBlock_READ_COUNT to enhance read performance. The note emphasizes the importance of understanding when and how to execute these operations using both Oracle and SAP tools, ensuring minimized impact on system availability and performance.
Key words :
alter session set db_file_multiblock_read_count = <new_value> oracle parameters sort_area_size / pga_aggregate_target, objectsanalyze table <table_name> validate structure cascade [online], creating indexes rebuilding indexes coalescing indexes generating statistics structure validation, r3load media recovery client deletion table conversion incremental conversion, coalescing indexesalter index <index_name> coalesce [parallel <degree>], parallel query processes generate hot spots, index fragmentationanalyze index <index> validate structure, rebuild [online] [parallel <degree>] [nologging] choose online, [online] [parallel <degree>] [nologging] choose online, avoid long-running table conversions
Related Notes :
| 1319517 | Unicode Collection Note |
| 1287382 | Extending BW objects on Oracle with large tables |
| 1047369 | Faster DB accesses for R3szchk using Oracle DBSL |
| 1045847 | ORACLE DIRECT PATH LOAD SUPPORT IN R3LOAD |
| 1043380 | Efficient Table Splitting for Oracle Databases |
| 1028099 | Long import runtimes for large db_cache_size |
| 1022097 | Analyze table validate structure cascade online is slow |
| 1013049 | FAQ: Oracle Data Pump |
| 954268 | Optimization of export: Unsorted unloading |
| 952514 | Using the table splitting feature |
| 936441 | Oracle settings for R3load based system copy |
| 912620 | FAQ: Oracle indexes |
| 906139 | Histogram creation takes longer with 9.2 than with 8.1.7 |
| 857973 | Deleting clients efficiently using Oracle |
| 857081 | Unicode conversion: downtime estimate |
| 842240 | FAQ: Backup strategy of large and highly-available databases |
| 793113 | FAQ: Oracle I/O configuration |
| 789011 | FAQ: Oracle memory areas |
| 771929 | FAQ: Index fragmentation |
| 766349 | FAQ: Oracle SQL optimization |
| 750631 | Approximations for cost calculation of the CBO |
| 711047 | Performance problem on DBMS_STATS in 9.2 |
| 684545 | Statistics generation: DBMS_STATS runs longer than ANALYZE |
| 682926 | Composite SAP note: Problems with "create/rebuild index" |
| 651060 | FAQ: Oracle Parallel Execution |
| 647697 | BRSPACE - New tool for Oracle database administration |
| 646681 | Reorganization of tables with BRSPACE |
| 632336 | Long runtimes when compiling statistics on Oracle 9.2.0.x |
| 628590 | Activating table monitoring using "-f monit" option |
| 619876 | Oracle9i: Automatic PGA Memory Management |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |
| 605062 | FAQ: Restore and recovery |
| 592393 | FAQ: Oracle |
| 588668 | FAQ: Database statistics |
| 548016 | Conversion to Unicode |
| 547464 | Nologging Option when creating indexes |
| 541538 | FAQ: Reorganization |
| 541311 | CC-INFO: Parallel processes FAQ |
| 491160 | Restore scenarios for lost files of oracle databases |
| 444287 | Checking the index storage quality |
| 424239 | New BRCONNECT parameter: stats_dbms_stats |
| 408527 | Checking the statistics using DBA_TAB_MODIFICATIONS |
| 403704 | BRCONNECT - Enhanced function for Oracle DBA |
| 365304 | CC-ADMIN: Reports for deleting tables |
| 334224 | Important notes for creating indexes |
| 124361 | Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x) |
| 85558 | Tables with data sorted by index |