This discussion is archived
5 Replies Latest reply: Oct 8, 2012 10:32 AM by jgarry RSS

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

872581 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    You don't need the slash in the code tag. Just use
     before and after your code/output.                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points