Re: DB memory parameter question

Not surprisingly I agree entirely with Stefan (although I suspect many people will be in the ASMM position because not many DBAs get that much time to understand applications any more). I see you are on 12.2 - if you do go down the PGA_AGGREGATE_TARGET route then, from 12.1 by doing this you are also effectively setting PGA_AGGREGATE_LIMIT https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328 which
is a *hard* limit on PGA usage and can lead to PX slaves and/or sessions being aborted. At this point, you will start to discover how your application uses memory again :)

On Wed, Feb 13, 2019 at 7:24 AM Stefan Koehler <contact_at_soocs.de> wrote:

> 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
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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