Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Administration : Restrict PGA using PGA_AGGREGATE_LIMIT

Until 12c we cannot restrict the PGA memory, even though you set pga_aggregate_target its just a soft limit, sessions can go beyond that.

However, we can restrict PGA using PGA_AGGREGATE_TARGET parameter and limiting the memory access, This is extremely useful in consolidated databases running on a host, but again the baseline should be done before setting this parameter. Even there is a default value for this parameter

PGA_AGGREGATE_LIMIT initialization parameter can be set dynamically; a database restart is not necessary. You can set the value of PGA_AGGREGATE_LIMIT regardless of whether automatic memory management is being used.

SQL> Alter system set pga_aggregate_limit = 5G;

If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:

  • Calls for sessions that are consuming the most untunable PGA memory are aborted.
  • If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.
  • In determining the sessions and processes to abort or terminate, Oracle Database treats parallel queries as a single unit.

  • Default Values
    PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the PROCESSES parameter. However, it will not exceed 120% of the physical memory size minus the total SGA size. The default value is printed into the alert log. A warning message is printed in the alert log if the amount of physical memory on the system cannot be determined

    -Thanks
    Geek DBA

  • Comments are closed.