Solution : https://service.sap.com/sap/support/notes/1013912 (SAP Service marketplace login required)
Summary :
SAP Note addresses database performance within SAP Business Warehouse (BW). Detailed are table types integral for BW, such as Fact and Dimension tables, alongside their indexing strategies. Temporary tables impacting performance also discussed, including options for controlling their generation through specific SAP parameters. Star transformation for InfoCube queries is explained, breaking down its reliance on Bitmap indexes and factors causing non-implementation despite hints. Guidance encompasses database index architecture, including performance optimization through design adjustments specific to table types and system version dependent practices. Recommendations for query optimization and related utilities are provided.
Key words :
selection conditions-> dimension table -> suitable dimension data records suitable dimension data records-> bitmap index, bitmap merge bitmap key iteration buffer sort table access full, temp table transformation recursive execution sys_le_2_0 insert statement load, column partition format a25column column_name format a20select decode, data mart-specific split parameter takes effect, suitable fact table data records, suitable data records, bitmap key iteration, fact table data records, relevant optimizer merge fixes
Related Notes :
| 1681396 | Query Performance |
| 1431798 | Oracle 11.2.0: Database Parameter Settings |
| 1247763 | BW query: No more hints as of ORACLE 10.2.0.4 |
| 1178655 | E2E Diagnostics - BI InfoCube compression - information |
| 1044441 | Basis parameterization for NW 7.0 BI systems |
| 1016927 | Long runtimes occur when statistics are updated |
| 1010233 | Performance problems during update statistics |
| 1008833 | Supplementary SAP Note about repartitioning |
| 999126 | Performance improvement for star queries |
| 998319 | Update statistics for ODS tables takes long time to run |
| 935815 | Query analysis tool |
| 912389 | Cursor parameters on Oracle |
| 906139 | Histogram creation takes longer with 9.2 than with 8.1.7 |
| 857998 | Number range buffering for DIM IDs and SIDs |
| 840553 | BW Oracle: TRUNCATE table slower than DELETE |
| 837841 | BW Performance: Star Transformation Hint Change |
| 830576 | Parameter recommendations for Oracle 10g |
| 811354 | Use of complex Queries slows down database system |
| 797629 | FAQ: Oracle histograms |
| 758989 | Poor performance with TRUNCATEs |
| 745639 | FAQ: Oracle enqueues |
| 744555 | Settings for materializing SID restrictions |
| 712624 | High CPU consumption by Oracle |
| 711047 | Performance problem on DBMS_STATS in 9.2 |
| 651060 | FAQ: Oracle Parallel Execution |
| 632556 | Oracle 9.2.0.* database parameterization for BW |
| 590370 | Too many uncompressed request (f table partitions) |
| 588668 | FAQ: Database statistics |
| 561961 | Deactivating/activating usage of fact table view |
| 555030 | Deactivating BW-initiated DB statistics |
| 544521 | Controlling the PARALLEL degree |
| 514907 | Processing complex queries (data mart, and so on) |
| 502782 | Composite SAP Note ora-4030 |
| 449891 | Temporary database objects in BW 3.x |
| 428212 | Using BRCONNECT to update InfoCube statistics |
| 424239 | New BRCONNECT parameter: stats_dbms_stats |
| 408532 | Using the DBMS_STATS package for collecting statistics |
| 402469 | Additional indexes on Master Data Tables |
| 383325 | How to manually create a bitmap index |
| 378483 | Extract BW query scenario |
| 354080 | Note collection for Oracle performance problems |
| 351163 | Creating ORACLE DB statistics using DBMS_STATS |
| 217397 | Indexing Scheme of BW facttables under Oracle |
| 192658 | Setting parameters for BW systems |
| 129252 | Oracle DB Statistics for BW Tables |