Slow query response time in 12c vs 11g

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

Christian Berg-0racle

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

Adam Wickes

Thanks Christian.

10 years ago, the business pressured us to build a report that I knew would come back and bite us on the butt.
It seems today's the day that's happened.
Unfortunately, it's used by 300+ users per day and is, by far, the most used dashboard we have.

This is going to be fun to explain to the manager in the morning!

Christian Berg-0racle

Nothing stopping anybody from redesigning it or pre-loading things via ETL or..

Tons of options

Adam Wickes

Ended up working this out.
It turned out to be that our 12c environment was, for some unknown reason, logging as if it was set to a loglevel of 5 even though in the RPD options, it specified a loglevel of 2.
Overriding with a session variable got rid of 208 seconds of overhead!

Christian Berg-0racle

Logging produced that??? Are you logging to 5.25'' floppy disks on that system? x-D

Adam Wickes

I'm as surprised as you are!!! Writing to SSD too. I think you underestimate the horrible design of this report

Christian Berg-0racle

Adam Wickes wrote:I think you underestimate the horrible design of this report