Before to try to improve something you must identify what you are supposed to improve.
"Improve report performance" doesn't actually mean anything in OBIEE as the report performance is a mix of multiple applications talking to each other, so until you don't start by finding out where time is spent there is nothing to improve (sorry but the GO_FAST = 42; parameter in nqsconfig.ini doesn't work ...).
If you want to start analyzing the root cause of your poor performance have a look at this: https://www.youtube.com/watch?v=0W34l2O9GMo
And the slides rmoff wrote are here: No Silver Bullets - OBIEE Performance in the Real World (Oracle Open …
Actually when i ran the physical query of the report taken from Manage Session. It took almost a day to fetch the data and also fetched only 17 records.
Tables used in the query is already indexed. Kindly suggest how to fine tune the query.
Indexing means nothing if not done with regards to the actual queries run against the database.
As Gianni said unless you do a proper analysis there's no "one size fits all" solution.
Well, this doesn't really tell you where the issue is ...
Did the query take a day to return you 17 records because your DB is slow or did it take so long because the query was really bad?
If it's the first you must ask a DBA to have a look and it's a DB issue, if it's the second you must investigate if the report is written in the best possible way, if the model in the RPD can be done in a different way etc.
So you need to look a bit more to find out exactly where to spend time improving something getting the best improvement out of it (like the 80-20 rule: find where you can improve 80% by investing just little time).
query is taking lot of time, DB is not slow. and please suggest how to redesign the RPD model for that particular report. Because the presentation , logical and physical tables and joins are already created ..How to proceed , so that i can check whether it is the query problems or join ?
... and please suggest how to redesign the RPD model for that particular report ...
You know that we have no idea of what kind of report you are talking, right?
Did you watch the recording of "No Silver Bullets - OBIEE Performance in the Real World" rmoff did? Or at least reading the linked slides?
There you find all the inputs to do a proper analysis of what happen when you run your analysis.
... DB is not slow...
Who told you that? What does "slow" for a DB mean for you? Again without an analysis of where time is spent, without a "time profile" of your analysis it's impossible to say if it's a query problem, a join or just because today it's sunny or windy ...