I don’t think that I’ve been involved in a project yet that did not meet performance hurdles. We have always overcome them, but do tend to be found towards the end of the project when things are already stressfull enough. I have decided to blog about the subject and hopefully help some people.
Performance is a large issue to cover; I guess that I will create quite a few blogs that will hopefully stitch together nicely. This is really just an introduction to the text.
What can we tune
When you come across obiee Performance issues then there are 4 main areas that you may look at to try and improve performance.
Where do we Start
Because tuning is such a big subject when we get hit with a problem it can be quite overwhelming to resolve it; when the issue is down to SQL Performance then we can follow a 3 step process. Actually, the process is similar regardless, but we will only be looking at SQL Performance.
1 - Isolate the Problem to a Single SQL Statement
To make the problem manageable then we need to reduce the issue to its most basic form; for example remove tables and columns in the query to only those causing the issue. It may be that there is an issue with the join to one dimension from the fact and another issue to another dimension; treat them differently, they may well be different issues. They may be the same issue, but breaking the issue down will make it more manageable and help to understand the overall problem.
To isolate the problem, the first thing that we do must be to derive the SQL submitted by the BI Server. We have a blog on the obiee query log, which will help if you don’t know how to do this. If you still have difficulties finding a query then it could be down to caching; we have a blog on purging obiee server cache, which would help with this issue.
2 – Analyze the SQL to determine the cause of the problem
Once you actually locate and analyze the problem you will usually find that the problem itself is quite easy to fix; the difficult, or at least time consuming bit, is in the analysis.
To analyse the SQL we need to extract it from the query log and then use a tool such as Oracle SQL Developer, or Toad, to submit it to the plan table and then read the explain plan from the plan table – see my blog on the obiee explain plan.
3 – Fix the Problem
As suggested earlier, it is very rare that there is not a solution to a performance problem, although the fix may vary in its complexity.
Every problem is unique, but hopefully some of the problems that I mention may suggest some solutions to help you.
Performance is a large issue to cover; I guess that I will create quite a few blogs that will hopefully stitch together nicely. This is really just an introduction to the text.
What can we tune
When you come across obiee Performance issues then there are 4 main areas that you may look at to try and improve performance.
- SQL Tuning
- Database Tuning
- System Tuning
- Network Tuning
Where do we Start
Because tuning is such a big subject when we get hit with a problem it can be quite overwhelming to resolve it; when the issue is down to SQL Performance then we can follow a 3 step process. Actually, the process is similar regardless, but we will only be looking at SQL Performance.
1 - Isolate the Problem to a Single SQL Statement
To make the problem manageable then we need to reduce the issue to its most basic form; for example remove tables and columns in the query to only those causing the issue. It may be that there is an issue with the join to one dimension from the fact and another issue to another dimension; treat them differently, they may well be different issues. They may be the same issue, but breaking the issue down will make it more manageable and help to understand the overall problem.
To isolate the problem, the first thing that we do must be to derive the SQL submitted by the BI Server. We have a blog on the obiee query log, which will help if you don’t know how to do this. If you still have difficulties finding a query then it could be down to caching; we have a blog on purging obiee server cache, which would help with this issue.
2 – Analyze the SQL to determine the cause of the problem
Once you actually locate and analyze the problem you will usually find that the problem itself is quite easy to fix; the difficult, or at least time consuming bit, is in the analysis.
To analyse the SQL we need to extract it from the query log and then use a tool such as Oracle SQL Developer, or Toad, to submit it to the plan table and then read the explain plan from the plan table – see my blog on the obiee explain plan.
3 – Fix the Problem
As suggested earlier, it is very rare that there is not a solution to a performance problem, although the fix may vary in its complexity.
Every problem is unique, but hopefully some of the problems that I mention may suggest some solutions to help you.
No comments:
Post a Comment