RE: ASMM - resizing triggers/thresholds
From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 4 Feb 2019 10:05:22 -0500
Message-ID: <10eb01d4bc9b$0d46bf00$27d43d00$_at_rsiz.com>
From: Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>
Sent: 04 February 2019 08:59:08
To: Jonathan Lewis
Cc: Oracle-L Freelists
Subject: Re: ASMM - resizing triggers/thresholds
Date: Mon, 4 Feb 2019 10:05:22 -0500
Message-ID: <10eb01d4bc9b$0d46bf00$27d43d00$_at_rsiz.com>
Slightly askew, old advice memory triggered by JL's mention of pinned:
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Monday, February 04, 2019 5:16 AM
To: Martin Klier - Performing Databases GmbH
Cc: Oracle-L Freelists
Subject: Re: ASMM - resizing triggers/thresholds
Two possibilities -
or maybe
Regards
Jonathan Lewis
From: Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>
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_at_performing-db.com // https://www.performing-databases.comReceived on Mon Feb 04 2019 - 16:05:22 CET
________________________________
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 -- http://www.freelists.org/webpage/oracle-l