OBIEE and Database Performance Tuning

User Rating: / 34

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


#1 Sravani 2012-12-06 10:21
I am agree with this but how many aggregates we need to built for a project. Suppose i have nearly 500 reports to built for those shall i need to create aggregates for each and every summary and detail report ?
#2 Tony H 2012-12-07 17:46
Hi Sravani,
Typically you won't need an aggregate for each report. You will normally find that a single aggregate can support multiple reports.
Aggregates are more applicable for "Summary" reports, you might find that the aggregates don't make as much difference for "Detail" reports - it just depends on how detailed those Detail reports are! But either way, you should not expect to create an aggregate for every report.
#3 subu 2013-06-06 00:29
one of the best presentation.

Tony you're Amazing.
#4 Nachiket 2014-03-26 03:18
Amazing presentation

View Office Addresses
+44 1246 550058
+353 1 871  9419
+44 1246 550058
+353 1 533 4280
Twitter: @Peakindicators

Latest Tweets