Two possibilities -
a) as you suggest, a test is not made immediately after the flush shared pool,
with the result that the shared pool has started to fill by the time the test
is run and the reparsing that occurs persuades Oracle that a shrink would be a
bad idea.
or maybe
b) when you flush the shared_pool items that are pinned are not flushed, and if
you have a lot of pinned items scattered across the shared pool then you may
have some items in every granule so that granules can't be re-allocated. (But
I think in this case you might see "deferred" shrink/grow reported in
v$sga_resize_ops / v$memory_resize_ops. If the whole granule can't be
re-allocated you may still see buffer cache activity in the shared_pool memory
with v$sgastat showing memory with the name 'KGH: NO ACCESS'.
If you can flush the shared pool can't you follow up with a manual resize of
the db_cache_size ? As a test, if nothing more ?
Regards
Jonathan Lewis
________________________________________
From: Martin Klier - Performing Databases GmbH <martin.klier@xxxxxxxxxxxxxxxxx>
Sent: 04 February 2019 08:59:08
To: Jonathan Lewis
Cc: Oracle-L Freelists
Subject: Re: ASMM - resizing triggers/thresholds
Hi Jonathan,
thank you for the ideas!
Maybe this mechanism is not triggered by flushing the shared pool. Do you think
that's possible?
Regards
--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director
martin.klier@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com
________________________________
Von: "jonathan" <jonathan@xxxxxxxxxxxxxxxxxx>
An: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>, "Martin Klier"
<martin.klier@xxxxxxxxxxxxxxxxx>
Gesendet: Samstag, 2. Februar 2019 15:03:43
Betreff: Re: ASMM - resizing triggers/thresholds
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
