Hi Martin,
When you say you don't see any resizing operations occurring, there are no
rows at all when you check v$sga_resize_ops?
The issue you are facing with the library cache is in the form of ORA-04031
errors?
When I use ASMM, I've always set minimums for the pools and then let Oracle
decide how to use the remaining amount. Part of this is a remaining
paranoia from using ASMM when it first came out and causing us some
performance issues with huge swings in where it was allocating memory
(mostly between the shared pool and the buffer cache).
I would be interested myself if someone has an answer to the internal
questions that you asked (where, how, what, when). :)
Alfredo
On Thu, Jan 31, 2019 at 4:01 AM Martin Klier - Performing Databases GmbH <
martin.klier@xxxxxxxxxxxxxxxxx> wrote:
Hi listers,
how and when are memory areas like DB cache and shared pool being resized,
when the instance runs in Automatic Shared Memory Management?
In my case, I have a system that has had some shared pool eating (child
cursor issue, blowing up the library cache), which was resolved. Now we
have a de-facto undersized buffer cache and low library cache consumption,
but I can't see any resizing taking place. I have SGA max size = SGA Target
and no minimum values for any component here.
Platform: Linux, Oracle EE 12.2.0.1
It would not be a big problem to restart the instance at some point, but
this may be different for other cases. So my interest is more educational.
:)
In short: Where are the thresholds, what does trigger the resizing, how
often is it scanned, when does it happen?
Thanks in advance!
--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director
martin.klier@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com
