Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 39AAE10036E784 for ; Tue, 2 Feb 2021 11:24:00 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 3EAD844AA1; Tue, 2 Feb 2021 10:23:59 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 29E613F85E; Tue, 2 Feb 2021 10:23:59 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612261439; bh=QhOppd0gXZK6zXPCEksR181VMdrzeEsWRLvw7GoKbxI=; h=From:Sender:Sender:From; b=ctEPSxAsixvp54YYbQ/WUWjQg5QUoCIQje+H1DStl1TE5kQqLCI44n7BFvivauk7f AG/4JSemI77IemapoE3Q3owPJ730Vfb1uM+EmIW2MRJpfV4F/+FR9yW5cFAhRw/rjo EYul4kgPoB+1e7IxQmFxixIjFRi/gmKH3BZyvdjo= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 8e19HQjfmq3p; Tue, 2 Feb 2021 10:23:59 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 57EFD3F970; Tue, 2 Feb 2021 10:23:56 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612261437; bh=QhOppd0gXZK6zXPCEksR181VMdrzeEsWRLvw7GoKbxI=; h=From:Sender:Sender:From; b=RofAy6JLxBiaBk85uX8vSjkzdTxVpHC0CPAT1g0C2dwUABB3hmcxEJkMq9lSQEY7j DbNXLR3I7N4I95QF95pgE/HC9BgTa4y5XLkGwCbpDjV5wvS2a2/ZtYkB61LcUPdMam yYGAyAHWsvY8fWCJ3Aq3eYAEXoiArpB5YUHWNVlA= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 10:23:55 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 1C0043F85E for ; Tue, 2 Feb 2021 10:23:55 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=B5EJUOj9; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4_3EPkjGh6CN for ; Tue, 2 Feb 2021 10:23:55 +0000 (UTC) Received: from mail-io1-f44.google.com (mail-io1-f44.google.com [209.85.166.44]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 029F13F844 for ; Tue, 2 Feb 2021 10:23:54 +0000 (UTC) Received: by mail-io1-f44.google.com with SMTP id u8so15587647ior.13 for ; Tue, 02 Feb 2021 02:23:54 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=9xGOhiPdaHyplrdXm9weyxD1mclxI0UxLObaZGkhu5g=; b=WZcMOBOhE+GyZy/b05QgeEztV1uY/VV9i1g2vYBxXHWWZV8MAu1LR8MiA1QzZZSegY qLi6iZsYv2DqmF9l6mqnMCkz1OJVffWhJaJnhfZd0LGVRktYrn7GneBpVqtTDhahSgum 3RcWNkFxOHo3bh1ohE7QYUl+YjX7ByWRh0TPVSG1xlqQprdUGFsjmWW1YiLa0ctRzhFM nn/tMO31MjcnmECG8eAvU+5Oz7OhJAeXOdeMgzfbVV57GDdiqIs7yqOnHBl/JCH6D2W8 N4xfc8eEeBa0YFIMRwCmMdjrFJOqZpVwYnP8T9Sw8Qk5C7YeXgS/2+/bn4ts30tAb7A6 fRZA== X-Gm-Message-State: AOAM533vmUnfBCJEQwziI819+I2J97Q5t1/9oWOnAUyGDJrW6zQ2KFmE VjUto45fVNbx+XZC57O2CPU71+XDdn4cLcQoWT4= X-Google-Smtp-Source: ABdhPJyKsRyZuuyEP6zHIIokXiHaZK20l2DS+lawQ17sJHe5V9vAJ6DzHRvehY0VpYYubtwzYbdsbdTDW2+jY4PHSMA= X-Received: by 2002:a05:6638:40b:: with SMTP id q11mr1923476jap.133.1612261434203; Tue, 02 Feb 2021 02:23:54 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Laurentiu Oprea Date: Tue, 2 Feb 2021 12:23:10 +0200 Message-ID: Subject: Re: Library lock issue To: Mohamed Houri Cc: Pap , Chinar Aliyev , Sayan Malakshinov , Oracle L Content-Type: multipart/alternative; boundary="00000000000012223105ba57de0b" X-archive-position: 78822 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: laurentiu.oprea06@gmail.com Precedence: normal Reply-To: laurentiu.oprea06@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --00000000000012223105ba57de0b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Nice one, thanks Mohamed. I`ll update on this thread once I reach the bottom of my issues as well. =C3=8En mar., 2 feb. 2021 la 12:19, Mohamed Houri = a scris: > @Laurentiu > > As I mentioned above it was a kind of bug or kind of =E2=80=9Cwe don=E2= =80=99t know why > when using bind variable PX slaves were refusing to share the execution > plan of their QC=E2=80=9D. I have had an excellent private discussion wit= h Oracle > support engineer about this issue and he clearly explained that this was > reported to the CBO team. > > So, I am sorry I haven=E2=80=99t any bug to share with you for this issue= . > Moreover, it happened in 12cR1 and, as I said above, I still have not > encountered a similar issue in 12cR2, 18c, and 19c where I am using > parallelism extensively > > @Pap > > My answer was that, *probably*, your parallel parsing issue is due to the > usage of GTT private statistics. So if your planned fix tentative will > reveal to be useless, I would then suggest checking if using GTT shared > statistics will get rid of the library cache lock and cursor pin S wait o= n > x wait events. If so then you have to balance between sharing inadequate > GTT statistics and parsing effects. > > > > *=E2=80=9CAll things being equal you must balance between the performance > improvement brought by this new GTT feature and the parsing side effect i= t > introduces because of the underlying cursor invalidation. In my client > case,** the Library cache and Cursor Pin S wait on X wait events > introduced by the SESSION PRIVATE statistics largely outweigh the > performance penalty that comes when the 49 streams share the same GTT > statistics.=E2=80=9D* > > > > Best regards > > Mohamed Houri > > Le mar. 2 f=C3=A9vr. 2021 =C3=A0 11:10, Laurentiu Oprea > a =C3=A9crit : > >> Hello Pap, >> >> I was mentioning DS in the context of possible bug as well. If L2 is use= d >> most probably you don't have an issue around this and your issue might b= e >> around GTTs stats. >> >> =C3=8En mar., 2 feb. 2021 la 11:57, Pap a >> scris: >> >>> Thanks much. >>> >>> For that query , I see the below note section. And as i mentioned are >>> using private session level stats collected for two of the global tempo= rary >>> tables but one global temp table is having null stats and so perhaps fo= r >>> that the dynamic sampling is triggered. In our case we are not using bi= nd >>> variables in this query but literals. But anyway , I am planning to set= the >>> paralle_force_local to TRUE in the session level and along with that we >>> will collect the stats on all the three tables setting all of them to u= se >>> private session level stats , hopefully that will also make optimizer g= et >>> rid of the dynamic sampling part. >>> >>> We can't set the dynamic sampling level to zero for this query because >>> we are many times getting a bad execution path and to help fix that we = need >>> accurate stats , so I think private session level stats for all three >>> tables is the one to go for in our case. Correct me if wrong. >>> >>> Also can you please share the exact bug which you are talking of. >>> >>> >>> Note >>> >>> ----- >>> >>> - dynamic statistics used: dynamic sampling (level=3D2) >>> >>> >>> Regards >>> >>> Pap >>> >>> On Tue, Feb 2, 2021 at 2:32 PM Mohamed Houri >>> wrote: >>> >>>> >>>> I have faced a similar 12cR1 issue of parallel slave (PX) refusing to >>>> share the execution plan of their query coordinator (QC) despite both = QC >>>> and its PX slaves were in the same instance. It turned to be a kind of= bug >>>> because of the usage of bind variable (yes using bind variables !!) >>>> and dynamic sampling. >>>> >>>> But here you are using literals and GTT with private statistics. Bear >>>> in mind that when you are using session private statistics Oracle will= not >>>> propagate GTT private statistics from one session to another one. It >>>> achieves this by invalidating similar cursor between sessions. >>>> >>>> >>>> https://hourim.wordpress.com/2019/07/30/global-temporary-table-private= -statistic >>>> >>>> >>>> >>>> That=E2=80=99s said I haven=E2=80=99t tested how Oracle will consider = the QC >>>> coordinator session per regards to its PX slave sessions in case of pr= ivate >>>> GTT sessions. >>>> >>>> You can see if your execution plan has the following Note signaling >>>> that it has used a GTT private statistics or not >>>> >>>> Note >>>> >>>> ----- >>>> >>>> -- Global temporary table session private statistics used >>>> >>>> >>>> >>>> And use Tanel Poder nonshared sql script to get the reason for which P= X >>>> slaves are refusing the share the execution plan of their QC >>>> >>>> >>>> Best Regards >>>> >>>> Mohamed Houri >>>> >>>> Le mar. 2 f=C3=A9vr. 2021 =C3=A0 09:42, Chinar Aliyev a >>>> =C3=A9crit : >>>> >>>>> Hi, >>>>> There are several bugs in Oracle Support and might one of them be >>>>> appropriate for your case. >>>>> >>>>> It happens when QC and PX Slaves are allocated in different instances= , >>>>> and a remote slave has to parse the statement based on the info sent = by >>>>> QC (to achieve the same execution plan generated by QC). >>>>> To reduce parsing you can force it as Sayan has mentioned. Also, chec= k >>>>> support notes/bugs. >>>>> >>>>> Best Regards >>>>> >>>>> On Tue, Feb 2, 2021 at 11:12 AM Pap >>>>> wrote: >>>>> >>>>>> Thanks a lot. >>>>>> >>>>>> We will definitely try rerunning by setting session level >>>>>> "parallel_force_local" parameter to true as we set this value as 'FA= LSE' in >>>>>> v$parameter. >>>>>> >>>>>> *"parallel slaves will not be able to join the execution because the= y >>>>>> are not able to reproduce the same execution plan as the coordinator= . "* >>>>>> >>>>>> I am a bit confused with above statement on the dynamic sampling >>>>>> part, I see out of three global temporary tables used in this query = , two >>>>>> of them are using private session level stats as it's gathered insid= e the >>>>>> code. But one(RTNI) is having stats set as NULL and also its locked = , which >>>>>> means the dynamic sampling must have been triggered for that table o= nly. >>>>>> But in that case too, i am not able to understand how the dynamic sa= mpling >>>>>> can be the cause, can you please explain bit more. As because , my >>>>>> understanding is parsing will happen at the first stage and till tha= t time >>>>>> parallel slaves wont get involve in real execution. So as the table = related >>>>>> info will be available in the node-3 as that being the session of th= e query >>>>>> coordinator/parent session , so that should only do the parsing work= . >>>>>> Please correct me if wrong. >>>>>> >>>>>> Regards >>>>>> Pap >>>>>> >>>>>> On Tue, Feb 2, 2021 at 11:20 AM Laurentiu Oprea < >>>>>> laurentiu.oprea06@gmail.com> wrote: >>>>>> >>>>>>> Hello, >>>>>>> >>>>>>> Additional to what has been mentioned, usually this issue is >>>>>>> caused by the fact that parallel slaves will not be able to join th= e >>>>>>> execution because they are not able to reproduce the same execution= plan as >>>>>>> the coordinator. >>>>>>> Dynamic Sampling is often one of the root causes for this issue, If >>>>>>> DS is not helping in your case you can create a spl patch for that = sql with >>>>>>> a lower level of DS or even disable it : dynamic_sampling(0). >>>>>>> >>>>>>> Good luck >>>>>>> >>>>>>> =C3=8En lun., 1 feb. 2021 la 23:53, Sayan Malakshinov >>>>>>> a scris: >>>>>>> >>>>>>>> Hi Pap, >>>>>>>> >>>>>>>> Have you tried to set parallel_force_local=3Dtrue? Your QC is on N= ode >>>>>>>> 3 while your slaves are on Node 2: >>>>>>>> 1. Parallel queries usually work much better if they don't need to >>>>>>>> send data between nodes and fight for concurrent access to the sam= e data; >>>>>>>> 2. You are using global temporary tables, so their data is private >>>>>>>> for your session and stored on the same node as your session. In c= ase of >>>>>>>> parallel access to GTT, QC has to send also their segment info, so= your >>>>>>>> slaves have to request data from it, ie from Node 3. >>>>>>>> 3. Also that means that node 2 have to parse your query too for >>>>>>>> your slaves (sometimes it even leads to more child cursors) >>>>>>>> >>>>>>>> On Mon, Feb 1, 2021 at 10:28 PM Pap >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Hello All, We are seeing some odd behaviour. Its version >>>>>>>>> 12.1.0.2.0 of oracle. And a small query(finishing in <1 minutes) = which is >>>>>>>>> executing in parallel(2) is experiencing "library cache lock" and= "cursor: >>>>>>>>> pin S wait on X" between its own slaves. I mean to say the blocki= ng session >>>>>>>>> is appearing as its own slave sessions. We have "parallel_degree_= policy" >>>>>>>>> set as MANUAL in v$parametr. This query is running for different = literals >>>>>>>>> one after another multiple times in a loop fashion. And all these= samples >>>>>>>>> logged in dba_hist_active_sess_history showing IN_PARSE as 'Y'. D= ueto these >>>>>>>>> waits the overall execution time of the process is going beyond ~= 5hrs+. The >>>>>>>>> CPU and IO waits as noted in sql monitor is very small. Wondering= how >>>>>>>>> parallel slave processes of the same query are blocking each othe= r during >>>>>>>>> parsing itself. Or are we hitting any bug in this version? >>>>>>>>> >>>>>>>>> Attached is the sql and its run time sql monitor. And all the >>>>>>>>> tables used in this query are global temporary tables "on commit = preserve >>>>>>>>> row" types. >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> Thanks And Regards >>>>>>>>> >>>>>>>>> Pap >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Best regards, >>>>>>>> Sayan Malakshinov >>>>>>>> Oracle performance tuning engineer >>>>>>>> Oracle ACE Associate >>>>>>>> http://orasql.org >>>>>>>> >>>>>>> >>>>> >>>>> -- >>>>> *Chinar Aliyev* >>>>> >>>>> >>>>> Visit My :Blog >>>>> Let's Connect - >>>>> *Linkedin >>>>> Profile * >>>>> >>>>> My Twitter - ChinarAliyev >>>>> >>>>> >>>>> >>>> >>>> -- >>>> >>>> Houri Mohamed >>>> >>>> Oracle DBA-Developer-Performance & Tuning >>>> >>>> Visit My - Blog >>>> >>>> Let's Connect - *Linkedin >>>> Profile * >>>> >>>> My Twitter - MohamedHouri >>>> >>>> >>>> > > -- > > Houri Mohamed > > Oracle DBA-Developer-Performance & Tuning > > Visit My - Blog > > Let's Connect - *L= inkedin > Profile * > > My Twitter - MohamedHouri > > > --00000000000012223105ba57de0b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Nice one, thanks Mohamed. I`ll update on this thread once = I reach the bottom of my issues as well.=C2=A0

=C3=8En mar., 2 feb. 2021 la = 12:19, Mohamed Houri <mohamed= .houri@gmail.com> a scris:

@Laurentiu<= /span>

As I mentioned above it was a kind of bug or k= ind of =E2=80=9Cwe don=E2=80=99t know why when using bind variable PX slaves were refusing to = share the execution plan of their QC=E2=80=9D. I have had an excellent private discus= sion with Oracle support engineer about this issue and he clearly explained that this= was reported to the CBO team.

So, I am sorry I haven=E2=80=99t any bug to sh= are with you for this issue. Moreover, it happened in 12cR1 and, as I said above, I still ha= ve not encountered a similar issue in 12cR2, 18c, and 19c where I am using parallelism extensively

@Pap

My answer was that, probably, your para= llel parsing issue is due to the usage of GTT private statistics. So if your planned fix tentative will reveal to be usel= ess, I would then suggest checking if using GTT shared statistics will get rid o= f the library cache lock and cursor pin S wait on x wait events. If so then y= ou have to balance between sharing inadequate GTT statistics and parsing effec= ts.

=C2=A0

=E2=80=9CAll things being equal you must balance between the performance improvement brought by this new GT= T feature and the parsing side effect it introduces because of the underlying cursor invalidation. In my client case, the Library cache and Cursor Pin S wait on X = wait events introduced by the SESSION PRIVATE statistics largely outweigh the performance penalty that comes when the 49 streams share the same GTT statistics.=E2=80=9D

=C2=A0

Best regards

Mohamed Houri


Le=C2=A0mar. 2 f=C3= =A9vr. 2021 =C3=A0=C2=A011:10, Laurentiu Oprea <laurentiu.oprea06@gmail.com>= ; a =C3=A9crit=C2=A0:
Hello Pap,

I was mentioning DS in= the context of possible bug as well. If L2 is used most probably=C2=A0you = don't have an issue around this and your issue might be around GTTs sta= ts.

=C3=8En mar., 2 feb. 2021 la 11:57, Pap <oracle.developer35@gmail.com> a scris:
<= div dir=3D"ltr">
Thanks much.

For that query ,= I see the below note section. And as i mentioned are using private session= level stats collected for two of the global temporary tables but one globa= l temp table is having null stats and so perhaps for that the dynamic sampl= ing is triggered.=20 In our case we are not using bind variables in this query but literals. But= anyway , I am planning to set the paralle_force_local to TRUE in the sessi= on level and along with that we will collect the stats on all the three tab= les setting all of them to use private session level stats , hopefully that= will also make optimizer=C2=A0get rid of the dynamic sampling part.=C2=A0<= /div>

We can't set the dynamic sampling level to zer= o for this query because we are many times getting a bad execution path and= to help fix that we need accurate stats , so I think private session level= stats for all three tables is the one to go for in our case. Correct me if= wrong.

Also can you please share the exact bug wh= ich you are talking of.=C2=A0


Note

-----

=C2= =A0=C2=A0- dynamic statistics used: dynamic sampling (level=3D2)


Regards

Pap


On= Tue, Feb 2, 2021 at 2:32 PM Mohamed Houri <mohamed.houri@gmail.com> wrote:
=

I ha= ve faced a similar 12cR1 issue of parallel slave (PX) refusing to share the execution plan of their query coordinator (QC) despit= e both QC and its PX slaves were in the same instance. It turned to be a kind= of bug because of the usage of bind variable (yes using bind variables !!) and dyna= mic sampling.

=C2=A0But here you are using literals and GTT with private statistics. Bear in mind that when = you are using session private statistics Oracle will not propagate GTT private statistics from one session to another one. It achieves this by invalidatin= g similar cursor between sessions.

https://hourim.wordpress.com/2019/07/30/global-temporary-table= -private-statistic

<= br>

That=E2=80=99s said I haven=E2=80=99t tested h= ow Oracle will consider the QC coordinator session per regards to its PX slave sessions in case of private GTT sessions.

You can see if your execution plan has the fol= lowing Note signaling that it has used a GTT private statistics or not

Note

----- <= /span>

-- Glob= al temporary table session private statistics used

=C2=A0

And use Tanel Poder nonshared sql script to ge= t the reason for which PX slaves are refusing the share the execution plan of the= ir QC


Best Regards

Mohamed Houri


Le=C2=A0mar. 2 f=C3= =A9vr. 2021 =C3=A0=C2=A009:42, Chinar Aliyev <chinaraliyev@gmail.com> a =C3=A9cr= it=C2=A0:
H= i,
There are several bugs in Oracle Support and might one of them be approp= riate for your case.

It happens=C2=A0when QC and PX Slaves are allocat= ed in different instances, and a remote=C2=A0slave has to parse the stateme= nt based on the info=C2=A0sent=C2=A0by QC (to achieve the same=C2=A0execution plan generated by = QC).=C2=A0
To reduce parsing you can force it as Sayan=C2=A0has= mentioned. Also, check support=C2=A0notes/bugs.

Best Regards=C2=A0

On Tue, Feb 2, 2021 at 11:12 AM Pap <<= a href=3D"mailto:oracle.developer35@gmail.com" target=3D"_blank">oracle.dev= eloper35@gmail.com> wrote:
Thanks a lot.

<= /div>
We will definitely try rerunning by setting session level "p= arallel_force_local" parameter to true as we set this value as 'FA= LSE' in v$parameter.

"parallel slaves = will not be able to join the execution because they are not able to reprodu= ce the same execution plan as the coordinator. "

I am a bit confused with above statement on the dynamic=C2=A0sampli= ng part, I see out of three global temporary tables used in this query , tw= o of them are using private session level stats as it's gathered inside= =C2=A0the code. But one(RTNI) is having stats set as NULL and also its lock= ed , which means the dynamic sampling must have been triggered for that tab= le only. But in that case too, i am not able to understand how the dynamic = sampling can be the cause, can you please explain bit more. As because , my= understanding is parsing will happen at the first stage and till that time= parallel slaves wont get involve in real execution. So as the table relate= d info will be available in the node-3 as that being the session of the que= ry coordinator/parent session , so that should only do the parsing work. Pl= ease correct me if wrong.

Regards
Pap

On Tue, Feb 2, 2021 at 11:20 AM Laurentiu Oprea <laurentiu.oprea06@gmail.= com> wrote:
Hello,

Additional=C2=A0to what has b= een mentioned, usually this issue is caused=C2=A0by the fact that parallel = slaves will not be able to join the execution because they are not able to = reproduce the same execution plan as the coordinator.=C2=A0
Dynam= ic Sampling is often one of the root causes for this issue, If DS is not he= lping in your case you can create a spl patch for that sql with a lower lev= el of DS or even disable it : dynamic_sampling(0).

Good luck

=C3=8En lun., 1 feb. 2021 la 23:53, Sayan Malakshinov <<= a href=3D"mailto:xt.and.r@gmail.com" target=3D"_blank">xt.and.r@gmail.com> a scris:
=
Hi Pap,

Have you tried to set=C2=A0para= llel_force_local=3Dtrue? Your QC is on Node 3 while your slaves are on Node= 2:
1. Parallel queries usually work much better if they don'= t need to send data between nodes and fight for concurrent access to the sa= me data;
2. You are using global temporary tables, so their data = is private for your session and stored on the same node as your session. In= case of parallel access to GTT, QC has to send also their segment info, so= your slaves have to request data from it, ie from Node 3.
3. Als= o that means that node 2 have to parse your query too for your slaves (some= times it even leads to more child cursors)

On Mon, Feb 1, 2021 at 10:2= 8 PM Pap <oracle.developer35@gmail.com> wrote:

Hell= o All, We are seeing some odd behaviour. Its version 12.1.0.2.0 of oracle. = And a small query(finishing in <1 minutes) which is executing in paralle= l(2) is experiencing "library cache lock" and "cursor: pin S= wait on X" between its own slaves. I mean to say the blocking session= is appearing as its own slave sessions. We have "parallel_degree_poli= cy" set as MANUAL in v$parametr. This query is running for different l= iterals one after another multiple times in a loop fashion. And all these s= amples logged in dba_hist_active_sess_history showing IN_PARSE as 'Y= 9;. Dueto these waits the overall execution time of the process is going be= yond ~5hrs+. The CPU and IO waits as noted in sql monitor is very small. Wo= ndering how parallel slave processes of the same query are blocking each ot= her during parsing itself. Or are we hitting any bug in this version?

Attached is the sql and its= run time sql monitor. And all the tables used in this query are global tem= porary tables "on commit preserve row" types.



Thanks A= nd Regards

Pap



--
Best regards,
Sayan Malaksh= inov
Oracle performance tuning engine= er
Oracle ACE Associate
http://orasql.org
=


--
Chinar Aliyev


Visit My=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 :Blog
Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 -=C2=A0ChinarAliyev

<= br>


--

Houri Mohamed=C2= =A0

Oracle DBA-Developer-Performance & Tuning=C2=A0

Visit My =C2=A0 =C2=A0 =C2=A0 =C2=A0 -=C2=A0Blog

Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -=C2=A0MohamedHouri



--

Houri Mohamed=C2= =A0

Oracle DBA-Developer-Performance & Tuning=C2=A0

Visit My =C2=A0 =C2=A0 =C2=A0 =C2=A0 -=C2=A0Blog

Let's Connect -=C2=A0Linkedin Profile

My=C2=A0Twitter= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -=C2=A0MohamedHouri

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