RE: ASMM - resizing triggers/thresholds
Date: Mon, 4 Feb 2019 12:39:18 -0500
Message-ID: <111d01d4bcb0$a169d2c0$e43d7840$_at_rsiz.com>
Nods. You read my advice and reacted reasonably (as opposed to someone for whom a destination is reserved). And you better described some of the overhead work maintaining workshifts entails.
One more suggestion: Write a little tool to actually pin every stored package’s full suite for everything you’ve got (including all the SYS and SYSTEM stuff you’ve found in your memory). That leaves out all the dynamic use and the question becomes whether “everything including the kitchen sink” fits “reasonably” and then all the dynamic stuff float above that.
It is entirely possible that “everything” will fit without getting you an express ticket to your destination.
It ain’t tuned for least memory (just the opposite), but it will *tend* to flush “better.”
Or, that might be completely impractical in your situation, in which case you’re occupying a spot without reservation, and my condolences.
It may be that future Oracle automation will include a potentially very expensive lock nightmare (but better than a restart?) flush shared pool with shuffle down.
mwf
From: Martin Klier - Performing Databases GmbH [mailto:martin.klier_at_performing-db.com]
Sent: Monday, February 04, 2019 10:43 AM
To: Mark Farnham
Cc: jonathan; Oracle-L Freelists
Subject: Re: ASMM - resizing triggers/thresholds
Mark,
thank you for sharing good advice.
As you pointed out, the more general-purpose a setup becomes, the more complicated it is to keep up with all the changes in such a "pinned" setup. Modern systems often are seen in a "blind" (agnostic) way: Dynamically changing work-loads and -shapes will send the DBA, who pins too much, to hell (if it exists).
Here, and this is often the case for me, it is a very small, but quick and latency-critical OLTP system, back-ending a single handcrafted application. User interaction by input dialog (the most simple case) or short-living data situation (much more complicated to find out) may dynamically change queries as well as the actual software modules used. Surveying that will take longer than they have as a development cycle, and makes one feel like Don Quixote. So dynamic features like ASMM are not pure luxury. The old style DBA who as anything in view and under control is dead and gone, I'm afraid.
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: "Mark Farnham" <mwf_at_rsiz.com>
An: "jonathan" <jonathan_at_jlcomp.demon.co.uk>, "Martin Klier" <martin.klier_at_performing-db.com>
CC: "Oracle-L Freelists" <oracle-l_at_freelists.org>
Gesendet: Montag, 4. Februar 2019 16:05:22
Betreff: RE: ASMM - resizing triggers/thresholds
Slightly askew, old advice memory triggered by JL's mention of pinned:
Survey what you chronically use, most especially things that you use intermittently but which fall on the slope of the "worth it to keep in memory to avoid the reload" and put them in your warm start procedure for any instances serviced by listeners that use them (either primary and secondary or everything, depending on your environment).
Consider strongly whether that means big stuff that is used infrequently as well, and CONSIDER whether your entire applications pool of stored procedures is a reasonable amount of memory to consume as pinned.
Your mileage MOST CERTAINLY WILL VARY, and there is a special place in hell if it exists for anyone who suggests I suggested this as a best practice.
Within reason and in some very notable high throughput environments that I am legally prohibited from documenting this can be a HUUGE savings and a more stable environment. It *may* even be useful dovetailed with a planned monthly or quarterly pair of restarts if some memory heavy stored procedures only run on month, quarter, and year end.
Packing these things all in pinned so that everything that fluctuates and is not pinned tends to be contiguous can be a big advantage this method may implement. INCLUDE THE SYS AND SYSTEM STUFF that you use.
It is NOT that Oracle won't sort of do all this for you. But this remains something a good operational DBA can do that would be very difficult for Oracle (or anyone) to automate well for the general case. There are definitely cases where the effort is worthwhile.
Good luck,
mwf
PS: This ancient advice is NOT obsolete due to Oracle's memory tradeoff automation. In fact the balance point of it being worthwhile is tipped toward more often being worthwhile as this thread underscores.
-----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 -
- 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_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.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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 04 2019 - 18:39:18 CET