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_at_performing-db.com // https://www.performing-databases.com 



> Von: "jonathan" <jonathan_at_jlcomp.demon.co.uk>

> An: "Oracle-L Freelists" <oracle-l_at_freelists.org>, "Martin Klier"

> <martin.klier_at_performing-db.com>

> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of

> Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>


> 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_at_performing-db.com // https://www.performing-databases.com


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