Re: Query Performance issue

Thank You Jonathan.

Seeing multiple things , I am not sure if i am mixing things and thus missing the real issue

I found the "total PGA allocated" is currently showing as ~5GB with "global memory bound" as ~1MB in v$pgastat. And then i went to the dba_hist_pgastat to see the max it touched in past(at least in last ~40days awr) , and i see its showing around max ~9GB during some point in time, and in that case too as per the calculation, 20% of (pga_aggregate_target i.e. 15GB- total pga allocated i.e. 9GB)= >1GB, so that should not make the "global memory bound" go down till ~1MB. Why is it happening?

Then I went to see the gv$sesstat using the below query , and seeing its summing up to ~14GB.

select sn.name ,sum(st.value)/1024/1024/1024 Total

from gv$sesstat st ,gv$statname sn

where st.STATISTIC# = sn.STATISTIC# and sn.inst_id= st.inst_id and sn.inst_id=1

 and lower(sn.name) like 'sess%mem%'

group by sn.name

order by sn.name

/

NAME TOTAL(In GB)
session pga memory 3.96
session pga memory max 6.53
session uga memory 1.51
session uga memory max 1.85

Then digging into the top sessions consuming the pga from gv$sesstat, i see below top 10 consumers, three of them seem GGS related and other one application related but all are inactive only at the moment.

NAME Value/1024/1024 UNITS RNK SID SERIAL# USERNAME EVENT MODULE session pga memory 145 MB 1 1464 1 SYS LogMiner reader: redo (idle) GoldenGate
session pga memory 143.8 MB 2 2806 4195 XXXXXX SQL*Net message from client DDTek.Oracle.dll
session pga memory 139.8 MB 3 1378 15 SYS LogMiner reader: redo (idle) GoldenGate
session pga memory 139.7 MB 4 1421 3 SYS LogMiner reader: redo (idle) GoldenGate
session pga memory 120.7 MB 5 99 4041 XXXXXX SQL*Net message from client DDTek.Oracle.dll
session uga memory 138.7 MB 1 2806 4195 XXXXXX SQL*Net message from client DDTek.Oracle.dll
session uga memory 115.5 MB 2 99 4041 XXXXXX SQL*Net message from client DDTek.Oracle.dll
session uga memory 91.8 MB 3 390 25633 YYYYYY SQL*Net message from client APP1SERVICE
session uga memory 91.1 MB 4 305 5667 YYYYYY SQL*Net message from client APP1SERVICE
session uga memory 89.5 MB 5 3060 2773 YYYYYY SQL*Net message from client APP1SERVICE Then I tried peeking into the v$process, v$process_memory for the top two sessions i.e. 1464 and 2806, they are as below. i do see a significant allocation to OTHER category, but not sure if that is really pointing to any bug.

SID USERNAME SPID TOTAL_ALLOCATED(In MB) PID SERIAL# CATEGORY ROUND(PM.ALLOCATED/1024/1024) ROUND(PM.USED/1024/1024) ROUND(PM.MAX_ALLOCATED/1024/1024)
1464 SYS 188271 146 106 1 Other 145 215 1464 SYS 188271 146 106 1 Freeable 1 0
1464 SYS 188271 146 106 1 PL/SQL 0 0 0
1464 SYS 188271 146 106 1 SQL 0 0 1 SID USERNAME SPID TOTAL_ALLOCATED(MB) PID SERIAL# CATEGORY ROUND(PM.ALLOCATED/1024/1024) ROUND(PM.USED/1024/1024) ROUND(PM.MAX_ALLOCATED/1024/1024)
2806 XXXXXX 348186 148 209 223 SQL 83 77 87 2806 XXXXXX 348186 148 209 223 Other 60 107 2806 XXXXXX 348186 148 209 223 Freeable 4 0 2806 XXXXXX 348186 148 209 223 PL/SQL 1 0 3 I also verified the trend of resource "processes" in dba_hist_resource_limit, and seeing there is increase in MAX_UTIZATION value for of processes from ~917 to ~1400, but i see that increase trend/timeline is not exactly matching with the "global memory bound" trend/timeline which dropped from ~1GB to 1MB.

Is this really pointing to a bug or are we really suffering because of the high number of processes, as because i am not able to pinpoint any odd one session which is causing this issue.

Regards

Lok

On Thu, Dec 31, 2020 at 1:32 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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_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