Hey Cheng,
please do a (PGA/UGA) heap dump and check the chunks+heaps. Unfortunately you
did not mention your database release, but you might can use
v$process_memory_detail for this as well. Heap dumps always work :-)
By the way PGA/UGA memory management is way more complex (and some views of
like v$sesstat are not 100% correct anymore) due to realfree PGA heap
management (introduced with Oracle 9i), but freeable memory is eligible to be
released to the operating system. However there are more than enough
bugs / memory leaks in this area ... so do a heap dump and identify the root
cause :-)
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Ls Cheng <exriscer@xxxxxxxxx> hat am 10. Januar 2017 um 11:45 geschrieben:
Hi
In one of DWH I take care of sometimes I observe allocated PGA (total PGA
allocated in v$pgastat) increases up to around 40GB but used (total PGA
inuse in v$pgastat) only shows bwteeen 12GB and 18GB, it is caused by a PQ
which reads 29000 million of rows. When checking V$PROCESS_MEMORY half of
parallel processes show large freeable memory but they are not freed to OS, I
deduced this because I graphed OS Watcher data and while this query is
running (around 10 hours) the free memory drops 40GB, once the query is
finished the whole 40GB is freed back to OS.
So what does Freeable memory really mean in PGA? From the OS data it does
not seem it is Freeable or perhaps the database simply hold the memory to
avoid constant allocation and deallocation of memory and only frees it when
it detects PGA pressure?
Thanks
