Re: Query Performance issue

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

1. 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.
2. 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_at_gmail.com> 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.
>
>
>

--
http://www.freelists.org/webpage/oracle-l