I am doing some performance test and I am noticing a big difference between the OBIEE report and the SQL statement generated by it. When I run the SQL statement generated by the report in SQL Developer or TOAD it takes 13 seconds however the same SQL Statement in OBIEE takes 1 minute !
Do you know what could cause such a difference ?
The query when run through OBIEE is likely hitting the OBIEE caching mechanisms (either at the BI server level or at the Presentation Services level) and hence returning data to you very quickly
The OBIEE documentation covers caching for the 18.104.22.168 release here
I hope you retrieved the sql from NQ Query Log File - Check for Physical query which runs against the database.
And, then run the same query.
If, you find vast diff. b/w run times. Then, check For Performance tuning methods.
Thanks for your reply. However, I do not understand what you mean. Could you be more specific ?
To answer your questions, yes, I have the query from the nqquery.log, yes, I run it on SQL Developer and yes I found a big difference between the query running on SQL Developer and the same query running on OBIEE. So my question is why ? As it is the same query running on the same database ?
so did you run the SQL query as obtained from nqsquery file as a direct database request from OBIEE??
or is it the Logical query you run from OBIEE but physical from toad?
hope it's not the time converting a logical to physical which is extra.
Hope the calculations are not in answers....but in BMM layer directly. Any calculation in answers will make it to run slow.
is the cache enabled in RPD?
what is the user logging level for user running the query?
Thanks for your reply.
I did not run the SQL query as a direct database request from OBIEE but this is an excellent idea and I will do it.
Yes, I run the physical in TOAD / SQL Developer and I guess OBIEE only runs the logical query. I have only one calculation on my report (I have to calculate the number of lines returned by the report) Not sure if this could explain the difference.
The cache is enabled and the logging level is set to 0 for the user.