Freeable memory in PGA
Date: Tue, 10 Jan 2017 11:45:36 +0100
Message-ID: <CAJ2-Qb-9vXbUzrO8QvnxbqATAD+03n3WC2VWYjLZyQsj15pK4Q_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 10 2017 - 11:45:36 CET