Performance issue — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Performance issue

Received Response
21
Views
5
Comments
user10499938
user10499938 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:58PM in Oracle Analytics Cloud and Server

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    A solution? Sure .... find out what happen !

    https://speakerdeck.com/rmoff/still-no-silver-bullets-obiee-12c-performance-in-the-real-world

    Look at logs, analyse the generated query, analyse where the time is spent: is it in the DB? is it somewhere else?

    Based on these information you will probably directly find how to improve things. If not at least you will have enough information to come back and post details here.

    PS: a "correct" RPD doesn't mean a good RPD, and Oracle support verification I'm not sure I really want to find out how it is done ... Without all the above information I wouldn't say the RPD is correct. It probably just doesn't have errors and warnings, which is different ....

  • user10499938
    user10499938 Rank 3 - Community Apprentice

    Hi Gianni,

    thanks for your reply and your suggestions. I know that my post is very generic, but the issue is very generic.

    In last days i've done many tests:

    - Drop and create the table in the rpd

    - Set the very small table as Driving table

    - Modify the join in a very simple join on key fields and not a complex join.

    The small table is joined to fact table with a simple inner join.

    Taking the query generated by the application and executing it on the DB, i've verified that also on the DB the time degenerates.

    So the problem seems to be in the DB.

    But why only for this table?

    Now i speak in italian: Non pensi che il problema possa essere intrinseco alla tabella (per esempio il modo in cui essa è stata creata?).

    Quali prove posso fare?

    Thanks

  • Gianni Ceresa
    Gianni Ceresa Mod

    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.

  • user10499938
    user10499938 Rank 3 - Community Apprentice

    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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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.