OBIEE and Database Performance Tuning

User Rating: / 6
PoorBest 

This presentation shows how you can use various tuning options on the Oracle Database to tune your OBIEE queries. The slides guide you through a performance test where a set of OBIEE dashboards were running slow (on a data set of 30 million records). A sequence of tuning options were then applied one-by-one and "Usage Tracking" was used to report on the results.

The performance tests investigated the use of Gatherings Stats, Star Transformation, Bitmap Indexes, removal of Snow-Flakes, Bitmap Join Indexes, Partitioning, Parallel Query, Compression, Aggregate Tables (MViews)

You might be surprised by some of the findings!! For example: removing snow-flakes had no performance benefit.

Overall, the main conclusion is: Nothing beats building aggregate tables (mviews) to improve performance!

In summary, here are the tuning options which had most benefit for Summary/Detail reports:

1) "Summary" reports:

- Gathering statistics 30/100%
- Star Transformation
- Partitioning
- Parallel Query
- Aggregation
- Compression

2) "Detail" reports:

- Gathering statistics 100% (as opposed to 30%)
- Star Transformation
- Partitioning
- Bitmap Join Indexes