869578 wrote:Was 25GB a nice random value, that had no basis in reality?
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.
869578 wrote: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).
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).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.
"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".
------------------------------------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.
select name,decode(unit,'bytes',round(value/1024/1024), value) mega_value,unit
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