For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hi Chandu,
Below the Steps to improve the performance.
1. implement caching mechanism
2. use aggregates
3. use aggregate navigation
4. limit the number of initialisation blocks
5. turn off logging
6. carry out calculations in database
7. use materialized views if possible
8. use database hints
9. alter the NQSONFIG.ini parameters
Note:calculate all the aggregates in the Repository it self and Create a Fast Refresh for MV(Materialized views).
and you can also do one thing you can schedule an IBOT to run the report every 1 hour or some thing so that the report data will be cached and when the user runs the report the BI Server extracts the data from Cache.
http://blogs.oracle.com/pa/resource/Oracle_OBIEE_Tuning_Guide.pdf
http://www.peakindicators.com/index.php/knowledge-base/98-obiee-and-database-performance-tuning
http://obiee101.blogspot.com/search/label/CACHE
http://obiee101.blogspot.com/2010/01/obiee-performance-tuning.html
http://www.slideshare.net/themoff/performance-testing-and-obiee-4247838
http://www.peakindicators.com/index.php/knowledge-base/96-20-golden-rules-for-the-obiee-11g-rpd
Mark if helps.
Thanks,
Some comments:
With regards to the OP @user5388961 :
"In Sql it is expected as the data is huge. But why OBIEE is taking such time."
^-- So you do expect your query to be slow in SQL. OBIEE is emitting SQL agaisnt the data base...how do you expect a speed-up to happen? If an (same) SQL statement takes 10 seconds on SQL Developer/Toad/whatever, it will not take any less when emitted by OBIEE.
In general: Most performance problems are best tackled at the source - i.e. the actual database. And the means for this are legion: from simple indexing to partitioning stategies, DB parametrization, actual aggregate modelling etc etc.
Check the Execution plan in database whether the tables are using full scan or index scan.
Check whether using cast functions in the front end of the report.
Check whether any null values are going to filter or in metric column, bec null values will take longer time in obiee.
let me know .