Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Why same Physical query takes more time to run in OBIEE than Sqlplus

Received Response
52
Views
4
Comments
DeepaliG-Oracle
DeepaliG-Oracle Rank 3 - Community Apprentice
in Oracle Business Intelligence Applications

Hi Guys,

We have an reporting tool in OBIEE 11g. We are struggling with the performance issue of some of the analysis.

While identifying issue we observed from logs (by setting LOGLEVEL=2 in BI analytics) that a SQL query which is hit multiple times takes more time (approx 40 sec.) in OBIEE Physical layer execution, while in SQL Plus it takes much lesser ( approx. 8 secs).

Please suggest how to cater this issue and improve the SQL execution time.

Thanks a lot in advance.

Regards,

Deepali

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Honestly don't even try to compare a command line thing with OBIEE ... Can you just imagine how many steps OBIEE execute for a query more than sqlplus?

    I would suggest to make a proper analysis of what happen when you run your analysis: https://www.youtube.com/watch?v=0W34l2O9GMo

    And don't try to compare incomparable things (ask sqlplus to build a nice dashboard, with prompts, charts, drill down etc., or even simpler: ask sqlplus to return you a pivot as fast as OBIEE ....).

  • DeepaliG-Oracle
    DeepaliG-Oracle Rank 3 - Community Apprentice

    The last line is absolutely true .

    However the time I mentioned is not in rendering overall analysis, it was just to execute the Physical query in DB, which I took from the logs.

    Thanks for sharing the video, I will go through it, hopefully it will help.

    Thanks again!

  • Gianni Ceresa
    Gianni Ceresa Mod

    Well, sqlplus has its own dedicated connection, OBIEE use connection pool shared by other things in your system, that's why the info in the logs can be not correct because of the way it's measured and it really depends on what else is happening on your system, how your "pipes" between OBIEE and the DB are setup and also if you run the query in sqlplus from the server itself or your own PC etc.

    That's why I posted the link to @rmoff video, it generally help in finding out how many elements play a role in an OBIEE analysis.

    In the end just be ready to accept that OBIEE is slower than sqlplus without managing to find a real reason, it can also happen ....

  • rmoff
    rmoff Rank 6 - Analytics Lead

    That's strange that the physical query should take much longer.

    Are you using the same DB user? Running the comparison SQL*Plus from the same machine as OBIEE?

    What kind of load is your BI Server under at the time? How much data are you pulling back in the query?

    You mention "which is hit multiple times" -- how many concurrent executions of it are running? Have a look at your DMS metrics and see if you connection pool is under strain.