Oracle Communities

Oracle Analytics and AI

Child Item

OAC dashboard performance issue

Hi, We have migrated from OBIEE 12c to OAC. We are seeing a performance issue with one dashboard after migration in OAC. It takes 5 mins to load the report. In OBIEE it returns in seconds.

Are there any sepecific settings in OAC to optimize the query/filters?

Thanks

Padmaja

Find more posts tagged with

Accepted answers

All comments

Clare Murtagh

Hi @User128178 - Oracle Best‑practice points to check in OAC after an OBIEE migration:

Verify BI Server Query Cache is enabled and sized correctly. OAC does not always mirror OBIEE cache behaviour.
Review dashboard prompts and filters eg - multi‑select prompts, “Is Prompted”, and request variables can generate heavy subqueries.
Check row‑level security filters. OAC can apply them differently, causing unexpected full scans.
Compare physical SQL between OBIEE and OAC; differences usually reveal the root cause.

Can you screenshot/share any sql pls?

User128178 - Oracle

here is the query-

SELECT   0 s_0,   "BI_CONTACT_RATE"."Dim Data"."Data CalendÃ¡rio" s_1,   avg(100*("BI_CONTACT_RATE"."Fato Sumarizada CR Dia DDD"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."T1542275.FS_CR_MES_PERFEITO"."QT_DOMICILIO_MES" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by case when DAYNAME("BI_CONTACT_RATE"."Dim Data"."Data CalendÃ¡rio") in ('Sun','Mon') then 0 else 1 end) s_2,   case when "BI_CONTACT_RATE"."Dim Data"."SK_DATA" >0 then 0 else case when 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano"))<>max(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano") then 0 else 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) end end s_3,   case when "BI_CONTACT_RATE"."Dim Data"."SK_DATA">0 then 0 else case when case when 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano"))=max(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano") then 0 else 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) end <=avg(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by case when DAYNAME("BI_CONTACT_RATE"."Dim Data"."Data CalendÃ¡rio") in ('Sun','Mon') then 0 else 1 end) then case when 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano"))=max(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano") then 0 else 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) end else 0 end end s_4,   case when "BI_CONTACT_RATE"."Dim Data"."SK_DATA">0 then 0 else case when case when 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano"))=max(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano") then 0 else 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) end >avg(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by case when DAYNAME("BI_CONTACT_RATE"."Dim Data"."Data CalendÃ¡rio") in ('Sun','Mon') then 0 else 1 end) then case when 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano"))=max(100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano") then 0 else 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) end else 0 end end s_5,   case when "BI_CONTACT_RATE"."Dim Data"."SK_DATA">0 then 100*("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Quantidade de LigaÃ§Ãµes"/sum("BI_CONTACT_RATE"."Fato Sumarizada Combo Multi"."Soma mÃ¡xima - Quantidade Domicilio por mÃªs" by "BI_CONTACT_RATE"."Dim Data"."DescriÃ§Ã£o MÃªs Ano")) else 0 end s_6FROM "BI_CONTACT_RATE"WHERE(("Dim Sub Marca"."Nome Sub Marca" IN ('CTV', 'NET')) AND ("Dim Motivo URA Remarcada"."Nome Motivo Remarcada URA" = 'Base') AND ("Dim Data"."SK_DATA" >= (20160601)) AND ("Dim Data"."SK_DATA" <= (20160630)))ORDER BY 2 ASC NULLS LASTFETCH FIRST 500001 ROWS ONLY

Gianni Ceresa

Are there any sepecific settings in OAC to optimize the query/filters?

There is no "GO_FAST=True" setting.

But you have everything to find what is happening, and from there why it is happening.

Look at your logs, when you open that dashboard, analyse the logs of each analysis in it, find where time is spent, by which object.

And from there look your logs in detail to find out what step of your analysis is taking time. Go back to your OBIEE and check if if it is doing the same thing or not.

You now have the cause of your "slow" dashboard, and you can therefore look at your options to fix it.

Sharing a LSQL isn't going to do much: nobody here has your OAC, your data model, your database.

PS: if cache is off, do not enable it without a full plan for cache management and impact analysis. When having performance issues, cache is just a workaround that will soon or late fail somewhere else, introducing issues you didn't expect.

Clare Murtagh

Thanks @User128178 - Oracle - you could try a few fixes below (iteratively may be better):

Remove/replace DAYNAME(): You could use database‑explicit function or map weekdays in a dimension.
Flatten nested CASE + aggregates: You could try repositioning into calculated columns and reducing overall usage.
Sorting half a million rows by an analytic expression column is expensive. If the analytic expressions require full-window evaluation before sorting, performance becomes even worse.

RVohra

@User128178 - Oracle , there is no specific setting in OAC that will make it faster. Looking at the query, it is a very heavy logical SQL with multiple nested aggregations. Try to simplify and remove unnecessary ORDER BY.

Hope it helps!

Brendan T

You need to look at the logs, how the connection is setup to ensure it can cater for number of users and number of queries, re-examine the queries to ensure they are efficiently written, check the Caching settings, etc. There is a lot that can be causing the issue

Gopi Mannava

Best option is to try recreating that analysis and see where it is running long by monitoring the physical SQL.