Re: DB memory parameter question

Hello Jeff,
here is my humble opinion about this.

AMM (memory_target): Never ever use it except for some playground databases for yourself. You already mentioned the limitations with huge pages and in addition the SHM granules are deflated/inflated.

ASMM (sga_target): It caused many problems when it was implemented first but nowadays it is pretty common and stable. However be aware that you still can run into issues with it (e.g. current example: https://twitter.com/jolliffe/status/1094195271448186881). You can use huge pages and it is static.

PGA (pga_aggregate_target): Usage is common standard nowadays however you may need to tweak the underscore parameters a little bit if you need huge memory allocations for a work area (e.g. http://www.soocs.de/public/research/150731_auto_pga_max_workarea.txt).

Summary: IMHO always use manual memory management for SGA and Auto PGA whenever possible. Use ASMM and Auto PGA if you don't know the memory requirements for the application. Never use AMM. :)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Jeff Chirco <backseatdba_at_gmail.com> hat am 12. Februar 2019 um 20:05 geschrieben:
>
> Ok from what I have read in various documents and experienced it is not a good idea to use memory_target anymore, or at least on databases with large memory.  Plus if you want to use huge pages you can’t.  What about sga_target, sga_max_target, and pga_aggregate_target?  Do I set those or still no and just set each individual SGA memory area?
>
> I recently migrated over from Windows to Oracle Linux, running 12.2.0.1 EE.
>
> Thanks
>
> Jeff

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