The global memory bound is the immediate cause of the multipass hash joins
with a huge number of passes.
There are two main reasons why it might get very small
a) lots of processes demanding lots of memory - for your version of Oracle
the global memory bound eventually drops to the 20% of
(pga_aggregate_target (parameter) - (v$pgastat) "total PGA allocated") and
that value is the memory allowed per workarea.
b) a memory leak, possibly with incorrect feedback to some of the
PGA-related views. There's a blog note here:
https://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/ which is probably
not relevant to 11.2.0.4 but the access to the view (v$sesstat, v$process,
v$process_memory) should give you some idea of where the PGA has gone and
where it might be incorrectly accounted.
Regards
Jonathan Lewis
On Wed, 30 Dec 2020 at 19:28, Lok P <loknath.73@xxxxxxxxx> wrote:
Talking of the root cause, I am seeing something odd with respect to the
overall system. As highlighted in one of the threads by Jonathan the
difference between the estimated optimal size and memory_used in
gv$sql_workarea seems to be very high. And then he tossed two statistics "
global memory bound, and PGA freed back to OS ", though I am not used to
checking these two in the past. But when i tried to see the trend " PGA
freed back to OS" seems to be varying in a sinusoidal wave fashion
throughout the day and i am seeing some change but not seems to be
significant change in that pattern, however the " global memory bound"
statistics in dba_hist_pga_stats has been dropped suddenly from ~1GB to
~1MB around 24th November(the same time when this query started to crawl)
and few times i see its fluctuating between ~10 to 50MBs but again i.e. too
small as compared consistent ~1GB in past. Same thing happened for Node-2,
but it dropped around 4th Dec on node-2. So trying to understand what could
be the cause of this fall in statistics and this may be the reason why we
are seeing exponential growth in the number of multipasses figures in
dba_hist_sql_workarea_hstgrm and for this sql slowness too.
