Re: ASMM - resizing triggers/thresholds

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 2 Feb 2019 14:03:43 +0000
Message-ID: <CWXP265MB0503668A842D0C157F35E920A5930_at_CWXP265MB0503.GBRP265.PROD.OUTLOOK.COM>

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

Received on Sat Feb 02 2019 - 15:03:43 CET