Performance and OBIEE - Summary and FAQ
This article is the final one in a series about OBIEE and performance. You can find the previous posts here:
Summary
- The key to long term, repeatable, successful performance optimisation of a system is KNOWING where problems lie and then resolving them accordingly. This means investing time up front to develop an understanding of the system and its instrumentations and metrics. Once you have this, you can apply the same method over and over to consistently and successfully resolve performance problems.
- GUESSING what the problem is and throwing best practice checklists at it will only get you so far. If you hit a lucky streak it may get you far enough to convince yourself that it is enough alone. But sooner or later you will hit a dead-end with your guesswork and have to instead start truly diagnosing problems. When this happens, you are starting from scratch in learning and developing your method for doing this.
To be able to improve performance you must first identify the cause of problem. If you start ‘tuning’ without identifying the actual cause you risk making things much worse.
The slightly acerbic tone at times of these articles may betray my frustration with the incorrect approach that I see people take all too often. A methodical approach is the correct one, and I am somewhat passionate about this, because:
- It works! You gather the data to diagnose the issue, and then you fix the issue. There is no guessing and there is no luck
- By approaching it methodically, you learn so much more about how the OBIEE stack works, which aside from being useful in itself means that you will design better OBIEE systems and troubleshoot performance more easily. You actually progress in your understanding, rather than remaining in the dark about how OBIEE works and throwing fixes at it to hope one works.
FAQ
Q: How do I improve the performance of my OBIEE dashboards/reports?A: Start here. Use the method described to help understand where your performance is slow, and why. Then you set to resolving it as described in this series of blog articles.
Q: No seriously, I don’t have time to read that stuff… how do I fix the performance? My boss is mad and I must fix it urgently!
A: You can either randomly try changing things, in which case Google will turn up several lists of settings to play with, or you can diagnose the real cause of the performance problem. If you’ve run a test then see here for how to analyse the performance and understand where the problem lies
Q: Why are you being such a bore? Can’t you just tell me the setting to change?
A: I’m the guy putting £0.50 bets on horses because I don’t want to really risk my money with big bets. In my view, changing settings to fix performance without knowing which setting actually needs changing is a gamble. Sometimes the gamble pays off, but in the end the house always wins.
Q: Performance is bad. When I run the report SQL against the database manually it is fast. Why is OBIEE slow?
A1: You can see from nqquery.log on the BI Server how long the SQL takes on the database, so you don’t necessarily need to run it manually. Bear in mind the network between your BI Server and the database, and also the user ID that the query is being executed as. Finally, the database may have cached the query so could be giving a better impression of the speed.
A2: If the query really does run faster manually against the database then look at nqquery.log to see where the rest of the query time is being spent. It could be the BI Server is having to do additional work on the data before it is returned up to the user. For more on this, see response time profiling.
Q: This all sounds like overkill to me. In my day we just created indexes and were done.
A: I’ve tried to make my method as comprehensive as possible, and usable in both large-scale performance tests but also isolated performance issues. If a particular report is slow for one use, then the test define, design and build is pretty much done already - you know the report, and to start with running it manually is probably fine. Then you analyse why the performance isn’t as good as you want and based on that, you optimise it.
Q: Should I disable query logging for performance? I have read it is a best practice.
A: Query logging is a good thing and shouldn’t be disabled, although shouldn’t be set too detailed either.
Reading & References
The bulk of my inspiration and passion for trying to understand more about how to ‘do’ performance properly has come from three key places:- Cary Millsap’s blog and particularly his paper Thinking Clearly about Performance
- Greg Rahn’s blog structureddata.org
- Zed Shaw’s essay Programmers Need To Learn Statistics Or I Will Kill Them All
Comments
I’d love your feedback on this. Do you agree with this method, or is it a waste of time? What have I overlooked or overemphasised? Am I flogging a dead horse?I’ve enabled comments on this article in the series only, to keep the discussion in one place. You can tell me what you think on twitter too, @rmoff