This discussion is archived
7 Replies Latest reply: Jan 31, 2013 12:37 AM by Chiwatel RSS

Performance in OBIEE

Chiwatel Newbie
Currently Being Moderated
Hello All,

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 ?

Thanks.
  • 1. Re: Performance in OBIEE
    pamitche Newbie
    Currently Being Moderated
    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 11.1.1.6 release here

    http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/querycaching.htm#BIESG226

    Paul
  • 2. Re: Performance in OBIEE
    Chiwatel Newbie
    Currently Being Moderated
    Hi Paul,

    Actually, the problem is that my query runs much quickly on SQL Developer or TOAD than in OBIEE and not the opposit.

    Any ideas ?

    Thanks.
  • 3. Re: Performance in OBIEE
    Chiwatel Newbie
    Currently Being Moderated
    Anybody on this one ?

    Thanks.
  • 4. Re: Performance in OBIEE
    GRK Journeyer
    Currently Being Moderated
    Hey,

    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.

    Thank you.
  • 5. Re: Performance in OBIEE
    Chiwatel Newbie
    Currently Being Moderated
    Hello,

    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 ?

    Thanks.
  • 6. Re: Performance in OBIEE
    user2534719 Newbie
    Currently Being Moderated
    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?
  • 7. Re: Performance in OBIEE
    Chiwatel Newbie
    Currently Being Moderated
    Hello,

    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.

    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points