Oracle #DB12c New – PGA_AGGREGATE_LIMIT

Program Global Area (PGA) in Oracle database is a private memory region that contains the data and control information for a server process. PGA is allocated per server process, used for storing run-time area of cursor, sorting, bitmap merge, etc.

Automatic PGA management in Oracle Database was managed by two parameters up until 11gR2 – PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY. When WORKAREA_SIZE_POLICY is set to AUTO (the default) Automatic PGA memory management is enabled, thus no need to define individual work areas (no need to worry about SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE). PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. If you explicitly did not set the PGA_AGGREGATE_TARGET parameter, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and PGA_AGGREGATE_TARGET is also set to a positive value, the PGA_AGGREGATE_TARGET value acts as the minimum value for the PGA size.

The PGA_AGGREGATE_TARGET parameter behavior or its default values did not change in Oracle Database 12c from 11gR2, but we have a new parameter in 12c, PGA_AGGREGATE_LIMIT, to limit the aggregate PGA memory consumed by the instance.

Why limit? What was happening in 11gR2 or before without the limit?

Well, PGA_AGGREGATE_TARGET is only a target, there is no limit, hence at times PGA memory usage could exceed the target and lead to high rate of swapping, and thus performance issues. To avoid this, the PGA_AGGREGATE_LIMIT parameter sets a hard limit on the PGA size. If you do not explicitly set a limit (provide a value), Oracle sets the default by picking the biggest value from:

  • 2 GB or
  • 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.

If you manually set the PGA_AGGREGATE_LIMIT, it must be bigger than the default value derived by Oracle. You can set the parameter to 0, to have no limit. If you try to set it to a smaller value, you will get an error. For example below, the minimum value determined by Oracle is 909M, and hence we cannot set it lower.

CDB$ROOT@ORCL> show parameter pga_aggregate_limit


NAME                         TYPE       VALUE
--------------------------- ----------- ------------
pga_aggregate_limit         big integer 2G

CDB$ROOT@ORCL> alter system set pga_aggregate_limit=200m ;

alter system set pga_aggregate_limit=200m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 909M and 100000G
CDB$ROOT@ORCL>

Caution: Since Oracle derives a default limit for PGA, DBAs upgrading database to 12c must be aware of what happens if the process requires more memory and the total PGA exceeds PGA_AGGREGATE_LIMIT. Once the limit exceeds, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated. So, don’t be surprised if you start seeing PGA memory exceeded error messages (ORA-04036) in the alert log after upgrading to 12c!

2 Replies to “Oracle #DB12c New – PGA_AGGREGATE_LIMIT”

  1. Nice details on new features of parallel execution and sorting with Orcale 12c DB.
    I am also using oracle12c DB and my application does lots of parallel query and CTAS operation with parallel execution. As of now I have 120GB of physical memory in my server.. and my current pga_aggregate_limit=40G; as becz i had got error few days back-‘ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT’ . my current SGA is 10GB. and i found lots of disk sorting is happening so increased my TEMP tablespace with 140G.. and its required and many parallel session invoking.. Still i have slowness issue as per application team. I found all batch job script having hints with parallel -8,10 like that. Can you suggest me here to decide and utilized memory in best way.

    It will be great help for me.

    Below is the details.

    NAME TYPE VALUE
    ———————————— ——————————— ——————————
    fast_start_parallel_rollback string LOW
    parallel_adaptive_multi_user boolean FALSE
    parallel_automatic_tuning boolean FALSE
    parallel_degree_level integer 100
    parallel_degree_limit string CPU
    parallel_degree_policy string AUTO
    parallel_execution_message_size integer 16384
    parallel_force_local boolean FALSE
    parallel_instance_group string
    parallel_io_cap_enabled boolean FALSE
    parallel_max_servers integer 401
    parallel_min_percent integer 0
    parallel_min_servers integer 64
    parallel_min_time_threshold string AUTO
    parallel_server boolean FALSE
    parallel_server_instances integer 1
    parallel_servers_target integer 160
    parallel_threads_per_cpu integer 2
    recovery_parallelism integer 0

    SYS
    >
    SYS>show parameter cp

    NAME TYPE VALUE
    ———————————— ——————————— ——————————
    cpu_count integer 16
    nls_nchar_conv_excp string FALSE
    parallel_threads_per_cpu integer 2
    resource_manager_cpu_allocation integer 16

    SYS>show parameter pga

    NAME TYPE VALUE
    ———————————— ——————————— ——————————
    pga_aggregate_limit big integer 40G
    pga_aggregate_target big integer 40G

    SYS>show parameter sga

    NAME TYPE VALUE
    ———————————— ——————————— ——————————
    lock_sga boolean FALSE
    pre_page_sga boolean TRUE
    sga_max_size big integer 10G
    sga_target big integer 10G
    unified_audit_sga_queue_size integer 1048576

    free -m
    total used free shared buffers cached
    Mem: 128926 128131 795 10240 560 122135
    -/+ buffers/cache: 5435 123491
    Swap: 8199 319 7880

    Thanks.
    -Raj Gupta

    GuptaRaj2015

Comments are closed.