Oracle Analytics and AI

Adam Wickes

Hi all,

I'm very sorry if this is very vague but i'm about at my wits end.
I am comparing a very intensive query in 11g vs 12c and getting very different results.

In 11g, the response is approximately 6 seconds (not hitting cache).
In 12c, the response is approximately 214 seconds (not hitting cache).

The environments are set up exactly the same.
The only difference is 12c has 32gb of memory allocated to bi-server whereas 11g has 8gb.

Looking at the session details and it looks like the SQL generated is a little different in 12c (adding distinct to some select statements where 11g doesn't for example).
It also looks like the majority of time is spent in BI Server and not in the DB (query has a lot of unions).

Does anyone have a suggestion on how I could start diagnosing this one? I've been at it all day and am yet to have any sort of breakthrough.

Thanks.
Adam

[Deleted User]

Hi Adam,

These are precisely the cases where "It also looks like the majority of time" doesn't cut it in terms of level of detail of analysis. You need at the query which the BI server emitts and its corresponding explain plan on the database.

From there you then build up detailed  time profiles. WHERE does  WHAT time get spent and WHY.

The cause can be just about anything in the RPD or analysis construction in Answers. 12c is much more sensitive to how things are built and how it interprets things accordingly. I've stopped counting the number of cases where upgrades showcased customers very dearly that at some point they have to pay for imprecisions or very simply but - garbage config (not saying this is your case, just a general fact).

It can range from never updating the type of database.in the physical layer and hence not allowing  the most optimal SQL to be formed, BMM layer config like LTS mangling, pre vs post agg calcs and as said front end things like X leftover columns in the analysis or front end calcs which by any standard should reside in the RPD etc etc.

Lastly - at the very very very last after all else has been checked -  you have all the extremely detailed confog options inside the BI server itself. The engine. Not the RPD.

You have to go step by step, break down things into smaller chunks, find out where and why performance gets lost *exactly* and where the source of it is. Exactly. And then you can start looking at the why and how to  counter it.

If query performance was the same for every single query produced, no matter it's form, 90% of the e industry would be out of a job