Frequent execution of sql referencing dictionary views

A colleague asked me to have a look at a AWR report for an older application.
Looking at the top sqls I noticed that the application code was accessing dictonary table user_table around 40-50.000 times per hour. Even if it wasn’t using  a huge amount of cpu I started to think how I can tune this statement.

Continue reading

Advertisements

Poor Performance related to ADS (Part2)

(read Part1 here)

Why do I need a part two ? well in part 1 i said  that “I’m (almost) sure we suffered from Very Long Parse Time for Queries in In-Memory Database (Doc ID 2102106.1).”
So glad I added (almost) because now it seems we still have the same issue during our last batch run. I did a few tests after setting inmemory_query=disable and it worked fine, but my testing was obviously not good enough.

Continue reading

Cursors not shared due to ROLL_INVALID_MISMATCH

I frequently check my customers databases, during one of these controls I found that we had many versions of the same sqls with reason ROLL_INVALID_MISMATCH. I do not experiencing an issue around high version count. Hard parse is not an issue and shared_pool is not using a lot of memory. I just want to understand whats behind the figures. This is what I found.

Continue reading