Performance issue

Question

Hi Guys,

i'm not able to understand why the original report it takes few seconds to retrieve results, but when i add to it another dimension table composed by few records (10. It isn't a big dimension) in inner join with fact table  the performance degenerates and it takes 3 hours.

Can you suggest a solution? The rpd is correct. It has been verified by Oracle support.

Thanks

[Deleted User] · Answer

So this clearly is a problem of the database and not OBI, that's a given. Why don't you take the precise use case and post it in the database forums? Including all pertinent information of couse like the SQL, the cardinalities between the tables, the number of rows in each table involved in the queries, the indexes you have...basically the core physical structures.

We - on the analytical side of things - are more or less completely dependent on what the sources provide us. If what they provide isn't working or not performing we can't just hit a GO_FASTER switch and the query magically performs in spite of what exist physically.

user10499938 · Answer

Hi Gianni,

unfortunately is already helping me a DBA colleague who, observing the execution plan, is trying to create some indexes on the DB to improve the performance, but no one works.

He and me are so frustrated :-(

Regards

Gianni Ceresa · Answer

The good point is that you managed to confirm the issue happen also when running the query directly on the DB side, so in a way it isn't OBIEE's fault (at least this time  ).

(English below)

Beh, purtroppo tutto dipende da come la tabella è fatta, dal database, dai parametri, dalle dimensioni, indici o meno etc. Visto che quando fai la query "a mano" sul DB ci mette anche li molto più tempo è chiaro che il problema sia, in un certo modo, intrinseco alla tabella. Onestamente direi che non vale la pena che tu perda tempo o provare cose "a caso". A dipendenza del tuo ruolo non sarai magari neanche autorizzato ad aver accesso alle informazioni che ti permettano di capire cosa sta capitando (penso all'execution plan se è un database Oracle). Se hai un DBA a portata di mano mandagli la query in questione presentando la problematica e chiedigli se è possibile fare i necessari cambiamenti. Il rischio è che la risposta sia di cambiare la query, cosa che non puoi fare in modo diretto in OBIEE ma solo indirettamente cambiando il modello (c'è spesso e volentieri modo di arrivarci ma richiede un po' di acrobazie nel RPD a seconda della query voluta).

Se non hai un DBA guarda l'execution plan, con un po' di pratica si intravvedono quali sono i problemi (spesso un indice non usato o non presente).

(in English to make it understandable to others)

It's kind of difficult to suggest a direct test to do in the DB to find out why it's taking longer as it clearly depends on many factors like the structure of the table, indexes etc. So having a look at the execution plan is a start. Ideally sending the query to the DBA and ask him to have a look as he is more used to that kind of things. The issue is the answer can be to change the query, something not so easy to do in OBIEE. But in the RPD is often possible to manage to change the model enough to generate a different query performing better.