2 Replies Latest reply: Dec 9, 2012 5:22 PM by Catch~22 RSS

    Aggregate PGA target parameter in V$PGASTAT

    Sadock
      Hi all;

      I running Oracle 11gR1 in test environment. I am puzzled with value learned by Oracle for aggregate PGA target parameter in V$PGASTAT. I have turned AMM on by setting MEMORY_TARGET to none-zero value. Both SGA_TARGET and PGA_AGGREGATE_TARGET have set to zero value.

      I have confused with Oracle Documentation that the aggregate PGA target parameter show Current value of the PGA_AGGREGATE_TARGET initialization parameter. If this parameter is not set, then its value is 0 and automatic management of PGA memory is disabled. Then, aggregate PGA auto target show Amount of PGA memory the Oracle Database can use for work areas running in automatic mode. This amount is dynamically derived from the value of the PGA_AGGREGATE_TARGET initialization parameter and the current work area workload, and continuously adjusted by the Oracle Database.

      The results of querying V$PGASTAT is:

      SQL> select name, value from v$pgastat;

      NAME VALUE
      ---------------------------------------------------------------- ----------
      aggregate PGA target parameter 104857600
      aggregate PGA auto target 42780672
      global memory bound 20971520
      total PGA inuse 58112000
      total PGA allocated 106348544
      maximum PGA allocated 188915712
      total freeable PGA memory 7864320
      process count 34
      max processes count 56
      PGA memory freed back to OS 3518889984
      total PGA used for auto workareas 0

      My confusions are, where the value of Aggregate PGA target parameter is derived when PGA_AGGREGATE_TARGET is set to zero but AMM enabled? What is the different between aggregate PGA target parameter and aggregate PGA auto target when PGA_AGGREGATE_TARGET is set to zero but AMM enabled?

      Sadock

      Edited by: Sadock on Dec 9, 2012 9:30 PM