Performance Tuning in OBIEE

Question

Hi,

I am Kumar, Having an issue in OBIEE, where the performance of the report is too bad. The time taking for the report is about 5 min.

But the client wants the same in a Min. Please help me out in this. Its a finance package customized by the client.

Default indexes are created, if customized indexes are to be created. Please let me know.

Here is the screen shot attached which illustrates the SQL query of the report.

http://

Regards,

Prashanth

Thomas Dodds · Answer

I'm reminded of the request for big data to ensure performance and not understanding a big data mining query can take days/weeks to process ... wrong tool for the job ... what's the JOB?  The REAL requirement?  was it accepted and vetted by those who UNDERSTAND the capability of the tool?  was performance designed up-front?

lastly, there's no tuning knob ... alas!  performance is a tradable commodity with flexibility -- they ALWAYS have an inverse relationship!  remove flexibility from the automobile and you get a Ferrari that doesn't haul lumber but it scoots around corners like it's on rails ... remove flexibility and build a heavy frame, plop a box on the back and you get a pickup truck that handles poorly but can haul the 1.5 ton load you want.   The vehicle is fit for the job ... by design.

Gianni Ceresa · Answer

Hi Kumar,

Please do not start adding random things like an index here or there because you are desperate, you will only make things worse and unpredictable for the future.

First you must be sure of where the time is spent, as you post a SQL Developer screenshot I assume your analysis ( https://speakerdeck.com/rmoff/still-no-silver-bullets-obiee-12c-performance-in-the-real-world  ) show you that 5 minutes are spent in the DB running the query?

If you didn't do that analysis I would really suggest to do it as an OBIEE analysis can spend time in many places ...

And for your question nothing better than asking your DBA, keeping in mind the query is generated and not written by hand and so any answer about adding hints here and there will not work.

The time taking for the report is about 5 min.But the client wants the same in a Min.

Well ... sometime what you want is not what you get, I asked many things to Santa Claus but didn't always get them ...

There can be other elements like database architecture or infrastructure making things impossible without changing a lot, so analyze the options, talk with your DBA and also get ready to tell your customer that 5 minutes is and 5 minutes will be and it's better than 10 ...