Martin,
If you look for the hidden parameters like "%memory_broker%" that gives you
some clues.
12.2.0.1
NAME SES_VAL
--------------------------------------------- ------------------------------
_automemory_broker_interval 3
_memory_broker_log_stat_entries 5
_memory_broker_marginal_utility_bc 12
_memory_broker_marginal_utility_sp 7
_memory_broker_shrink_heaps 15
_memory_broker_shrink_java_heaps 900
_memory_broker_shrink_streams_pool 900
_memory_broker_shrink_timeout 60000000
_memory_broker_stat_interval 30
I believe MMAN handles the memory management - so you could enable wait state
tracing on that process in a sandbox.
I think there's a calculation every 3 seconds to determine whether or not
memory allocations should change, although strangely the parameters above
suggest both 3 seconds and 30 seconds . The former apparently is for AMM while
the latter is for ASMM.
The marginal_utility suggests to me that the calculation involves the shared
pool / buffer cache advice regarding time saved by changing memory size (a
variant of the details shown in the AWR), with a requirement (perhaps) of a
minimum improvement (12%, 7%) in performance before a change will be triggered.
There is a parameter _memory_management_tracing, default 0, that might enable
tracing of the feature(s).
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Martin Klier - Performing Databases GmbH <martin.klier@xxxxxxxxxxxxxxxxx>
Sent: 31 January 2019 10:00:35
To: Oracle-L Freelists
Subject: ASMM - resizing triggers/thresholds
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
--
//www.freelists.org/webpage/oracle-l