5 Replies Latest reply: Oct 8, 2012 12:32 PM by jgarry RSS

    "aggregate PGA auto target" is too small compared with aggregate PGA target

    872581
      Hi, all.

      The database is 11gR2(11.2.0.3) on a linux machine.

      "aggregate PGA target parameter" is about 25 giga size.
      "aggregate PGA auto target" is about 5.5 giga size
      "total PGA allocated" is about 25 giga size.
      "total PGA inuse" is about 19.5 giga size.

      According to the oracle manual, "aggregate PGA auto target" means :

      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.

      If this value is small compared to the value of PGA_AGGREGATE_TARGET, then a large amount of PGA memory is used by other components of the system (for example, PL/SQL or Java memory) and little is left for work areas. The DBA must ensure that enough PGA memory is left for work areas running in automatic mode

      -----------------------------------------

      My question is as followings :

      1. which component is consuming about 20 giga size of pga(total 25 giga).
      If PL/SQL or Java memory consumes 20 giga size, isn't it too much compard to total pga size (25giga) ?
      Does it depend on the applications? (we have some server-side procedures/packages in the database)

      2. Total PGA allocated(25 giga) is near to "aggregate PGA target parameter(25giga).
      "total PGA inuse" is about 19.5 giga.

      Is this mean that the database has the free space of 5.5 giga size for PGA?
      I am a little confused between "Total PGA allocated" and "total PGA inuse".

      ------------------------------------

      {code}

      select name,decode(unit,'bytes',round(value/1024/1024), value) mega_value,unit
      from v$pgastat



      aggregate PGA target parameter     25,600      bytes
      aggregate PGA auto target     5,505      bytes
      global memory bound     1,024      bytes
      total PGA inuse     19,483      bytes
      total PGA allocated     25,503      bytes
      maximum PGA allocated     53,426      bytes
      total freeable PGA memory     2,486      bytes
      process count     5,946      
      max processes count     6,039      
      PGA memory freed back to OS     39,677,868      bytes
      total PGA used for auto workareas     -      bytes
      maximum PGA used for auto workareas     15,543      bytes
      total PGA used for manual workareas     -      bytes
      maximum PGA used for manual workareas     38,293      bytes
      over allocation count     3,733      
      bytes processed     17,288,505      bytes
      extra bytes read/written     3,144,169      bytes
      cache hit percentage     85      percent
      recompute count (total)     13,254,278      


      {/code}

      Thanks in advance.

      Best Regards.

      Edited by: 869578 on Oct 7, 2012 8:39 PM
        • 1. Re: "aggregate PGA auto target" is too small compared with aggregate PGA target
          sb92075
          what problem are you trying to solve?
          How will you know when correct solution has been posted?
          • 2. Re: "aggregate PGA auto target" is too small compared with aggregate PGA target
            872581
            Thanks for your replay.

            Currently, I have no problem with PGA.

            However, "total PGA allocated" is near to " pga_aggregrate_target" of 25 giga size.
            I would like to know which I need to increase "pga_aggregrate_target" parameter, or not.

            Thanks in advance.

            Best Regards.
            • 3. Re: "aggregate PGA auto target" is too small compared with aggregate PGA target
              sb92075
              869578 wrote:
              Thanks for your replay.

              Currently, I have no problem with PGA.

              However, "total PGA allocated" is near to " pga_aggregrate_target" of 25 giga size.
              I would like to know which I need to increase "pga_aggregrate_target" parameter, or not.

              Thanks in advance.

              Best Regards.
              Was 25GB a nice random value, that had no basis in reality?
              Just curious, exactly upon what criteria was the 25GB size obtained?
              post SQL & results that was used to produce the 25GB value.

              SELECT * FROM V$PGA_TARGET_ADVICE;

              post FORMATTED results from SQL above.
              • 4. Re: "aggregate PGA auto target" is too small compared with aggregate PGA target
                Jonathan Lewis
                869578 wrote:
                -----------------------------------------

                My question is as followings :

                1. which component is consuming about 20 giga size of pga(total 25 giga).
                If PL/SQL or Java memory consumes 20 giga size, isn't it too much compard to total pga size (25giga) ?
                Does it depend on the applications? (we have some server-side procedures/packages in the database)
                The place to look is v$process_memory (see, for example this blog item - ignore the fact that it's talking about a bug from 3 years ago, the SQL to query the memory usage is the important bit for you).
                2. Total PGA allocated(25 giga) is near to "aggregate PGA target parameter(25giga).
                "total PGA inuse" is about 19.5 giga.

                Is this mean that the database has the free space of 5.5 giga size for PGA?
                I am a little confused between "Total PGA allocated" and "total PGA inuse".
                Basically yes, your system currently believes it has about 5.5GB of memory that can be called on by any process that needs more memory to do some work. When a process demands memory (e.g. for sorting) it won't necessarily return it the moment it has finished with it, since it anticipates re-using it in the near future. That's why the amount allocated can be larger than the amount in use. If some other process.
                ------------------------------------

                {code}

                select name,decode(unit,'bytes',round(value/1024/1024), value) mega_value,unit
                from v$pgastat

                aggregate PGA target parameter     25,600      bytes
                aggregate PGA auto target     5,505      bytes
                global memory bound     1,024      bytes
                total PGA inuse     19,483      bytes
                total PGA allocated     25,503      bytes
                maximum PGA allocated     53,426      bytes
                total freeable PGA memory     2,486      bytes
                process count     5,946      

                {code}
                You've got nearly 6,000 processes live - and it takes a couple of MB per process just to operate a few little bits of SQL, so it's not really surprising that you've already allocated about 19GB of memory - that's an average of 3MB per process. You MAY find, though, that a couple of processes have run away with a large chunk, that's why you can look at v$process and v$process_memory in detail.

                Regards
                Jonathan Lewis
                • 5. Re: "aggregate PGA auto target" is too small compared with aggregate PGA target
                  jgarry
                  You don't need the slash in the code tag. Just use
                   before and after your code/output.