Re: How to trace memory resize ops

On 2018/03/26 05:43, Ingrid Voigt wrote:

> Specified: 16GB db_cache_size, 3GB shared_pool_size, 20GB sga_max_size.
> Changes to: approx. 3.5GB db_cache_size, 16GB shared_pool_size

I had a similar issue a number of years back (probably v10.1), with 1 application that had 250K statements in the SGA (no binds), increasing the shared pool from 1.5GB to >11GB. A side effect was that stats collection on the dependent table caused a library cache lock that froze the instance for 10+ minutes while it invalidated those statements. Workarounds ensued...

My solution was to set the memory sizing back to manual by having SGA_TARGET=0 and specifying sizes of all pools manually. Coming up with the exact size is an exercise in itself, but we've been stable ever since. The vendor's app is still not using bind variables and we're on v11.3 (going to 12 soonish), but the shared pool size is now static. Ideal? No. But the vendor's extremely unlikely to rewrite their SQL-Server-centric app to accommodate.

Just a thought...

Rich

--
http://www.freelists.org/webpage/oracle-l