3 Replies Latest reply on May 20, 2013 12:07 AM by Alvaro

    pga setting issue

      Hi All,

      Using Oracle on windows.
      After setting the pga_aggregate_target why is the value different in v$memory_dynamic_components and v$pgastat?
      SQL> alter system set pga_aggregate_target=128M scope=spfile;
      System altered.
      SQL> shu immediate
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup
      ORACLE instance started.
      Total System Global Area 1068937216 bytes
      Fixed Size                  2182592 bytes
      Variable Size             662700608 bytes
      Database Buffers          398458880 bytes
      Redo Buffers                5595136 bytes
      Database mounted.
      Database opened.
      SQL> select component, (current_size/1024/1024) "Size(Mb)"  from v$memory_dynamic_components;
      COMPONENT                                  Size(Mb)
      ---------------------------------------- ----------
      shared pool                                     352
      large pool                                        8
      java pool                                        16
      streams pool                                      0
      SGA Target                                      768
      DEFAULT buffer cache                            380
      KEEP buffer cache                                 0
      RECYCLE buffer cache                              0
      DEFAULT 2K buffer cache                           0
      DEFAULT 4K buffer cache                           0
      DEFAULT 8K buffer cache                           0
      COMPONENT                                  Size(Mb)
      ---------------------------------------- ----------
      DEFAULT 16K buffer cache                          0
      DEFAULT 32K buffer cache                          0
      Shared IO Pool                                    0
      PGA Target                                      256
      ASM Buffer Cache                                  0
      16 rows selected.
      SQL> select * from v$pgastat;
      NAME                                                                  VALUE UNIT
      ---------------------------------------------------------------- ---------- ------------
      aggregate PGA target parameter                                    268435456 bytes
      aggregate PGA auto target                                         196982784 bytes
      global memory bound                                                53686272 bytes
      total PGA inuse                                                    49675264 bytes
      total PGA allocated                                                75768832 bytes
      maximum PGA allocated                                              82322432 bytes
      total freeable PGA memory                                          15728640 bytes
      process count                                                            34
      max processes count                                                      35
      PGA memory freed back to OS                                        79167488 bytes
      total PGA used for auto workareas                                         0 bytes
      NAME                                                                  VALUE UNIT
      ---------------------------------------------------------------- ---------- ------------
      maximum PGA used for auto workareas                                 1483776 bytes
      total PGA used for manual workareas                                       0 bytes
      maximum PGA used for manual workareas                                     0 bytes
      over allocation count                                                     0
      bytes processed                                                    22456320 bytes
      extra bytes read/written                                                  0 bytes
      cache hit percentage                                                    100 percent
      recompute count (total)                                                 114
        • 1. Re: pga setting issue
          when all else fails, Read The Fine Manual

          • 2. Re: pga setting issue
            i see no difference in two views - both views having same value 256MB
            • 3. Re: pga setting issue

              It looks like you are using Automatic Memory Management, that is why your pga is being set above 128 MB.

              When you set memory_target, you enable the Automatic Memory Management.

              When memory_target is set and pga_aggregate_target is set, the oracle instance uses pga_aggregate_target value as a minimum.

              So, what is likely happening now, is:

              1. You tell Oracle that the minimum your PGA should ever be is 128M.

              2. You give an X (looks like around 1GB from your output) ammount of memory for oracle to automatically size dynamically according to workload.

              3. Oracle sees your instance, and based on the current state of the workload, decides that allocating more memory than the minimum you specified (128mb) is beneficial.

              4. You see the pga target at 256mb.
              1 person found this helpful