Oracle Analytics and AI

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

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

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

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

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

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

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.