OBIEE and Database Performance Tuning

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
#5 Pothalaiah 2014-12-16 12:21
Hi all,

To improve Report performance generally we go for creating Aggregate tables for the most oftenly used dimention levels by the users but if the users wants to dill down or up with each dimention levels then we need to go for creating cubes. this is Basic factor to go for Cubes and also its difference between aggregate tables and Cubes recomondation.


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

Latest Tweets