I was looking for some advice on how you guys go about performance tuning OBIEE,
I have done the usual steps to make sure I have done all that can be done on the BI server, I am now looking at the physical query's on the data warehouse.
I have the optimizer set to gather statistics I have many indexes that are very fresh but I was wanting use some more materialized view to try and get the cost down more. How on earth can you do this the query I am seeing that the BI server submits to the data warehouse are highly complex and huge query's where do i start.
1. If is ain't broke, don't fix it!
2. Don't select a solution until you know what the problem is
3. Don't even begin performance 'tuning' until you first document the performance that you have now.
It sounds like you have already broken all of those rules.
Why do you have 'many indexes that are very fresh'? Optional indexes are generally added to improve performance. So why were those indexes added?
And why have you selected a 'materialized view' solution when you haven't identified a specific problem that needs to be solved?
You need to avoid getting infected with CTD - compulsive tuning disorder. There has to be a reason to do performance tuning. The first step is to determine that the performance of 'something' actually NEEDS to be tuned. Often that need might be based on an SLA (service level agreement) that needs to be met.
Start with the overall process, not with a specic query. Determine the performance goals for each part of the process and prioritize them. Any improvement in one part of the process (or one query) will often negatively impact another part.
Document the current performance. Identify ALL key queries and processes and collects metrics about their current performance: execution time, number of rows processed, generate/archive the actual execution plans.
You need that baseline information in order to know, later, if the performance has changed. Performance is all about comparison: comparing NOW with BEFORE.
Thanks for the insight,
Your right about the fully documenting the performance before and after, I am trying to tune some of the long running query as we have query's that can be running for 45 min+. Unfortunately with regards to the indexes I was not the one who added them, I am not the developer of the system as a whole just the DBA/OBIEE Admin tasked with looking after it.